Transaction Log replication (log shipping) is the method that you use to
create a warm standby server.
Here is my "canned" response. I am using this method with much success.
Others are also using this method:
It is a common misconception that you need SQL2k or the Back Office Resource
Kit to use log shipping.
If you read up on warm standby servers within Books Online, you will get
lots of good information.
It is easy to "roll your own" log shipping
the basics are
1. full database backup on primary server
2. restore database to secondary server WITH STANDBY
3. backup transaction log on primary server
4. restore transaction log to secondary server WITH STANDBY.
repeat steps 3 and 4 as often as needed.
You can bring your secondary server online if you restore WITH RECOVERY.
You can also automate the entire process.
This is the method that I used:
On my primary server, I have set up a linked server to the secondary server
(I replaced the server names with Primary and Secondary here)
Primary server:
--backup database job (nightly)
BACKUP DATABASE foo to dmp_foo WITH INIT
GO
The job on the primary server starts this job on the Secondary server:
--restore the database
exec usp_KillTheUsersConnected 'foo' --kills the users connected to the
database
go
RESTORE DATABASE foo
FROM DISK = '\\Primary\D$\dmp_foo.BAK'
WITH FILE = 1, NOUNLOAD , STANDBY = 'E:\mssql7\logs\undo.ldf', STATS =
10, REPLACE ,
MOVE N'foo_Data' TO N'F:\MSSQL7\data\foo.MDF',
MOVE N'foo_Log' TO N'E:\mssql7\logs\foo.ldf'
go
Now, throughout the day, I backup the logs on the primary server:
BACKUP LOG foo to dmp_foo_LOG WITH INIT
/*I also zip and copy the log file to another server at this point.*/
The job listed above starts this job on the secondary server:
exec usp_KillTheUsersConnected 'foo' --kills the users connected to the
database
go
RESTORE LOG foo
FROM DISK = '\\Primary\d$\dmp_foo_LOG.BAK'
WITH FILE = 1, STANDBY = 'E:\mssql7\logs\undo.ldf'
If you ever want to bring your secondary server online, you need to restore
WITH RECOVERY
RESTORE LOG foo
FROM DISK = '\\Primary\d$\dmp_foo_LOG.BAK'
WITH FILE = 1, NOUNLOAD , RECOVERY
/*
an article written on the subject that holds your hand a bit more than my
simple write up does
http://www.sql-server-performance.com/sql_server_log_shipping.asp
*/
--
Keith, SQL Server MVP
Quote:> hello
> My company using standard SQL Server7.now we want to create
> standby server for any emergency that will be in dfferent
> location.i wanted to know which is method better
> transection replication or standby server.
> i have tried transection replication in that
> subscription server i have to remove all constrain
> of prymary & foreing key then only it work.
> i wanted know that in transection replication we
> have to remove this contrain or i am somewhere wrong.
> for this reason i am not using this option.
> so other is standby server if you help by guiding me how
> to create standby server then it will be great help for me.
> Thank you
> kalpaj
by