sql server replication: Hi, I'm relatively new to SQL. I have a it set up on 2servers. Windows
2000 Server, with SQL 2000 Standard on server1 and server2.

Server 1 has a live database, which is backed up nightly. I want to take
that nightly backup, and load it onto server2. I want this to occur every
night, so sever2 will have a day old copy of the database on it.

How do I go about doing this?

thank you

James,
if you had enterprise edition you could use log shipping, which is an
automated way of doing what you want, but by moving and restoring the
transaction logs. However with standard edition and only being interested in
the databases and not the logs, it's just as simple to set it up manually.
Just create a job on Server1 to do the backup. The backup device on Server1
must exist on a network share accessible by Server2. Create another job on
server2 to do the restore. Schedule the jobs to occur daily but to be
slightly staggered - the backup one occurring before the restore. When you
restore the database by default it will try to restore to the same disk
location as it was cacked up from, but this is configurable using with move.
The commands for backup database and restore database are both in books
online (BOL).
Regards,
Paul Ibison

Paul

This issue is exactly what I am dealing with too. I have Standard Ed. and I have a development team asking for a standby server to be setup that can be available for read access by supervisors, but can also assume the primary server role if necessary. Will doing the backup scenario you just described meet that need? Are there simpler options for accomplishing this without the expenditure for Enterprise Ed.

Thanks

Lee
logshipping out of the box requires enterprise edition, however it doesn't do anything that can't be achieved with a bit of scripting. In my previous post I mentioned backing up and restoring the databases because James only needed a nightly process, and was backing up his databases once a day, which is I suppose "database shipping". If you want a warm standby server then you need to use log shipping. You could use the methodology from the previous post and apply it to the logfiles, or better still there are plenty of people out there who have done this already and posted up the scripts. For a start you could look at: http://www.sql-server-performance.com/sql_server_log_shipping.asp or the SQL Server 7 resource kit
Regards

Paul

Paul

I've used the methods described in that article you referenced above and after some hitches I've got the process working. I only have a couple of hurdles I can't seem to get around. Basically what I have done is this
1. Running a 4 step job on the production server that A.truncates log B.backsup database C. OS command copy backup file from production server to standby server D. call a stored procedure on the standby server that restores the databas
2. Running a 3 step job on the production server that A. Backsup the transaction log B. OS copy the backup file to the standby server C. call a stored procedure on the standby server to restore the transaction lo

My problem is that when I try to run the transaction log restoration, it fails because the restored database on the standby server is read only. When I try to manually change it, I get a message like this: "Error 5063: Database 'UserDetails is in warm standby. A warm-standby database is read-only. ALTER DATABASE statement failed. sp_dboption command failed"

What am I missing

Here is a copy of my restoration stored procedure

CREATE PROCEDURE usp_RestoreUserDetailsD

A

RESTORE DATABASE UserDetail
FROM DISK = 'c:\sqlbkp\UDBkpDb.bak
WIT
DBO_ONLY
REPLACE
STANDBY = 'c:\sqlbkp\undo_userdetailsdb_restore.ldf

MOVE 'UserDetails_Data' TO 'c:\sqldata\UserDetails_Data.mdf
MOVE 'UserDetails_Log' TO 'c:\sqldata\UserDetails_Log.ldf

WAITFOR DELAY '00:00:10

EXEC sp_dboption 'UserDetails', 'single user', tru
G

Lee,
the TSQL you posted isn't for log restoration - it's the database backup.
You're restoring in RO mode (STANDBY) so the EXEC sp_dboption 'UserDetails',
'single user', true statement won't work. If you want to prevent write
access to the standby server then it will be read-only anyway, so in that
sense there is no need to make it single-user. If you don't want anyone at
all to access the database you can use NORECOVERY rather than STANDBY,
otherwise you can leave the mapping of logins to users until failover so
nobody can connect that way.
Process 1 refers to database shipping, process 2 log shipping. As long as
these are referring to different databases it looks ok.
Regards,
Paul Ibison