Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Creating, altering and dropping a database - Part 2

In Part 1 of SQL Server, we have seen, using SSMS to connect to SQL Server. In this part we will learn creating, altering and dropping a database.


A SQL Server database can be created, altered and dropped
1. Graphically using SQL Server Management Studio (SSMS) or
2. Using a Query





To create the database graphically
1. Right Click on Databases folder in the Object explorer
2. Select New Database
3. In the New Database dialog box, enter the Database name and click OK.


To Create the database using a query
Create database DatabaseName


Whether, you create a database graphically using the designer or, using a query, the following 2 files gets generated.
.MDF file - Data File (Contains actual data)
.LDF file - Transaction Log file (Used to recover the database)


To alter a database, once it's created 
Alter database DatabaseName Modify Name = NewDatabaseName


Alternatively, you can also use system stored procedure
Execute sp_renameDB 'OldDatabaseName','NewDatabaseName'


To Delete or Drop a database
Drop Database DatabaseThatYouWantToDrop


Dropping a database, deletes the LDF and MDF files.


You cannot drop a database, if it is currently in use. You get an error stating - Cannot drop database "NewDatabaseName" because it is currently in use. So, if other users are connected, you need to put the database in single user mode and then drop the database.
Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate


With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the database.


Note: System databases cannot be dropped.

15 comments:

  1. AnonymousJune 22, 2015 at 12:38 AM

    Hi Venkat,

    Is it possible to add sample links of scriptlets or snippets of databases and tables as a part of your download.

    Additionally, TSQL based programming functions and procedures tutorials will be a good start for beginners.

    thanks

    ReplyDelete
  2. UnknownApril 2, 2016 at 4:59 AM

    Hi Venkat,

    If the size of the .LDF file is getting increased in GBs so how to decrease the size of it. In my case the size is increased about 48GB. I have tried with shrink option but it is not working.

    Thanks.

    ReplyDelete
    Replies
    1. UnknownMay 16, 2019 at 12:20 PM

      Simple take log backup of database and perform DBCC Shrink file operation on it.

      Delete
    2. AnonymousJuly 1, 2022 at 10:59 AM

      Change the database recovery mode to simple if you don't need to restore the data to point in time.

      Delete
  3. UnknownJanuary 6, 2018 at 7:41 AM

    if we drop the database .ldf file will be deleted.then how it is used to recover the database?

    ReplyDelete
    Replies
    1. AnonymousMarch 26, 2023 at 11:23 AM

      If the .ldf file is deleted, it means that all the transaction log data is lost, and it will not be possible to use it for recovering the database. In such a scenario, you will have to resort to other backup and restore options to recover your data.

      Delete
  4. AnonymousNovember 25, 2018 at 6:52 PM

    hi Mr. venkat,

    i need to take sql sever course with you

    ReplyDelete
  5. TrinathDecember 26, 2019 at 1:47 AM

    Hi sir
    Is there any chance to create the Files with Query

    ReplyDelete
  6. Sagar PatelJuly 21, 2020 at 12:14 AM

    Hi Venkat,

    In this blog, as per you said if we delete the database both .mdf and .ldf will be deleted but if we rename the database, Will the actual database name in file path be changed?

    I found in my case that if I rename the database, the change in database name is not reflecting in the actual file path, so what's the reason behind it?

    Thanks

    ReplyDelete
  7. SureshJune 8, 2021 at 1:25 AM

    Dear Venkat Sir,

    When I executed following query,

    Alter table tblPerson
    add constraint tblPerson_GenderId_FK FOREIGN KEY (GenderId) references tblGender(ID)

    Getting below error,

    Msg 547, Level 16, State 0, Line 1
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "tblPerson_GenderId_FK". The conflict occurred in database "sample", table "dbo.tblGender", column 'ID'

    ReplyDelete
    Replies
    1. GopiMay 4, 2023 at 12:50 PM

      May be your table "tblPerson" GenderId column has value other than value present in the table "tblGender" Id column. i.e., If GenderId column has a value "99" prior to foreign key constraint is a valid case. But if you try to add foreign key constraint GenderId column must have a value of the table "tblGender" Id column or NULL

      Delete
  8. UnknownJune 24, 2021 at 11:41 PM

    Hi Venkat,
    Will it be possible to share .bak file which you are using for training.
    It would be helpful for tarining

    ReplyDelete
  9. UnknownJuly 2, 2021 at 11:13 PM

    Msg 547, Level 16, State 0, Line 1
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "tblPerson_GenderID_FK". The conflict occurred in database "Sample3", table "dbo.tblGender", column 'ID'.

    ReplyDelete
  10. Vishal JhaveriJanuary 9, 2022 at 3:32 AM

    Avoid using sp_renameDB to rename the Database, as it is soon going to be removed from future version of MS SQL SERVER. Refer: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-renamedb-transact-sql?view=sql-server-ver15

    ReplyDelete
  11. AnonymousSeptember 13, 2023 at 6:53 PM

    trying to drop a database but it failed. I tried the command shown below (change to a single user ) but I got an error message which is : User does not have permission to alter database. Anyone can help ?

    ReplyDelete

It would be great if you can help share these free resources

Subscribe to: Post Comments (Atom)