Restore nightly backups script

Restore nightly backups script

—————————————————————————————-

– Restore database from a backup file

– NOTE: If the database gets locked in Single user mode, execute

– the below command on it and it will restore the Multiuser mode:

– ALTER DATABASE yourdatabasename SET MULTI_USER

– Modify the @databaseName, @restoreDirectory & @backupFileName as per your requirement.

—————————————————————————————

USE master

GO

  – Declare the variables

DECLARE @backupFileName varchar(100),

                    @restoreDirectory varchar(100),

                    @databaseDataFilename varchar(100),

                    @databaseLogFilename varchar(100),

                    @databaseDataFile varchar(100),

                    @databaseLogFile varchar(100),

                    @databaseName varchar(100),

                    @execSql nvarchar(1000)

– Set the name of the database to restore

SET @databaseName = ‘YOUR_DATABASE_NAME’

– Set the path to the directory containing the database backup file.

– IMPORTANT:: Make sure there is a \ character at the end of your directory path as below;

SET @restoreDirectory =‘LOCATION OF BACKUPFILE’ – such as ‘\\NETWORK\BKUP\DB\’

– Create the backup file name based on the restore directory, the database name and today’s date & time.

– The formatting has been done as per my backup file requirement.

SET @backupFileName = @restoreDirectory + @databaseName+‘_backup_’+convert(varchar,getdate(), 112)+‘.bak’

PRINT @backupFileName

– Get the data file and its path

SELECT @databaseDataFile =RTRIM([Name]),

                 @databaseDataFilename =RTRIM([Filename])

FROM master.dbo.sysaltfiles AS  files

INNER JOIN master.dbo.sysfilegroups AS groups

ON files.groupID = groups.groupID

WHERE dbid =

(

  SELECT dbid

  FROM master.dbo.sysdatabases

  WHERE [Name] = @databaseName

)

– Get the log file and its path

SELECT @databaseLogFile = rtrim([Name]),

                 @databaseLogFilename = rtrim([Filename])

FROM master.dbo.sysaltfiles AS files

WHERE dbid =

(

 SELECT dbid

 FROM master.dbo.sysdatabases

 WHERE [Name] = @databaseName

)

AND

groupID = 0

PRINT ‘Killing active connections to the “‘+ @databaseName + ‘” database’

– Create the sql to kill the active database connections

SET @execSql = ‘ ‘

SELECT @execSql = @execSql +‘kill ‘+convert(char(10), spid)+ ‘ ‘

FROM master.dbo.sysprocesses

WHERE db_name(dbid)= @databaseName

AND dbid<> 0

AND spid <> @@spid

– EXEC (@execSql)

PRINT ‘Restoring “‘+ @databaseName +‘” database from “‘+@backupFileName + ‘” with ‘

PRINT ‘ DATA file “‘+ @databaseDataFile +‘” located at “‘+@databaseDataFilename +‘”‘

PRINT ‘ LOG file “‘+ @databaseLogFile +‘” located at “‘+@databaseLogFilename + ‘”‘

SET @execSql = ‘restore database [' + @databaseName + ']

from disk = ”’ + @backupFileName + ”’

with

        file = 1,

        move ”’ + @databaseDataFile +”’ to ‘+””+ @databaseDataFilename + ”’,

        move ”’ + @databaseLogFile +”’ to ‘+””+ @databaseLogFilename + ”’,

        norewind,

        nounload,

        replace’

 PRINT @execsql

EXEC sp_executesql @execSql

—————End of Restore script—————————————