Summary - PowerShell Restore Driver Script for Log Shipping Lite

The objective of this article is to develop a light weight log shipping solution for a very specific scenario. If you are using Enterprise Editions of SQL Server 2008 or 2012 then asynchronous database mirroring with snapshots or Availability Groups in 2012 will deliver everything in this article and more. If you require a database to be refreshed on demand initially from a full then incrementally from differential and transaction log backups but are constrained by:

• Standard Editions of SQL Server 2008, 2012
• Concerns with the latency introduced on the primary by synchronous database mirroring
• Do not require a Disaster Recovery solution
• Do not want to, or do not have the space to store a second copy of backups files on the standby server

This article might be interesting if you have similar requirements.

Default Log Shipping - No Monitor Server

The default SQL Server Log Shipping feature, shown in the diagram below, creates jobs to perform the following tasks:

• Primary Server Job - Take an initial, optional full backup then scheduled log backups on the primary server
• Standby Server Job - Copy the full and log backup files from the primary to the standby server
• Standby Server Job - Restore the backups on the standby server.

Log Shipping Light

If all that's needed is to restore primary backups on the standby server, for example to use as ETL source data, then it's potentially a lot of unnecessary storage consumed storing a second copy of the backup files. The scripts here perform the restore using the primary backup file UNC path directly, no files are copied to the standby server. The diagram below shows this light weight log shipping solution.

The PowerShell script (PS_LogShippingLight attached) can be invoked from the PowerShell command line, a SQL Agent job or as an SSIS job step on any server with PowerShell 2.0 and SQL Server Management Studio installed. The T-SQL stored procedure (sp_LogShippingLight, also attached) queries backup history in msdb and database file details in master on the primary server. It builds and returns a T-SQL script to the PowerShell caller which then invokes the RESTORE commands on the standby server.

Features of the solution include:

• No #temp tables or cursors are used (as a challenge in my own time) which resulted in a single 1000+ line select statement involving repeating references to a CTE. It isn't a conventional approach to query writing or something I've done before/will do again/advocate but it works well, if a little slow, here.
• Allows for databases containing multiple secondary database files
• Allows for up to 10 striped backup files
• Ignores WITH COPY_ONLY and Volume Shadow Copy type backups that don't affect LSN recovery sequences.
• Checks for and uses CHECKSUM if possible during the restore
• Optimized restores using a differential backup where possible

Available Script Parameters

Examples

In the examples below, the parameters used can be verified against the 'Log Shipping Light' solution diagram to see how they map to actual resources in the lab test environment. The example executions of the script are over multipe lines for readability and use the escape character to indicate a new line if part of the previous. The commands could be invoked on a single line which might be easier if you are new to PowerShell. IE

X:\Scripts\PS\ps_LogShippingLight.ps1 -DBName "db_workspace" -FullBackupUNC "\\S1-SQL\Backups\S1-SQL-I08\" -PrimaryServer "S1-SQL\I08" -StandbyServer "S3-SQL\I08"

Example - One backup file pending restore

The following script was executed from the command line.

X:\Scripts\PS\ps_LogShippingLight.ps1 `

    -DBName "db_workspace" `

    -PrimaryServer "S1-SQL\I08" `

    -StandbyServer "S3-SQL\I08" `

    -FullBackupUNC "\\s1-sql\backups\S1-SQL-I08\S1-SQL`$I08\db_workspace\FULL\" `

    -DiffBackupUNC  "\\s1-sql\backups\S1-SQL-I08\S1-SQL`$I08\db_workspace\DIFF\" `

    -LogBackupUNC  "\\s1-sql\backups\S1-SQL-I08\S1-SQL$`I08\db_workspace\LOG\" `

    -Initialize "1"

The PowerShell script executed the restore on the standby server, connections to the database on the standby server blocking the restore are killed.

QUERY IN PROGESS - ;RESTORE DATABASE [db_workspace] FROM DISK = '\\s1-sql\backups\S1-SQL-

I08\S1-SQL$I08\db_workspace\FULL\S1-SQL$I08_db_workspace_FULL_20130615_180622.bak' WITH R

EPLACE, FILE = 1,CHECKSUM, STANDBY =N'\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace

\FULL\\db_workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE 'db_workspace' TO 'x:\data\I08\db

_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db_workspace_log.ldf', MOVE 'db_

workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE 'db_workspace_FG2' TO 'x:\data

\I08\db_workspace_FG2.ndf'

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

Example - Apply incremental log file backups

Subsequently, a transaction log backup was taken on the primary and a call made to the PowerShell script as below.

X:\Scripts\PS\ps_LogShippingLight.ps1 `

    -DBName "db_workspace" `

    -PrimaryServer "S1-SQL\I08" `

    -StandbyServer "S3-SQL\I08" `

    -FullBackupUNC "\\s1-sql\backups\S1-SQL-I08\S1-SQL`$I08\db_workspace\FULL\" `

    -DiffBackupUNC  "\\s1-sql\backups\S1-SQL-I08\S1-SQL`$I08\db_workspace\DIFF\" `

    -LogBackupUNC  "\\s1-sql\backups\S1-SQL-I08\S1-SQL$`I08\db_workspace\LOG\" `

    -Initialize "0"

With "Initialize = No", the PowerShell script identified the new transaction log backup files and restored just that as showed in the results below.

QUERY IN PROGESS - ;SELECT 'RESTORING LOG';RESTORE LOG [db_workspace] FROM DISK = '\\s1-s

ql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\LOG\S1-SQL$I08_db_workspace_LOG_20130615_18

5429.trn' WITH STANDBY =N'\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\FULL\\db_wo

rkspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 1 ,STOPAT = '2013-06-15 18:54:41',MOVE 'db

_workspace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db

_workspace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE

'db_workspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

Example - Identify quickest recovery plan

In a situation where the following backups had been taken on the primary server , FULL + LOG + DIFF + LOG

The fastest restore strategy involves

•Restore the full backup

•Restore the differential backup

•Restore the outstanding log backup

The PowerShell script below was run after the sequence of backups described above.

X:\Scripts\PS\ps_LogShippingLight.ps1 `

    -DBName "db_workspace" `

    -PrimaryServer "S1-SQL\I08" `

    -StandbyServer "S3-SQL\I08" `

    -FullBackupUNC "\\s1-sql\backups\S1-SQL-I08\S1-SQL`$I08\db_workspace\FULL\" `

    -DiffBackupUNC  "\\s1-sql\backups\S1-SQL-I08\S1-SQL`$I08\db_workspace\DIFF\" `

    -LogBackupUNC  "\\s1-sql\backups\S1-SQL-I08\S1-SQL$`I08\db_workspace\LOG\" `

    -Initialize "0"

It returned the expected result with the log backup between the full and differential backups ignored.

QUERY IN PROGESS - ;SELECT 'RESTORING FULL';RESTORE DATABASE [db_workspace] FROM DISK = '
\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\FULL\S1-SQL$I08_db_workspace_FULL_201

30615_185353.bak' WITH REPLACE, FILE = 1,CHECKSUM, STANDBY =N'\\s1-sql\backups\S1-SQL-I08

\S1-SQL$I08\db_workspace\FULL\\db_workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE 'db_works

pace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db_works

pace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE 'db_wo

rkspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

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

QUERY IN PROGESS - ;SELECT 'RESTORING DIFF';RESTORE DATABASE [db_workspace] FROM DISK = '
\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\DIFF\S1-SQL$I08_db_workspace_DIFF_201

30615_185551.bak' WITH REPLACE, FILE = 1,CHECKSUM, STANDBY =N'\\s1-sql\backups\S1-SQL-I08

\S1-SQL$I08\db_workspace\FULL\\db_workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE 'db_works

pace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db_works

pace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE 'db_wo

rkspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

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

QUERY IN PROGESS - ;SELECT 'RESTORING LOG';RESTORE LOG [db_workspace] FROM DISK = '\\s1-s

ql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\LOG\S1-SQL$I08_db_workspace_LOG_20130615_18

5605.trn' WITH STANDBY =N'\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\FULL\\db_wo

rkspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 1 ,STOPAT = '2013-06-15 18:57:38',MOVE 'db

_workspace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db

_workspace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE

'db_workspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

Example - To stop at a specific point in time

A CSV log is used to record STOPAT points as recovery progresses, it forces these points forward in time and prevents errors. The following call was made to the PowerShell script

X:\Scripts\PS\ps_LogShippingLight.ps1 `

    -DBName "db_workspace" `

    -PrimaryServer "S1-SQL\I08" `

    -StandbyServer "S3-SQL\I08" `

    -FullBackupUNC "\\s1-sql\backups\S1-SQL-I08\S1-SQL`$I08\db_workspace\FULL\" `

    -DiffBackupUNC  "\\s1-sql\backups\S1-SQL-I08\S1-SQL`$I08\db_workspace\DIFF\" `

    -LogBackupUNC  "\\s1-sql\backups\S1-SQL-I08\S1-SQL$`I08\db_workspace\LOG\" `

    -Initialize "1" `

    -StopAt "2013-06-14T23:44:08"  

It correctly identified the outstanding full, diff log backups needed. It's possible to step forward a minute or few seconds at a time and just that part of the log is restored. On my server, there have been new full backups since the stopat specified above, older backup files were used as shown in the results below.

 QUERY IN PROGESS - ;SELECT 'RESTORING FULL';RESTORE DATABASE [db_workspace] FROM DISK = '
\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\FULL\S1-SQL$I08_db_workspace_FULL_201

30614_230335.bak' WITH REPLACE, FILE = 1,CHECKSUM, STANDBY =N'\\s1-sql\backups\S1-SQL-I08

\S1-SQL$I08\db_workspace\FULL\\db_workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE 'db_works

pace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db_works

pace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE 'db_wo

rkspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

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

QUERY IN PROGESS - ;SELECT 'RESTORING DIFF';RESTORE DATABASE [db_workspace] FROM DISK = '
\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\DIFF\S1-SQL$I08_db_workspace_DIFF_201

30614_233131.bak' WITH REPLACE, FILE = 1,CHECKSUM, STANDBY =N'\\s1-sql\backups\S1-SQL-I08

\S1-SQL$I08\db_workspace\FULL\\db_workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE 'db_works

pace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db_works

pace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE 'db_wo

rkspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

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

QUERY IN PROGESS - ;SELECT 'RESTORING LOG';RESTORE LOG [db_workspace] FROM DISK = '\\s1-s

ql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\LOG\S1-SQL$I08_db_workspace_LOG_20130614_23

3142.trn' WITH STANDBY =N'\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\FULL\\db_wo

rkspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 1 ,STOPAT = '2013-06-14 23:44:08',MOVE 'db

_workspace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db

_workspace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE

'db_workspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

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

QUERY IN PROGESS - ;SELECT 'RESTORING LOG';RESTORE LOG [db_workspace] FROM DISK = '\\s1-s

ql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\LOG\S1-SQL$I08_db_workspace_LOG_20130614_23

3518.trn' WITH STANDBY =N'\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\FULL\\db_wo

rkspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 1 ,STOPAT = '2013-06-14 23:44:08',MOVE 'db

_workspace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db

_workspace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE

'db_workspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

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

QUERY IN PROGESS - ;SELECT 'RESTORING LOG';RESTORE LOG [db_workspace] FROM DISK = '\\s1-s

ql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\LOG\S1-SQL$I08_db_workspace_LOG_20130614_23

4141.trn' WITH STANDBY =N'\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\FULL\\db_wo

rkspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 1 ,STOPAT = '2013-06-14 23:44:08',MOVE 'db

_workspace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db

_workspace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE

'db_workspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

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

QUERY IN PROGESS - ;SELECT 'RESTORING LOG';RESTORE LOG [db_workspace] FROM DISK = '\\s1-s

ql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\LOG\S1-SQL$I08_db_workspace_LOG_20130614_23

4203.trn' WITH STANDBY =N'\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\FULL\\db_wo

rkspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 1 ,STOPAT = '2013-06-14 23:44:08',MOVE 'db

_workspace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db

_workspace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE

'db_workspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

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

QUERY IN PROGESS - ;SELECT 'RESTORING LOG';RESTORE LOG [db_workspace] FROM DISK = '\\s1-s

ql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\LOG\S1-SQL$I08_db_workspace_LOG_20130614_23

4501.trn' WITH STANDBY =N'\\s1-sql\backups\S1-SQL-I08\S1-SQL$I08\db_workspace\FULL\\db_wo

rkspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 1 ,STOPAT = '2013-06-14 23:44:08',MOVE 'db

_workspace' TO 'x:\data\I08\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\I08\db

_workspace_log.ldf', MOVE 'db_workspace_FG1' TO 'x:\data\I08\db_workspace_FG1.ndf', MOVE

'db_workspace_FG2' TO 'x:\data\I08\db_workspace_FG2.ndf'

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

Conclusion

The "Restore Database" wizard in SQL Server is more powerful than the T-SQL procedure attached here but it can't be invoked from the command line. Combining the T-SQL procedure with the PowerShell script and optionally only restoring new differential and log backups offers an alternative to log shipping with the caveats already mentioned. This is not a high availability or disaster recovery solution, its a cheap and cheerful way for silver category servers to provide reporting, data recovery, ETL source data that can be refreshed with incremental differential and log file backups from the primary.

Download and Install

The solution works with all versions of SQL Server from 2005.

• sp_LogShippingLight.txt - The T-SQL stored procedure attached needs to be created on the primary server
• ps_LogShippingLight - The PowerShell script attached should be saved to a local folder (X:\Scripts\PS in this case), the extension changed to .ps1 then invoked with parameters set as shown in the examples.

References

http://stackoverflow.com/ - StackOverflow

http://blogs.technet.com/b/heyscriptingguy/ - 'Hey, scripting guy'

Both the links above contained lots of good, relevent answers and information to PowerShell questions.