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”
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.