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
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

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' 
WITH 
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',
STATS = 5

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.

ALTER DATABASE [AdventureWorks2014] SET MULTI_USER

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

Leave a Reply

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