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.

Method

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-PROD1\sql\backup\’ 

 

– – specify filename format

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

 

DECLARE db_cursor CURSOR FOR 

SELECT name

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

SP2013PROD_Content_CentralAdmin.BAK

SP2013PROD_Content_MySites.BAK

SP2013PROD_Content_Portal.BAK

SP2013PROD_MetaData.BAK

SP2013PROD_Profile.BAK

SP2013PROD_Search.BAK

SP2013PROD_SearchCentre.BAK

SP2013PROD_Search_AnalyticsReportingStore.BAK

SP2013PROD_Search_CrawlStore.BAK

SP2013PROD_Search_LinksStore.BAK

SP2013PROD_SecureStore.BAK

SP2013PROD_Social.BAK

SP2013PROD_StateService.BAK

SP2013PROD_Sync.BAK

SP2013PROD_TranslationService.BAK

TestAAG.bak

WSS_Content_test.BAK

WSS_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-PROD1\sql\backup\’ 

SET @datapath = ‘D:\Program Files\Microsoft SQL Server\MSSQL11.SQLDR\MSSQL\DATA\’

SET @logpath = ‘L:\Program Files\Microsoft SQL Server\MSSQL11.SQLDR\MSSQL\Logs\’

 

print ‘backup path is ‘ + @backuppath

print ‘data path is ‘ + @datapath

print ‘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 CURSOR FOR

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/