Creating altering and dropping a database - Part 2 Video Lecture - SQL

hello welcome to presume technologies I
am venket this is part 2 of sequel
server in this session we'll understand
how to create alter and drop a database
in part 1 of this video series we have
seen how to connect to a sequel server
using sequel server management studio
having connected to the sequel server
now it's time for us to create a
database now remember in sequel server
most of the things can be done in two
ways one is graphically using sequence
of a management studio or using a query
now let us see how to create a database
graphically using sequel server
management studio let's flip back to
sequence of a management studio now
within the object Explorer window you
see a databases folder right click on
the databases folder and select a new
database and the moment we do that you
see a new database dialog box all you
have to do here is provide the name of
the new database that you want to create
for example let's say I want to create a
database with named sample1 and click OK
so what's going to happen now behind the
scenes sequence of a management studio
is going to issue a query to have this
database created for us so if you expand
the databases folder you should see
sample one database created there now we
can also create a database using a query
now let us see how to create a database
using a query now database is also one
of the types of sequel server objects
just like database you can create tables
views triggers tour procedures etc which
we will be talking about in a later
session but remember irrespective of the
type of the object that you are creating
in sequel server you use the create
statement ok so create now if I want to
create database I will say database and
on the other hand let's say if I want to
create a table I will say table if I
want to create a view I would say create
view ok so depending on the type of the
object you specify the type of the
object that you want to create which in
our case is the database so create
database and what is the name of the
that I want to create so let's say I
want to create a database called sample
- okay create database and the name of
the database executes
so what's going to happen now it creates
this database sample - so to see the
database refresh the databases folder
and you should see sample two there and
let's say for some reason you know you
close the object Explorer and you want
back and you want it back
just go to the View menu and select
object Explorer so you have it back all
right so we have seen how to create a
database graphically using the designer
as well as using it query now
irrespective of how you have created the
database when we create a database there
are two files that gets generated now
let's look at properties of one of these
databases that we have created and look
at this you know these two or
user-defined databases whereas if I
expand system databases folder you see
there are some system databases master
model MSD beat MDB these databases are
provided by sequel server installation
itself and these are required for proper
functioning of the sequel server so they
should be there so system databases and
user defined databases user defined
databases of the databases that we
create okay now for every database that
we have whether it is a system database
or a user-defined database okay
there are two files that gets generated
now let's click right click on one of
the user-defined databases go into
properties and in the files tab you will
have a path okay so there is a path here
let me copy that path paste that in the
run window and plus enter now if you
look at this you see there is sample 1
dot MDF and sample 1 underscore log dot
LDF so these are the two files that are
related to the database that we have
just created sample1 and sample2 so
sample 2 dot MDF sample to underscore
log dot LDF okay so what are these files
so MDF file is nothing but the data file
and this is the actual master data file
which contains actual data whereas LDF
is a transaction log file and we use
this file basically to recover the
database in case if there are any
problems ok we will be talking about
these two files in a great detail in a
later session but for now understand
that you know whenever we create a
database these two files get generated
dot m DF and dot L DF m DF master data
file l DF log data file all right we
have seen how to create a database now
let's see how to rename a database after
it's been created now again to rename
the database there are two ways one you
can graphically do that using sequel
server management studio or you can
write a query and to graphically to do
that just right-click on the database
and select rename just like how you
rename a file within the windows file
system and then provide your new name
there and press Enter the name will be
changed automatically but on the other
hand let us say how to rename a database
for database using a query now if you
want to do it with query then there are
two ways to do it
one is to basically use all the database
command so alter database and then the
name of the database modify name is
equal to new database name alright let's
see how to do that let's say I want to
change the name of sample to database to
sample 3 so alter database and I want to
alter sample to database and what do I
want to alter I want to alter the name
to sample 3 so when I execute this query
what's going to happen the name has been
changed from sample to to sample 3 and
if you want to see that change just
refresh this folder so now you should
see sample 3 another way to change the
name of the database is to use one of
the system stored procedures now we will
be talking about short procedures in a
later in a later session but understand
that stored procedures you know are
nothing but a group of commands that are
executed together I will talk we'll be
talking about them in a later session
but right now understand that there are
two types of stored procedures one is
system should
Ceaser's and the other one is
user-defined stored procedures now what
our system should procedures these are
provided by Microsoft sequel server
which are basically used for four
functions like renaming a stored
procedure or if you want to view the
text of a stored procedure you can use
SP underscore help text if you want to
find out how many indexes are there on a
table you can basically use a stored
procedure called SP and let's go help
index there are several system store
procedures that we use for a variety of
different tasks which we will be looking
at as we go through this course but for
now understand that if you want to
rename a database there is a system
stored procedure called SP underscore
rename DB which can be used and - this
stored procedure obviously if you have
to change the name of a database you
know you will have to tell it what is
the database you know whose name you
want to change and what is the new name
so provide these two parameters to the
stored procedure which will do its job
let's see how to do that so let's say I
want to change you know sample three
database to sample for so SP underscore
rename DB and what is the old name of
these you know database sample three and
I want to change that to sample four
so the database name sample four has
been set and now if we refresh you
should see sample form alright so we
have seen how to create a database and
how to alter a database now finally let
us see how to drop a database or delete
a database okay again to drop a deleted
database you can basically do that you
graphically using sequel server
management studio or by writing it query
for example if I want to drop this
database I can just right click that and
say delete okay and the moment I do that
what's going to happen behind the scenes
it will delete the MDF and LDF files
that are associated with this database
okay so if you go to that folder you see
sample 1 dot MDF and sample 1 underscore
log LDF now if I delete this graphically
so I'm deleting this database click okay
so what's going to happen that database
got deleted and if you go into the
folder the log files and the MDF files
both of those files should also be
deleted okay now we have seen how to do
that graphically now let's see how to do
that using a query to drop any object
let it be database table view stored
procedure etc we basically use the drop
statement drop what do I want to drop I
want to drop a database so database and
then the name of the database that you
want to drop let's say sample four so
when I execute this query what's going
to happen sample four database will be
deleted and behind the scenes both the
MDF and LDF files will also be deleted
but remember when you are dropping a
database that database should not be in
use for example let's understand what we
mean by that look at this this the
database server and more and and all the
developers or users can connect to that
database using their respective
computers now let us say this developer
is using our sample for database
developer one is using sample for
database and developer two is trying to
delete the database okay so what should
happen ideally it shouldn't be deleted
because this user will get an error
because it's there is a query for
example that's being executed and while
that query of developer 1 is being
executed developer 2 is trying to delete
the database okay so what happens at
that point of time you know the database
will not be deleted developer 2 will get
an error stating that the database is in
use and cannot be deleted okay
so you cannot drop a database if it's
currently in use you get an error
stating cannot drop database because it
is currently in use so if the other
users are connected you need to put the
database in a single user mode and then
drop the database okay so if the if
there are so many users connected to the
database then the database is set to be
in multi-user mode okay so you will have
to bring it back to single user mode
before you can actually drop that if
there are people currently connected to
that database that you are trying to
delete okay and the way we do that is we
again use the alter database command all
the database the database name that we
want to alter and we want to set the
database in single user mode with
rollback immediate what we mean by with
rollback immediate now this option let's
say for example if we go back to the
next slide let's say this user is
actually using the database and there is
a long-running query the query is taking
let's say 10 minutes to execute and this
developer is trying to delete that and
obviously he developer to cannot delete
that because that database is currently
in use so what this guy can do developer
2 can do basically he can force the
database to be set into single user mode
and then at that point of time he can
also tell okay if there are any pending
transactions running on that server roll
them back immediately okay so this
option with rollback immediate basically
tells okay if there are any incomplete
transactions roll them back immediately
and close the connection to the database
okay so this is the command that we use
let us simulate that you know let's say
let's open another sequence of a
management studio and try to connect to
that and then let's put the sample for
database into use there so let's connect
to sequels of a management studio
okay so we are connected to sequel
server management studio and let's open
up a new query editor window and let's
select sample four here so it's like
this is one user who is using sample
four database and this is another user
who is trying to drop the database now
let's try to drop this database and see
what's going to happen so I am trying to
drop this database look at this this
query is trying to drop the database but
we are not able to do that because this
user is currently connected okay but on
the other hand let's cancel this query
look at this it's still not completed
it's waiting for that user to release
that so let's cancel this query let me
close this connection and now if I
execute this statement it will be
dropped immediately or what you can
basically do is you can graphically do
that delete the database and you can say
close existing connections which will
basically do effectively the same thing
okay let's say if I put this database if
I select this database currently I have
selected sample for database and if I
try to drop the database because we look
at this and trying to use that and at
the same time I am trying to drop that
look at what's going to happen cannot
drop the database because it's currently
in use okay so so obviously if you have
to drop a database that database cannot
be in use otherwise you get an error
stating so okay so if I drop this now
the database will be dropped and if you
refresh you should see that the database
is gone now okay keep that in mind if
you want to drop a database that's in
use you can use alter database database
name set single user with rollback
immediate and that this rollback
immediate option will basically roll
back all incomplete transactions and
closes any connections to the database
that you are trying to drop and also a
very important point to keep in mind
system databases cannot be dropped for
example if I try to delete one of the
system database you cannot do that you
will get an error you know drop database
let's say for example I want to drop em
the master database and if I execute
that look a that cannot drop the
database master because it is a system
database on the slide you can find
resources for asp.net and C sharp
interview questions that's it for today
thank you for listening have a great day