SQL Jobs - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Jobs

Author

Topic

SQL360
Starting Member

20 Posts

Posted - 2008-09-10 : 19:25:32

Following script to restore db and tran log works just fine via QA.
I created a job to restore it automatically using the same script below in the transact sql command box in the create job steps and ran job successfully but it is not restoring the database and tran log.
What could be the possible problem?
Inside the job I have two steps and on each step I have each of the
script below.
Thank you.

RESTORE DATABASE test
FROM DISK = 'D:\Microsoft SQL Server\BACKUP\test_db_200809100300.BAK'
WITH NORECOVERY,
MOVE 'test_Data' TO 'D:\Microsoft SQL Server\Data\test_Data.mdf',
MOVE 'test_Log' TO 'D:\Microsoft SQL Server\LOG\test_log.ldf'
------------------
RESTORE LOG test
FROM DISK = 'D:\Microsoft SQL Server\BACKUP\test_tlog_200809100650.TRN'
WITH RECOVERY,
MOVE 'test_Data' TO 'D:\Microsoft SQL Server\Data\test_Data.mdf',
MOVE 'test_Log' TO 'D:\Microsoft SQL Server\LOG\test_log.ldf'

SQL360

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-09-12 : 01:50:32

Could you post the Error Message you are getting . If you haven't specifically requested the output onto a log file , you can get information from studing the Job History Steps

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

SQL360
Starting Member

20 Posts

Posted - 2008-09-12 : 17:25:47

the info in the job step history.

The job succeeded.
The Job was invoked by User test\dbadmin.
The last step to run was step 3 (Restore test Database Tran Log Final).
The job was requested to start at step 1 (Restore test Database).

Third step:Could not find database ID 65535. Database may not b
e activated yet or may be in transition.
[SQLSTATE 42000] (Error 913) RESTORE LOG is terminating abnormally.
SQLSTATE 42000] (Error 3013). The step failed.

Second step:Could not find database ID 65535.
Database may not be activated yet or may be in transition.
[SQLSTATE 42000] (Error 913) RESTORE LOG is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.

First step: Logical file 'test_Data' is not part of database 'test'.
Use RESTORE FILELISTONLY to list the logical file names.
[SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.
--------
sp_help db test gave result test db does not exist.

Again in a nutshell I am trying to automate db and tran log restore via sql job.

I have these files on the E:drive
test_db_200809120300.BAK
test_tlog_200809120250.TRN
test_tlog_200809120650.TRN
test_tlog_200809121050.TRN
test_tlog_200809121450.TRN
test_tlog_200809121850.TRN

SQL360

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-14 : 00:35:04

quote:
Originally posted by SQL360

the info in the job step history.

The job succeeded.
The Job was invoked by User test\dbadmin.
The last step to run was step 3 (Restore test Database Tran Log Final).
The job was requested to start at step 1 (Restore test Database).

Third step:Could not find database ID 65535. Database may not b
e activated yet or may be in transition.
[SQLSTATE 42000] (Error 913) RESTORE LOG is terminating abnormally.
SQLSTATE 42000] (Error 3013). The step failed.

Second step:Could not find database ID 65535.
Database may not be activated yet or may be in transition.
[SQLSTATE 42000] (Error 913) RESTORE LOG is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.

First step: Logical file 'test_Data' is not part of database 'test'.
Use RESTORE FILELISTONLY to list the logical file names.
[SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.
--------
sp_help db test gave result test db does not exist.

Again in a nutshell I am trying to automate db and tran log restore via sql job.

I have these files on the E:drive
test_db_200809120300.BAK
test_tlog_200809120250.TRN
test_tlog_200809120650.TRN
test_tlog_200809121050.TRN
test_tlog_200809121450.TRN
test_tlog_200809121850.TRN

SQL360

Your restore statement doesn't look right.
Restore first full backup with Norecovery and 4 TRN files with Norecovery and last TRN file with Recovery.
any specific reason to run this as a job? Do you get same files everyday? Datestamp might be different.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-14 : 00:55:41

Try putting them in one job step.

You need to verify if test_Data and test_Log are the logical names as the error indicates they aren't. Run RESTORE FILELISTONLY to see what names to use.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SQL360
Starting Member

20 Posts

Posted - 2008-09-15 : 17:38:19

It finally worked. Logical and Physical name was not matching.
Thank you all.

SQL360

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources