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

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'

Database Assessment


This script looks much more intimidating than the results it produces, so I recommend running it before reading through it.  I’ll run this when I first get on a unfamiliar server as a basic assessment of what’s there and find some hard to find issues with no effort.

The issues you’ll find include:

  • Backups – Where are they, when did they last occur, how big, etc.
    • I always look for databases (excluding model) that are in full or bulk logged recovery with no log backups.  This is a common issue, especially with vendor databases, that will fill your drives with data you didn’t care to have.
      • To know how critical it is when you find this, and you probably will, I include the log size and percent full.
    • There are variables in this script for a cutoff date which will ignore old backups. If you set @use_cutoffdate to 1 then any database not backed up since the value of @cutoffdate will show up as never being backed up. I love this feature because there’s no chance that you’ll misread a year-old backup as being taken last night.
    • Warning, the size of the backup is the size of the data, not the backup file.  To keep this compatible with SQL 2005, I’m not grabbing the compressed_backup_size field.
    • Differential backup results are commented out for a reason…most people don’t use them.  There is absolutely no reason to comment this out other than limiting the number of columns in my results.
  • Compatibility Level – If you upgrade a server it’s easy to leave a database behind.  I’ve seen SQL 2000 compatibility on SQL 2008 R2 for no other reason than “oops”.
  • Collation name – If this isn’t consistent you could run into issues that are next to impossible to debug.  The exceptions, ironically, are Microsoft databases.  Report Server and SharePoint databases specifically will be a different collation than anything else, but they were written with that in mind and handle it gracefully and shouldn’t be changed.
  • Auto Close – Ok, I admit it, I actually have this one commented out in my personal copy of this code.  If this is turned on it will typically blow up your error logs and make them half-way unreadable, and I’ll see this when I attempt to read them.  However, it’s good to know if this is ever turned on as it will kill performance.  I should uncomment this in my version with the mentality that too much information will slightly annoy you, too little will kill you.
Especially with most of us running more vendor databases than home-grown, don’t trust that this stuff was done right.
DECLARE @cutoffdate datetime
DECLARE @use_cutoffdate bit
SET @cutoffdate = DateAdd(Month, -3, GetDate())
SET @use_cutoffdate = 0

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

CREATE TABLE ##FileGroupSize
(      DB VarChar(128)
    , FileGroup VarChar(128)
    , Used_MB Int
    , Free_MB Int
    , Size_MB Int
)

exec sp_MSforeachdb N'use [?]; 
INSERT INTO ##FileGroupSize
SELECT DB = db_name()
    , FileGroup = ISNULL(g.name, f.Type_Desc) 
    , Used_MB = SUM(FileProperty(f.name, ''SpaceUsed'')) / 128
    , Free_MB = SUM(f.size - FileProperty(f.name, ''SpaceUsed'')) / 128
    , Size_MB = SUM(f.size) / 128 
FROM sys.database_files f
    LEFT JOIN sys.filegroups g on f.data_space_id = g.data_space_id
WHERE f.Type_Desc = ''LOG''
GROUP BY f.Type_Desc, g.name
'
SELECT database_name = d.name 
    , d.create_date
    , d.compatibility_level
    , LastFullBU = BUFull.last_backup  
    , LastDiffBU = BUDiff.last_backup  
    , LastLogBU = BULog.last_backup  
    , Recovery_Model = d.recovery_model_desc
    , Log_Size_MB = LogSize.Size_MB
    , Log_Pct_Used = Cast(100 * Used_MB / (Cast(Size_MB as Dec(20,2)) + .01) as Dec(20,2))
    , d.collation_name
    , LastFullLoc = BUFullLoc.physical_device_name
    , LastFullSize_MB = Cast(BUFullLoc.backup_size / 1024 / 1024 as bigint)
    --, LastDiffLoc = BUDiffLoc.physical_device_name
    --, LastDiffSize_MB = Cast(BUDiffLog.backup_size / 1024 / 1024 as bigint)
    , LastLogLoc = BULogLoc.physical_device_name
    , LastLogSize_MB = Cast(BULogLoc.backup_size / 1024 / 1024 as bigint)
    , d.is_auto_close_on
FROM master.sys.databases d with (nolock)
    LEFT JOIN (SELECT b.database_name, max(b.backup_finish_date) last_backup
        FROM msdb..backupset b with (nolock)
        WHERE b.type = 'D' 
            AND (b.backup_finish_date > @cutoffdate or @use_cutoffdate = 0)
        GROUP BY b.database_name
        ) BUFull ON d.name = BUFull.database_name
LEFT JOIN (SELECT b.database_name, max(b.backup_finish_date) last_backup
        FROM msdb..backupset b with (nolock) 
        WHERE b.type = 'I' 
            AND (b.backup_finish_date > @cutoffdate or @use_cutoffdate = 0)
        GROUP BY b.database_name
        ) BUDiff ON d.name = BUDiff.database_name 
    LEFT JOIN (SELECT b.database_name, max(b.backup_finish_date) last_backup
        FROM msdb..backupset b with (nolock) 
        WHERE b.type = 'L' 
            AND (b.backup_finish_date > @cutoffdate or @use_cutoffdate = 0)
        GROUP BY b.database_name
        ) BULog ON d.name = BULog.database_name 
    LEFT JOIN (SELECT f.physical_device_name, b.database_name, b.backup_finish_date, b.backup_size
        FROM msdb..backupset b with (nolock)
            INNER JOIN msdb..backupmediafamily f ON b.media_set_id = f.media_set_id
        WHERE b.type = 'D') BUFullLoc ON BUFull.database_name = BUFullLoc.database_name AND BUFull.last_backup = BUFullLoc.backup_finish_date
    LEFT JOIN (SELECT f.physical_device_name, b.database_name, b.backup_finish_date, b.backup_size
        FROM msdb..backupset b with (nolock)
            INNER JOIN msdb..backupmediafamily f ON b.media_set_id = f.media_set_id
        WHERE b.type = 'I') BUDiffLoc ON BUDiff.database_name = BUDiffLoc.database_name AND BUDiff.last_backup = BUDiffLoc.backup_finish_date
    LEFT JOIN (SELECT f.physical_device_name, b.database_name, b.backup_finish_date, b.backup_size
        FROM msdb..backupset b with (nolock)
            INNER JOIN msdb..backupmediafamily f ON b.media_set_id = f.media_set_id 
        WHERE b.type = 'L') BULogLoc ON BULog.database_name = BULogLoc.database_name AND BULog.last_backup = BULogLoc.backup_finish_date
    LEFT JOIN ##FileGroupSize LogSize ON d.name = LogSize.DB
WHERE d.name <> 'tempdb'
    AND d.state = 0 --Online

DROP TABLE ##FileGroupSize