March 28, 2003 at 12:08 pm
I work for an ASP. We have about 30 databases currently that we backup nightly, compress, then securely transfer to backup servers in another state. I am trying to automate the process of restoring these databases to the backup server. I have written a scrips which automate backing up all of the databases, zipping them, transferring them, unzipping them, and finally restoring them to the backup server.
Everything works great until I get to the part where they databases are restored onto the backup server. For some reason intermittently one of the databases fails to restore. It is left in a suspect state with the error message that says that one of the log files is corrupted. I was not able to find a solution to the problem so I eventually rewrote the script to first drop the database, create a new one, then restore the backup on top of it. Now I get a different error message. Msg 913, Sev 16: Could not find database ID 32. Database may not be activated yet or may be in transition. [SQLSTATE 42000].
Any ideas? I would love to modify the job further to actually read the databases out of the zip file and restore them all on the server whether they allready existed or not, this would save me from having to manually create a database on the backup server when a new customer was added to the main server but I have avoided implementing this feature since I can't even get the main script to run reliably yet.
- Shane
Shane
March 31, 2003 at 8:00 am
This was removed by the editor as SPAM
April 2, 2003 at 12:32 pm
I notice in the banner for the site an advert for
perhaps you should trial that ?
April 4, 2003 at 6:23 am
I don't see the need to try SQLZIP in my case, the problem is not with the zipping, transferring, or unzipping steps, they all work flawlessly. The problem is with writing a script that will reliably restore all of the databases from one server to a backup server.
Shane Delmore
Clinicient
Shane
April 4, 2003 at 6:52 am
presumably the steps are
1. backup on source server
2. ZIP dump file
3. push [source] or pull [target] to target
4. unZIP dump on target
5. restore dump on target
what you imply is that steps 2-4 are flawless so it is just like a local dump-restore ie steps 1,5 ??
do you ever get failures restoring other dumps [taken locally] on the target server ?
Are your dumps corrupt ? Can you restore each individual dump file [other than the 30-db run] ?
you could find out the status of a particular db by looking at the master..sysdatabases.status values before/after your operation.
If you are restoring all db's via a cursor on the target server, it is possible that you have struck a resource problem [cursor operations may be greedy] You should ensure one db restore has completed before starting next [maybe a WAITFOR statement is indicated].
The other scenario is that you tried to restore master [from source] onto the target machine. Not a wise operation and akin to sawing off the branch you are sitting on
- hopefully you didn't do that !?
Otherwise I guess the usual gamut of ServicePacks [O/S and MSSQL], TechNet K/B etc might be appropriate.
best of luck
Dick
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply