Today I ran into an issue where I wanted to restore a SQL database to a Development server from the Production server, below is a simple script that updates the database on the Development server from the folder on the Production SQL server where SQL does it’s full backups in the Windows Backup .bak format.

NOTE: The location of SQL Backups folder where SQL stores it’s Full backups, that are done by the SQL Management Studio, should have permissions so the Development server can access the Full Backups that will be in a Windows Backup .bak format. Also it would be useful to share the folder with a folder name with a “$” IE: ‘foldername$’ so that it can be viewed via a UNC path from the development server. The “$” makes the share only visible to a user with administrator or domain administrator rights.

USE master — (Can’t sit in the database whilst its being restored!)
GO

ALTER DATABASE [Local Target Database]  SET SINGLE_USER — this might be needed to disconnect users from database so the restore can be performed.

 

Example Target Database: “Clifford_App”
EXEC master.dbo.xp_cmdshell ‘DIR ‘[Location of the SQL Backups on the Source SQL Server – a UNC path will work]’*.* /O-D’

 

Example path:  “\\cliffordsql\SQLBackups\Clifford_App\”

 

RESTORE DATABASE [Local Target Database]
FROM DISK = ‘[Location of the SQL Backups on the Source SQL Server – a UNC path will work]\[Name of the Databases Backup job with the YYYY/DD/MM/BackupJobNumber].bak’ WITH NORECOVERY, REPLACE

 

Example path:  “\\cliffordsql\SQLBackups\Clifford_App\Clifford_App_backup_2016_02_23_024806_0043466.bak”

 

ALTER DATABASE [Local Target Database] SET MULTI_USER — this might be needed to allow for users to reconnect to the database are the restore so user can use the database.
GO
RETURN

This script was very helpful for me, I’m using it to make a automated script that will be doing this well automatically, but this little script is just part of the equation, now I’ll just automate it. Perhaps this will be helpful to others. 🙂