SQL 2005 Transaction Logs

15 pts.
Tags:
SQL Server 2005
SQL Server administration
SQL Server transaction logs
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.
<pre>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</pre>

The code for the second step will be something like this.
<pre>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</pre>

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,050 pointsBadges:
    report
  • Danyluk
    Okay, thank you so much. I am going to look at this some more tonight and give it a try. The transaction logs do have the timestamps. I will let you know how it goes.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following