Rolling log files for a day, especially with 15 or even 5 minute log backups is a pain at best. Here’s a slightly better way to do it. Set the two variables at the top to the directory where the log backups are and the database you’re looking to restore. It will, assuming you named your log backups as DatabaseName_Log*, display all log backups in that folder in chronological order into an output script which is best read when doing results to text (Query/Results To/Results To Text).
Here’s what this won’t do. It won’t require SQL Server to remember taking the backups, so you can run it from any server that has access to this folder. It won’t restore anything for you, it will just give you the text to copy/paste and run yourself. It won’t leave xp_cmdshell on, although it does require it to be turned on for a bit if it was off.
Since this does nothing more than display the code you’ll probably run next, feel free to run it even if you’re just playing around or only want to roll half the logs.
DECLARE @LogBackupDirectory VarChar(1000) DECLARE @DatabaseName VarChar(256) SELECT @LogBackupDirectory = '\\BUServer\BUShare\Folder\logs\' , @DatabaseName = 'model' ---Don't change anything below this point unless upgrading the script--- ---Also, slight change if backup and restore database names are different--- SET NOCOUNT ON DECLARE @XP_CmdShell_Enabled INT , @XP_CmdShell_Command VarChar(4000) IF Object_ID('TempDB..#temp') IS NOT NULL BEGIN DROP TABLE #temp END create table #temp ( Dir VARCHAR(MAX) ) SELECT @XP_CmdShell_Enabled = CONVERT(INT, ISNULL(value, value_in_use)) FROM master.sys.configurations WHERE name = 'xp_cmdshell' IF @XP_CmdShell_Enabled = 0 BEGIN EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE WITH OVERRIDE END SELECT @XP_CmdShell_Command = 'dir /od /b ' + @LogBackupDirectory + @DatabaseName + '_log*' INSERT INTO #temp EXECUTE xp_cmdshell @XP_CmdShell_Command IF @XP_CmdShell_Enabled = 0 BEGIN EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE WITH OVERRIDE END DELETE #temp WHERE Dir IS NULL SELECT commands = 'Restore Log [' + @DatabaseName + '] from DISK=N''' + @LogBackupDirectory + Dir + ''' with NoRecovery' FROM #temp SELECT 'RESTORE DATABASE [' + @DatabaseName + '] WITH RECOVERY'
Related articles
- Disaster Recovery and Business Continuity (simplesqlserver.com)
Simple SQL Server 
Great tool. Just wonder if you can enhence it for multiple stripes in different folders.
It is possible, but the method I have in mind will depend on your naming convention. I’ll explain my idea and write try to write it up before too long.
Pull the contents of each folder into a three temp tables, one for each stripe. Then select from table a, join table b on right(a.dir, 15) = right(b.dir, 15), and the same with table c.
This goes off of two assumptions. First, you’re using three stripes. Second, your naming convention ends in the date, which would be the same for all stripes. It’s an easy fix if either of these assumptions is wrong.
If you have a chance to write this before I do, I’d love to see your solution.
Why not just do the same thing in a batch file so you don’t have to worry about xp_cmdshell?
@ECHO OFF
IF “%2” EQU “” GOTO Usage
IF “%3” NEQ “” GOTO Usage
CLS
ECHO.
IF EXIST %1\%2_log* (
FOR /F %%I IN (‘DIR /B /OD %1\%2_log*’) DO ECHO Restore Log [%2] from DISK=’%1\%%I’ with NoRecovery
ECHO.
ECHO RESTORE DATABASE [%2] WITH RECOVERY
) ELSE (
ECHO No log files found at %1
)
ECHO.
GOTO End
:Usage
ECHO.
ECHO Please specify the path to the database and the database name
ECHO %0 “\\Use Quotes\If Spaces exist\In Path” Dbname
GOTO End
:End
ECHO.
PAUSE
Good Luck!
John
I’m personally not too concerned with xp_cmdshell. If you have enough permissions to use it, you also have enough permissions to turn it on. The biggest issue I have with xp_cmdshell is that it if you turn it on and off all the time then it makes your error log a little more difficult to read.
Writing a batch file like that is very impressive, and something I wish I was able to do. If only I had as much time to learn as I did desire to learn then I’d be in good shape!
Could you ease explain what it is you are trying to accomplish here? I’m not familiar withe the term “rolling logs”.
Thanks
You take log backups of databases in full recovery. When you restore a database with log backups to a point in time you restore all the log backups starting at the time of the full backup, which is called rolling the logs. If you take logs every 15 minutes and have to restore 8 hours of backups, you’re talking about 32 restore statements.
I wrote these restore statements a couple times by hand. Being that I get annoyed easily by manual processes, I soon had this script to help me out.
instead of doing a dir on the backup dir (somebody may change where backups are witten to) select from [msdb].[dbo].[backupmediafamily] and [msdb].[dbo].[backupset] you can get just the backups you need ie full and logs, if you feel like it hou could write it out as a bat file each time you take a backup.
The issue is that you’re rarely restoring a database with log backups on the same install that you took the backups on. Also, the backups could have moved after taking them.
The typical use is for restoring for larger replication rebuilds or restoring a prod copy to a non-prod environment to get data right before it was accidently changed. In both cases you could get the data you mentioned from the prod instance, assuming the backup files weren’t moved or restored to a different location.
Don’t get me wrong, your idea is a great one, and I’ve even written about it here: Backup History. It would just be a simple tweak to this code to get the restore statements if you wanted the full solution.