Restore nightly backups script

I am used to getting requests from users to restore Test databases with that of PROD. This could be ad-hoc or on schedule. The below script takes inputs as DB Name, Prod Backup location and backupfile name and restores the Test db. I have made modifications to suit my requirement.

----------------------------------------------------------------------------------------

-- 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---------------------------------------

MvM