Restore MSSQL database from backup (.bak)

Submitted by:David Villa

Date added:03 October, 2011

Category:SQL

An example code to restore MSSQL database from backup

Tags: restore mssql database

Code Snippet:

    DECLARE @BAK_FILE_PATH varchar(300)
DECLARE @DB_TO_RESTORE_TO varchar(300)
DECLARE @SQL_SERVER_DATA_FOLDER varchar(300)

/* [BAK_FILE_PATH] : Path of backup file. e.g; D:\DatabaseBackup\MyOldDB.bak */
SET @BAK_FILE_PATH = 'D:\DatabaseBackup\MyOldDB.bak'

/* [DB_TO_RESTORE_TO] : Database to restore to. Should already exist. */
SET @DB_TO_RESTORE_TO = 'MyNewDB'

/* [SQL_SERVER_DATA_FOLDER] : Sql server data folder. Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data */
SET @SQL_SERVER_DATA_FOLDER = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'

/*
Nothing to edit below this line
*/

DECLARE @LOGICAL_NAME_Data varchar(300)
DECLARE @LOGICAL_NAME_Log varchar(300)
DECLARE @query varchar(3000)

/* Step 1 : Get the logical file names */
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''')

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,Old_PhysicalName nvarchar(128)
,[Type] char(1)
,FileGroupName nvarchar(128)
,[Size] numeric(20,0)
,[MaxSize] numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
)

INSERT #restoretemp EXEC (@query)

SELECT @LOGICAL_NAME_Data = LogicalName FROM #restoretemp WHERE [type] = 'D'
SELECT @LOGICAL_NAME_Log = LogicalName FROM #restoretemp WHERE [type] = 'L'

PRINT @LOGICAL_NAME_Data
PRINT @LOGICAL_NAME_Log

TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

/* Step 2 : Restore the database */
SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@query)

SET @query = 'RESTORE DATABASE ' + @DB_TO_RESTORE_TO
+ ' FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''')
+ ' WITH REPLACE, '
+ ' MOVE ' + QUOTENAME(@LOGICAL_NAME_Data, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'.mdf') , '''')
+ ', MOVE ' + QUOTENAME(@LOGICAL_NAME_Log, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'_log.ldf'), '''')
EXEC (@query)

SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET MULTI_USER'
EXEC (@query)
 
 

Comments