Restore/Create Database using a database backup in SQL Query

Restore/Create SQL Server Database from a DB BackUp using SQL Query:
1. Connect to the database server in sql server management studio. For example localhost. Right Click on the “Databases” and Choose “Restore Database” option. This will open the “Restore Database” window
2. To extract the query required to restore/Create the database, Click the Script button on the top and select “Script Action to New Query Window”

Image

 

3. This will give you the query to restore a database using an existing backup.

Below mentioned is the Query to create new database “TEST” from the database backup in the “D:\SQL DB\BACKUP\AdventureWorks2008R2.bak” path in device.

RESTORE DATABASE 
[TEST] 
FROM DISK = N'D:\SQL DB\BACKUP\AdventureWorks2008R2.bak' 
WITH FILE = 1, 
MOVE N'AdventureWorks2008R2_Data' 
TO N'D:\SQL DB\DATA\TEST.mdf', 
MOVE N'AdventureWorks2008R2_Log' 
TO N'D:\SQL DB\DATA\TEST_1.LDF', 
NOUNLOAD, 
STATS = 10
GO

You may also change the path of the .mdf and .ldf files if you want. By changing the .mdf and .ldf files, will create the database in a different location that you have specified.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s