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'
- Disaster Recovery and Business Continuity (simplesqlserver.com)
Filed under: Backups, Scripts, SQL Server Tagged: backup, Full Recovery, Logs, recovery, recovery model