How to setup a server to read log files nightly.

Published On: 2008-08-25By:

So you’ve been tasked with setting up a quick and dirty reporting server.  The goal is to restore the log files from the production server to the reporting server nightly.

 The backups are simple, use the SQL Maintenance plan to backup the logs, and then copy them to the remote machine.  But how do you restore the logs to the reporting server nightly.

 Well I’ve got a two step SQL job which should help you out.

Step 1 kills all current sessions in the database, and step 2 does the actual restores.

The code for step 1 is: 

declare @spid varchar(20)
declare cur CURSOR FOR
select spid
from sys.sysprocesses
where dbid = db_id('Your Database Name Here') /*<---Put your database name here*/
and spid > 50
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
exec ('kill ' + @spid)
fetch next from cur into @spid
END
close cur
deallocate cur

The code for step 2 is:

create table #Files
(FileName nvarchar(4000),
Depth int,
IsFile bit)
insert into #Files
exec xp_dirtree 'd:', 1, 1

delete from #Files
where IsFile = 0

declare @FileName nvarchar(4000)
declare cur CURSOR FOR SELECT FileName from #Files
open cur
fetch next from cur into @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName = 'D:PathToYourLogBackups' + @FileName
RESTORE LOG YourDatabaseNameHere FROM DISK=@FileName WITH STANDBY='D:PathToYourStandByFile.standby'
IF @@ERROR <> 0
exec xp_delete_file @FileName
fetch next from cur into @FileName
END
close cur
deallocate cur

I hope this makes your process easier. Now this code only works on SQL Server 2005 and up as the system stored procedures which I use were not included until SQL Server 2005.

Denny

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via