Restore database backup using SQL script

Here is a way to restore database backup using SQL script.

The first step is to get the logical file names contained in the backup device.

-- get file names from the backup device 
RESTORE FILELISTONLY FROM DISK = N'E:\Ahmad\AdventureWorks2014.bak'

1_restore database backup using sql script

If database is being restored on an existing database, the second step is to disconnect all sessions to that database.

ALTER DATABASE AdventureWorks2014

The third step is to construct the restore database command based on the logical file name returned from the first step. If the database is being restored to new location then specify the new path for the database files using the MOVE command. The query for the same is shown below.

RESTORE DATABASE [AdventureWorks2014] FROM DISK = N'E:\Ahmad\AdventureWorks2014.bak' 
MOVE N'AdventureWorks2014_Data' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DMSQL2014\MSSQL\DATA\AdventureWorks_Data.mdf',  
MOVE N'AdventureWorks2014_Log' TO N'C:\Program Files\
Microsoft SQL Server\MSSQL12.DMSQL2014\MSSQL\DATA\AdventureWorks_Log.ldf',

In order to overwrite existing database append REPLACE command to above query.

The last step is to bring database to multi_user mode if it doesn’t come up by itself.


Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *