SQL Server – Restore database backup script

Summary:

T-SQL Script, using variables and dynamic SQL.

It actually prints SQL code for you to review before execution.

Proceed with caution.

Here it is:

DECLARE @DATABASE_NAME VARCHAR(3000)
SET @DATABASE_NAME='MyDB'

DECLARE @BACKUP_FILE VARCHAR(3000)
SET @BACKUP_FILE='E:\my_db_backup.bak'

DECLARE @RESTORE_PATH VARCHAR(3000)
SET @RESTORE_PATH='E:\SQLDATA\'

DECLARE @DATA_FILE VARCHAR(3000)
SET @DATA_FILE=@RESTORE_PATH + @DATABASE_NAME + '.mdf'

DECLARE @LOG_FILE VARCHAR(3000)
SET @LOG_FILE=@RESTORE_PATH + @DATABASE_NAME + '_log.ldf'

--PRINT @DATABASE_NAME
--PRINT @BACKUP_FILE
--PRINT @RESTORE_PATH
--PRINT @DATA_FILE
--PRINT @LOG_FILE

-- Retrieve the Logical file name of the database from backup
-- gives you 'db_data_file_logical_name' and 'db_log_file_logical_name' to be used on the restore script
RESTORE FILELISTONLY
FROM DISK = @BACKUP_FILE
DECLARE @SQLString nvarchar(3000)

----Make Database to single user Mode
SET @SQLString =
'ALTER DATABASE [' + @DATABASE_NAME + '] ' +
'SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
--EXECUTE( @SQLString )
PRINT @SQLString

----Restore Database
SET @SQLString =
'RESTORE DATABASE [' + @DATABASE_NAME + '] FROM DISK = ''' + @BACKUP_FILE + '''' +
' WITH MOVE ''db_data_file_logical_name'' TO ''' + @DATA_FILE + ''', ' +
' MOVE ''db_log_file_logical_name'' TO ''' + @LOG_FILE + ''', ' +
' REPLACE'
--EXECUTE( @SQLString )
PRINT @SQLString

/*If there is no error in statement before, database will be in multiuser mode.
If error occurs, please execute following command, it will convert database in multi user.*/

SET @SQLString =
'ALTER DATABASE [' + @DATABASE_NAME + '] ' +
'SET MULTI_USER'
--EXECUTE( @SQLString )
PRINT @SQLString

GO

References:

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
EASY ONE: ALTER DATABASE with Variable
SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database
SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: