I worked with LiteSpeed extensively many years ago when I was a DBA at Microsoft before native backup compression was added to SQL Server in SQL Server 2008. Back then, LiteSpeed had just recently been purchased by Quest Software (I don’t remember who owned it before Quest). I wrote a lot of automation scripts for backups and restores using LiteSpeed back in 2006 and 2007, but I haven’t really used LiteSpeed since 2008 or early 2009. It’s been a while. LiteSpeed is now owned by Dell (they bought Quest). The software has changed, I’ve changed. Lots of things are different. At my current role, we recently switched to LiteSpeed, because we have to store backups for a very long time due to regulatory concerns so we want to compress those backups as much as possible. Native backup compression is really targeted at the sweet spot that is the balance between compression and performance. But we also want performance, and LiteSpeed proved to perform much better than our previous third-party backup solution.
Simply working with LiteSpeed again wasn’t enough to motivate me to jump into learning the intricacies of LiteSpeed again. One of the other DBAs handled changing our backup routines to use LiteSpeed so it wasn’t something I needed to worry about right away, but I started working on migrating the instances to SQL Server 2016. For the migration, I wanted to use log shipping. I can’t use native log shipping because we are using LiteSpeed for our backups, and I don’t want to modify our DR plan for restoring one of the databases. Litespeed has it’s own log shipping functionality and interface, but I have a lot of databases on several server and don’t want to have to deal with a GUI nor do I want to modify our current log backup processes.
I decided to roll my own version of log shipping. I wrote two processes for this. The first script is used to seed the databases. It restores the last full backup and all log backups since the full backup to the new server and leaves it in a recovering state. This process is called SeedLitespeedLogshipping.sql.
The second script is called SyncLiteSpeedLogShipping.sql, and it checks for new log backups and restores them. Again, it leaves the database in a restoring mode.
First, I need to set up a linked server back to the source server where I run my backups. My databases are in an Availability Group, and we back up on the readable secondary so I point it to the secondary. the backups are backed up to a file share on a separate server.
Note: all scripts available to download at bottom of post.
-- Set the source location for the databases DECLARE @PhysicalHost sysname = N''; -- Create a linked server for use by scripts If Not Exists (Select 1 From sys.servers Where name = N'LITESPEEDLSSOURCE') Begin; -- Linked server to source server named LITESPEEDLSSOURCE Exec master.dbo.sp_addlinkedserver @server = N'LITESPEEDLSSOURCE', @srvproduct = @PhysicalHost, @datasrc = @PhysicalHost, @provider = N'SQLNCLI11'; -- Authenticate as caller Exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LITESPEEDLSSOURCE', @useself = N'True', @locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULL; End;
xp_restore_automated
Now that I have a linked server, I need grab a list of the back names and restore them. LiteSpeed has this really cool automated restore script that will check the backup location and find the right backup files and restore them. Unfortunately, this procedure restores them fully, and I need them to be left in a recovering state. Xp_restore_automated does not support the NORECOVERY flag. Works great for restores to test or some other environment, but not so great for making my own log shipping.
I am able to take advantage of this procedure though. It has a parameter called @dryrun which toggles whether you want it to perform the restore or simply list the backups to be restored. I generate a list of databases, their backup locations, and the source server, and the procedure gives me a list of backups to restore. Then I can just use xp_restore_backup to restore each one.
The other parameters I care about for xp_restore_automated are as follows:
- @backuppath — path to where the backups are located
- @database — name of the database we are restoring to/as
- @backupextension — the extension used for the backup file
- @sourceserver — the server that generated the backups. This is critical as it will not restore backups from a different server
- @sourcedatabase — name of the database that was backed up
- @backuptype — full = restore only the full backup, diff = restore the full and most recent differential, tlog = restore the full, most recent differential, and all log backups since the differential or full
- @DontUseCopyOnly — is it okay to use copy_only backups in the restore? Double negative where 0 = yes, include copy_only backups and 1 = no, do not include copy_only backups
For the full backup, some of my backups may be striped to multiple files, so I need to combine all of the full backups it returns into a single restore command. Then I can simple restore the log backups one at a time. It is possible for log backups to be striped to multiple files, but I know that’s not the case in my environment, so I did not worry about that.
This is the full seeding script:
DECLARE @PhysicalHost sysname, @DBName sysname, @BackupPath nvarchar(100), @MaxID int, @CurrID int, @RestoreSQL nvarchar(max), @RestoreFiles nvarchar(max), @MaxFile int, @CurrFile int, @BFName nvarchar(260), @BFNumber int, @ErrNumber int, @ErrSeverity int, @ErrState int, @ErrProcedure sysname, @ErrLine int, @ErrMsg nvarchar(2048); Declare @DBs Table (DBID int identity(1, 1) not null primary key, DBName sysname, BackupPath nvarchar(100)); Declare @BackupFiles Table (BFID int identity(1, 1) not null primary key, BFName nvarchar(260), BFNumber int); -- Get the name of the instance where the backups are run Select @PhysicalHost = data_source From sys.servers Where name = N'LITESPEEDLSSOURCE'; -- Get the location of each backup for the last backup created With BUD As (Select RowID = ROW_NUMBER() Over (Partition By BS.database_name Order By BS.backup_finish_date Desc), BS.database_name As DatabaseName, PhysDevice = Reverse(Right(Reverse(BMF.physical_device_name), Len(BMF.physical_device_name) - CharIndex('\', Reverse(BMF.physical_device_name)) + 1)) From LITESPEEDLSSOURCE.msdb.dbo.backupset As BS Inner Join LITESPEEDLSSOURCE.msdb.dbo.backupmediafamily As BMF On BMF.media_set_id = BS.media_set_id) Insert Into @DBs (DBName, BackupPath) Select DatabaseName, PhysDevice From BUD Where RowID = 1; -- Set the start and stop points for the database Select @MaxID = Max(DBID), @CurrID = 1 From @DBs; -- Loop through each database While @CurrID <= @MaxID Begin -- Get important values for current database Select @DBName = DBName, @BackupPath = BackupPath From @DBs Where DBID = @CurrID; -- Only seeding databases that don't already exist If Not Exists (Select * From sys.databases Where name = @DBName) Begin Delete From @BackupFiles; -- Get list of files to restore for database Insert Into @BackupFiles(BFName, BFNumber) EXEC master.dbo.xp_restore_automated @database = @DBName, @backuppath = @BackupPath, @backupextension = N'bak', @checksubfolders = 1, @sourceserver = @PhysicalHost, @sourcedatabase = @DBName, @backuptype = N'tlog', @DontUseCopyOnly = 0, @affinity = 0, @logging = 0, @withreplace = 0, @dryrun = 1; Set @RestoreFiles = Null; -- Create the restore database command (accounts for multiple backup files) Select @RestoreFiles = IsNull(@RestoreFiles + char(10), '') + N'@filename = ''' + BFName + N''',' From @BackupFiles Where BFName Like N'%full%.bak'; Set @RestoreSQL = N'EXEC master.dbo.xp_restore_database @database = N''' + @DBName + ''', ' + @RestoreFiles + N' @affinity = 0, @logging = 0, @with = N''STATS = 10'', @with = N''NORECOVERY'';'; -- Restore the full backup Exec sys.sp_executesql @RestoreSQL; -- Loop through remaining files Select @MaxFile = Max(BFID), @CurrFile = Min(BFID) From @BackupFiles Where BFName Not Like N'%full%.bak'; While @CurrFile <= @MaxFile Begin Select @BFName = BFName, @BFNumber = BFNumber From @BackupFiles Where BFID = @CurrFile; -- Restore each log file Begin Try EXEC master.dbo.xp_restore_log @database = @DBName, @filename = @BFName, @filenumber = @BFNumber, @affinity = 0, @logging = 0, @with = N'STATS = 10', @with = N'NORECOVERY'; End Try Begin Catch Set @ErrNumber = ERROR_NUMBER(); Set @ErrSeverity = ERROR_SEVERITY(); Set @ErrState = ERROR_STATE(); Set @ErrProcedure = ERROR_PROCEDURE(); Set @ErrLine = ERROR_LINE(); Set @ErrMsg = ERROR_MESSAGE(); RaisError(@ErrMsg, 1, 1); End Catch -- Next loop Set @CurrFile = @CurrFile + 1; End End -- Next loop Set @CurrID = @CurrID + 1; End
The final script is really just a version of the the seeding script. The differences here are that I am only going to attempt to restore transaction log backups, and only for databases that already exist.
DECLARE @PhysicalHost sysname, @DBName sysname, @BackupPath nvarchar(100), @MaxID int, @CurrID int, @RestoreSQL nvarchar(max), @RestoreFiles nvarchar(max), @MaxFile int, @CurrFile int, @BFName nvarchar(260), @BFNumber int, @ErrNumber int, @ErrSeverity int, @ErrState int, @ErrProcedure sysname, @ErrLine int, @ErrMsg nvarchar(2048); Declare @DBs Table (DBID int identity(1, 1) not null primary key, DBName sysname, BackupPath nvarchar(100)); Declare @BackupFiles Table (BFID int identity(1, 1) not null primary key, BFName nvarchar(260), BFNumber int); Set NoCount On; -- Get the name of the instance where the backups are run Select @PhysicalHost = data_source From sys.servers Where name = N'LITESPEEDLSSOURCE'; -- Get the location of each backup for the last backup created With BUD As (Select RowID = ROW_NUMBER() Over (Partition By BS.database_name Order By BS.backup_finish_date Desc), BS.database_name As DatabaseName, PhysDevice = Reverse(Right(Reverse(BMF.physical_device_name), Len(BMF.physical_device_name) - CharIndex('\', Reverse(BMF.physical_device_name)) + 1)) From LITESPEEDLSSOURCE.msdb.dbo.backupset As BS Inner Join LITESPEEDLSSOURCE.msdb.dbo.backupmediafamily As BMF On BMF.media_set_id = BS.media_set_id) Insert Into @DBs (DBName, BackupPath) Select DatabaseName, PhysDevice From BUD Where RowID = 1 And Exists (Select 1 From sys.databases Where name = BUD.DatabaseName And state = 1); -- Set the start and stop points for the database Select @MaxID = Max(DBID), @CurrID = 1 From @DBs; -- Loop through each database While @CurrID <= @MaxID Begin Select @DBName = DBName, @PhysicalHost = PhysicalHost, @BackupPath = BackupPath From @DBs Where DBID = @CurrID; Delete From @BackupFiles; -- Get list of files to restore for database Insert Into @BackupFiles(BFName, BFNumber) EXEC master.dbo.xp_restore_automated @database = @DBName, @backuppath = @BackupPath, @backupextension = N'bak', @checksubfolders = 1, @sourceserver = @PhysicalHost, @sourcedatabase = @DBName, @backuptype = N'tlog', @DontUseCopyOnly = 0, @affinity = 0, @logging = 0, @withreplace = 0, @dryrun = 1; -- Loop through non full bakcup files Select @MaxFile = Max(BFID), @CurrFile = Min(BFID) From @BackupFiles Where BFName Not Like N'%full%.bak'; While @CurrFile <= @MaxFile Begin Select @BFName = BFName, @BFNumber = BFNumber From @BackupFiles Where BFID = @CurrFile; -- Restore each log file Begin Try EXEC master.dbo.xp_restore_log @database = @DBName, @filename = @BFName, @filenumber = @BFNumber, @affinity = 0, @logging = 0, @with = N'STATS = 10', @with = N'NORECOVERY'; End Try Begin Catch Set @ErrNumber = ERROR_NUMBER(); Set @ErrSeverity = ERROR_SEVERITY(); Set @ErrState = ERROR_STATE(); Set @ErrProcedure = ERROR_PROCEDURE(); Set @ErrLine = ERROR_LINE(); Set @ErrMsg = ERROR_MESSAGE(); RaisError(@ErrMsg, 1, 1); End Catch -- Next loop Set @CurrFile = @CurrFile + 1; End -- Next loop Set @CurrID = @CurrID + 1; End Set NoCount Off;
You can download these scripts here: DIYLitespeedLogshipping.zip (4 KB)
T-SQL Tuesday #081: Recap | SQL RNNR
[…] Robert Davis (blog | twitter) – Robert found himself placed in the performance circle thanks to his article involving a third party backup utility that should be heavy on the performance side. Robert needed something interesting to push him to reacquaint himself with this tool. Once he found that project that required just a touch of ingenuity, performance and a way to avoid the GUI, Robert found himself right at home with a great solution for his environment. […]