September 10, 2008 at 10:59 pm
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'
September 10, 2008 at 11:59 pm
1) what's the info in the job step history.
2) your first step should work, you'll need to add ", replace" if you want to overwrite the current db !
3) you should not specify datafiles when performing a restore log.
4) your D drive should not be a mapped drive (usersettings).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 11, 2008 at 9:27 am
1) what's 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.
2) your first step should work, you'll need to add ", replace" if you want to overwrite the current db !
Not overwriting the current db. This db does not exist under db list.
3) you should not specify datafiles when performing a restore log.
I took out line below from 2 and 3 rd step.
MOVE 'test_Data' TO 'D:\Microsoft SQL Server\Data\test_Data.mdf'
4) your D drive should not be a mapped drive (usersettings).
It is not.
I started the job again but still not restoring the db.
September 12, 2008 at 12:18 am
did the "restore database" step succeed ?
(what's it output)
can you perform sp_helpdb 'test'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2008 at 2:56 pm
restore db 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
I would appreciate your help. TY
September 13, 2008 at 7:13 am
Sqlchamp (9/12/2008)
restore db 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
I would appreciate your help. TY
In your first posts you pointed to bak files on D-drive !
What's the error this step produced ?
(jobstephistory)
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'
so this should at least be:
FROM DISK = 'E:\....\test_db_200809100300.BAK'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 15, 2008 at 3:53 pm
I tried couple of different things and it worked finally. Logical name and physical was not matching.
Thank you all.:)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply