SQLServer 2000: Error when run a job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQLServer 2000: Error when run a job

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-08-18 : 19:16:36

Hi there

We have 2 database servers (PRODUCTIONSERVER and STANDYSERVER). We would like to do database shipping every day from PRODUCTIONSERVER to STANDBTSERVER (no log shipping required).

We linked the STANDYSERVER in PRODUCTIONSERVER. We created a job which has 3 steps:

1. Truncate Transaction log

BACKUP LOG [OurDB] WITH TRUNCATE_ONLY
WAITFOR DELAY '00:00:05'

2. Backup Local Disk

USE [OurDB]
GO

BACKUP DATABASE [OurDB]
TO DISK = 'e:\data\backup\OurDB\OurDB_0000.bak'
WITH INIT,
STATS = 5
WAITFOR DELAY '00:00:05'
GO

3. Restore Remote Database

EXEC [STANDBYSERVER].master.dbo.usp_restoredbbackup_OurDB

Here's the usp_restoredbbackup_OurDB detail:

CREATE PROCEDURE usp_restoredbbackup_OurDB AS
BEGIN

ALTER DATABASE OurDB SET OFFLINE WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE OurDB
FROM DISK = '\\5.5.5.1\E$\data\backup\OurDB\OurDB_0000.bak'
WITH
REPLACE,
STATS = 5,
MOVE 'OurDB_data' TO 'E:\MSSQL$QRY01\Data\OurDB\OurDB.MDF',
MOVE 'OurDB_log' TO 'E:\MSSQL$QRY01\Data\OurDB\OurDB_log.LDF'
WAITFOR DELAY '00:00:05';

ALTER DATABASE OurDBSET ONLINE;

END
GO

If I ran this job, I always got an error on the 3rd step:

Executed as user: DomainName\SQLServerSrvAct. ...s_Log' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1807744 pages for database 'OurDB', file 'OurDB_Data' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent restored. [SQLSTATE 01000] (Message 3211) 95 percent restored. [SQLSTATE 01000] (Message 3211) 90 percent restored. [SQLSTATE 01000] (Message 3211) 85 percent restored. [SQLSTATE 01000] (Message 3211) 80 percent restored. [SQLSTATE 01000] (Message 3211) 75 percent restored. [SQLSTATE 01000] (Message 3211) 70 percent restored. [SQLSTATE 01000] (Message 3211) 65 percent restored. [SQLSTATE 01000] (Message 3211) 60 percent restored. [SQLSTATE 01000] (Message 3211) 55 percent restored. [SQLSTATE 01000] (Message 3211) 50 percent restored. [SQLSTATE 01000] (Message 3211) 45 percent restored. [SQLSTATE 01000] (Message 3211) 40 percent restored. [SQLSTATE 01000] (Message 3211) 35 percent restored. [SQLSTATE 01000] (Message 3211) 30 percent restored. [SQLST... The step succeeded.

When I look on SQL log on STANDYSERVER:

2006-08-19 06:05:55.58 spid51 Starting up database 'OurDB'.
2006-08-19 06:05:55.72 spid51 Error: 947, Severity: 16, State: 1
2006-08-19 06:05:55.72 spid51 Error while closing database 'OurDB' cleanly..
2006-08-19 06:47:25.83 spid51 Starting up database 'OurDB'.
2006-08-19 06:47:25.85 spid51 Bypassing recovery for database 'OurDB' because it is marked IN LOAD.
2006-08-19 06:47:26.14 spid51 Starting up database 'OurDB'.
2006-08-19 06:47:26.16 spid51 Bypassing recovery for database 'OurDB' because it is marked IN LOAD.
2006-08-19 06:47:27.02 spid51 Recovery is checkpointing database 'OurDB' (5)
2006-08-19 06:47:27.21 spid51 Starting up database 'OurDB'.
2006-08-19 06:47:30.13 backup Database restored: Database: OurDB, creation date(time): 2002/09/11(15:46:30), first LSN: 350037:216:1, last LSN: 350037:378:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\5.5.5.1\E$\data\backup\OurDB\OurDB_0000.bak'}).
2006-08-19 07:59:10.46 spid51 Starting up database 'OurDB'.
2006-08-19 07:59:10.57 spid51 Bypassing recovery for database 'OurDB' because it is OFFLINE.

Not sure why? But if I run just only 3rd steps which RESTORING BACKUP assuming the backup is already there. I work no problem. ?!?!

Any ideas? Or am I doing something wrong here to do database shipping doing this way?

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-08-19 : 11:59:43

if you're trying to simulate the logshipping process

1. don't truncate the log file, make sure db is in full recovery model
2. take a full backup then restore with standby or norecovery option
3. create a job that will backup the log file periodically (like every 15 minutes or depending on your business continuity plan)
4. add a step to the job in #3 to transfer the log onto the other server
5. on the other server, create a job that will read if the log backup have been transferred, probably a look up table or check if file exists (moved when restored) kind of plan
6. add a step on #5 to do the restore with norecovery or standby again

the standby option allows the database to be read only so you can verify if the changes were transferred over

HTH

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources