Roll Logs – Backup Recovery

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


DECLARE @XP_CmdShell_Enabled INT
    , @XP_CmdShell_Command VarChar(4000)

IF Object_ID('TempDB..#temp') IS NOT NULL BEGIN
    DROP TABLE #temp

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

SELECT @XP_CmdShell_Command = 'dir  /od /b ' + @LogBackupDirectory + @DatabaseName + '_log*'

EXECUTE xp_cmdshell @XP_CmdShell_Command

IF @XP_CmdShell_Enabled = 0 BEGIN
    EXEC sp_configure 'xp_cmdshell', 0

DELETE #temp

SELECT commands = 'Restore Log [' + @DatabaseName + '] from DISK=N''' + @LogBackupDirectory + Dir + ''' with NoRecovery' 
FROM #temp


8 thoughts on “Roll Logs – Backup Recovery

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

  1. Why not just do the same thing in a batch file so you don’t have to worry about xp_cmdshell?

    IF “%2” EQU “” GOTO Usage
    IF “%3” NEQ “” GOTO Usage

    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
    ) ELSE (
    ECHO No log files found at %1
    GOTO End

    ECHO Please specify the path to the database and the database name
    ECHO %0 “\\Use Quotes\If Spaces exist\In Path” Dbname
    GOTO End


    Good Luck!


  2. 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!

  3. Could you ease explain what it is you are trying to accomplish here? I’m not familiar withe the term “rolling logs”.


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

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

Questions are some of the sincerest compliments

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s