Restore Log Question

Question

Restore Log Question

A-245760

Newbie

Points: 2
More actions
March 15, 2006 at 5:42 pm

#113748

I have setup Log shipping for our Live database on a SQL2000 server. The Standby database is not currently used for running queries out of but will be shortly.
My question is when the Log files are restored do I need to stop all queries in the Standby database or does the restore do this itself or can they both be running at the same time (queries may be inconsistent but the restore still works and queries can still be produced)
The restore command I use is : RESTORE LOG @DBName FROM ISK=@BackupFileName">DISK=@BackupFileName WITH STANDBY=@UndoFile
thanks for your help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

skeane SSC Eights! Points: 877 More actions · Answer 1

The database will be unavailable to users during the restore. Assuming you are using SQLServer's log shipping rather than a home grown solution, check the box for 'Terminate users in database' to automatically kill any connections to the standby database before starting the restores.

Vee Hall of Fame Points: 3028 More actions · Answer 2

While setting up logshipping there is an option wether to terminate all user connected while doing restore. Select this option if u want to terminate terminate users while doing restore.else Your restore operation will be terminated.

A-245760 Newbie Points: 2 More actions · Answer 3

thanks for the replies. I only have Standard Edition so have to implement my own log shipping rather than using the Wizard in Enterprise Edition.

Vee Hall of Fame Points: 3028 More actions · Answer 4

I have done that using stored procedures and linked servers. and soon i will be coming up with GUI. I will let you know once I Finished that. Now i have only procedures which will do that.

skeane SSC Eights! Points: 877 More actions · Answer 5

Here's the code for a proc I use to kill all users for a database. It's not infallible, but it gets the job done most of the time.

CREATE PROCEDURE sp__killallusers

@dbname varchar(255)

AS

declare @spid int

declare @sql varchar(100)

declare c cursor for

select spid from master..sysprocesses where

db_name(dbid) = rtrim(@dbname)

open c fetch next from c into @spid

while @@fetch_status -1 begin

set @sql = 'kill ' + cast(@spid as varchar(15))

exec(@sql)

fetch next from c into @spid

end

close c

deallocate c

GO

Jim Dillon-291542 Hall of Fame Points: 3344 More actions · Answer 6

Has anyone created their own log shipping process between a 2005 and 2000 implementation? I also am stuck with Standard versions of these products and would like to set up my own transaction log shipping routine with stored procedures. However, I don't know where to start.

Thanks,

jim