I needed to restore some SharePoint backup files from a data centre to a Disaster Recovery SQL instance. I could have done this manually using the restore task, however I knew that I would like to do this repeatedly for different customers and different databases. So with the help of a colleague and some reference articles I created a script that would do the restore. The method below should deal with all or any SQL database files.


I logged onto the source server and shared the drive where the backups were going to be stored with permissions to read and write using the account that was running the MSSMS on each server. I then opened Microsoft SQL Server Management Studio (MSSMS) and created backups of the databases I was interested in. 

I used the following Transact-SQL script sourced from this article by Greg Robidoux[1]: Simple script to backup all SQL Server databases. This script will back up files (.bak) to a directory of choice.   

DECLARE @name VARCHAR(50) - - database name 
DECLARE @path VARCHAR(256) - - path for backup files 
DECLARE @fileName VARCHAR(256) - - filename for backup   
DECLARE @fileDate VARCHAR(20) - - used for file name   - - specify database backup directory 
SET @path = ’\SRV-SQL-PROD1sqlackup’    - - specify filename format
FROM master.dbo.sysdatabases
WHERE name NOT IN (’master’,’model’,’msdb’,’tempdb’)  - - exclude these databases   OPEN db_cursor  FETCH NEXT FROM db_cursor INTO @name     WHILE @@FETCH_STATUS = 0  BEGIN         SET @fileName = @path + @name + ’.BAK’        BACKUP DATABASE @name TO DISK = @fileName           FETCH NEXT FROM db_cursor INTO @name  END     CLOSE db_cursor  DEALLOCATE db_cursor 

  Here is a typical list of backup files this might create:   SP2013PROD_Config.BAKSP2013PROD_Content_CentralAdmin.BAKSP2013PROD_Content_MySites.BAKSP2013PROD_Content_Portal.BAKSP2013PROD_MetaData.BAKSP2013PROD_Profile.BAKSP2013PROD_Search.BAKSP2013PROD_SearchCentre.BAKSP2013PROD_Search_AnalyticsReportingStore.BAKSP2013PROD_Search_CrawlStore.BAKSP2013PROD_Search_LinksStore.BAKSP2013PROD_SecureStore.BAKSP2013PROD_Social.BAKSP2013PROD_StateService.BAKSP2013PROD_Sync.BAKSP2013PROD_TranslationService.BAKTestAAG.bakWSS_Content_test.BAKWSS_UsageApplication.BAK 

  I then logged in on the destination server, opened up MSSMS, and used this script to restore the files:  

 DECLARE @name VARCHAR(256) - - database name 
DECLARE @backuppath NVARCHAR(256) - - path for backup files 
DECLARE @datapath VARCHAR(256) - - path for data files 
DECLARE @logpath VARCHAR(256) - - path for log files 
DECLARE @backupfileName VARCHAR(256) - - filename for backup
 DECLARE @datafileName VARCHAR(256) - - filename for database
DECLARE @logfileName VARCHAR(256) - - filename for logfile
DECLARE @logName VARCHAR(256) - - filename for logfile   - - specify database backup directory
SET @backuppath = ’\SRV-SQL-PROD1sqlackup’ 
SET @datapath = ’D:Program FilesMicrosoft SQL ServerMSSQL11.SQLDRMSSQLDATA’SET @logpath = ’L:Program FilesMicrosoft SQL ServerMSSQL11.SQLDRMSSQLLogs’   print ’backup path is ’ + @backuppathprint ’data path is ’ + @datapathprint ’log path is ’ + @logpath   - -Table to hold each backup file name in   
DROP TABLE #files - - remove any previous instance
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
INSERT #files
EXECUTE master.dbo.xp_dirtree @backuppath, 1, 1
SELECT * FROM #files   DECLARE files 
SELECT fname FROM #files    OPEN files
FETCH NEXT FROM files INTO @name      WHILE @@FETCH_STATUS = 0  BEGIN             DECLARE @cleanname AS VARCHAR(255)           
SET @cleanname  = REPLACE(@name, ’.BAK’, ’’)           
PRINT @cleanname       
SET @backupfileName = @backuppath + @name      
 SET @datafileName = @datapath + @cleanname   + ’.MDF’       
SET @logfileName = @logpath + @cleanname   + ’_log.LDF’       
 SET @logName = @cleanname + ’_log’       
 print ’backup file is ’ + @backupfileName       
 print ’data file is ’ + @datafileName        
print ’log file is ’ + @logfileName          
RESTORE DATABASE @cleanname        FROM DISK = @backupfileName        WITH NORECOVERY,           
MOVE @cleanname        TO @datafileName,        MOVE @logName        TO @logfileName            FETCH NEXT FROM files INTO @name  END     CLOSE files  DEALLOCATE files 

  Note that you will need to alter the paths to the backup, log and database areas. Note that the backup area is the shared directory from the source server. This is in UNC (\<server -name><shared-name>...) format. F5 executes the script, which spits out a list of the backup files, and the results. I hope this saves a few minutes of work for you in the future. Keep tuned for more helpful tips and trips from the Datalytyx technical blogs. [1] Script source: https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/