Question

SQL 2005 Transaction Logs

SQL Server administration, SQL Server transaction logs, SQL Server 2005

I am by no means very good at SQL. I know the basics. I am a network engineer but was recently asked to do a few tasks in a SQL 2005 environment. I currently have a database that is residing off-site and I was asked to import the database and transaction logs so that this server can be used for reporting purposes only. I have to restore transaction logs on a daily basis and left the database in "Standby" "Read Only" Everything is working fine but two things are happening. One is that I have to do this task everynight which is rather annoying and secondly I have to restore these transaction logs one at a time. I try to restore all 26 logs in order but it will not let me. I can only do one at a time and that sucks. I am trying to accomplish the following. I want to be able to have this done automatically and leave the database in Read Only/Standby when it is done. Can someone point me in the right directions?

Thank you.

That can easily be done via T/SQL directly.

I assume that your transaction log backups have the date/time stamp when they were created in the filename? If not can you add it to the filename? Having the date and time in the filename makes the entire procedure a lot easier.

To do the import nightly you'll need to use a SQL Job to kick off the work.

As for the code within the job, I see two steps of the job. The first one will kill any people who are using the database. The second will restore what ever transaction log files are in the folder, removing the files after they are processed. If you don't kill any users within the database before starting the restore process.

Both steps of the job should be set to run in the master database. The code from the first step I use all the time. The second step isn't tested, but should work. You'll want to test it before deploying it to production.

The code for the first step will be something like this.

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 the second step will be something like this.

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:\Path\To\Your\Log\Backups\' + @FileName 
 	RESTORE LOG YourDatabaseNameHere FROM DISK=@FileName WITH STANDBY='D:\Path\To\Your\StandBy\File.standby'
 	IF @@ERROR <> 0
 		exec xp_delete_file @FileName
 	fetch next from cur into @FileName
 END
 close cur
 deallocate cur