October 12, 2004 at 9:13 am
I need to disable/remove master/model/msdb to test our backup/restore system databases. I know about the -c -m on the startup, but unfortunately in the the system we use has a bug and won't allow a "overwrite" of an existing database.
Any ideas?
Thanks,
Joseph Devereaux
October 12, 2004 at 10:39 am
Joe,
What do you mean disable? Are you trying to test a restore of Master?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 12, 2004 at 11:09 am
Yes, I need to test a restore of the master/model/msdb databases.
We use a product called Tivoli (by IBM) to do all our backups (oracle, sql, adabase, nt, ect.) to the tape robitic system. You use Tivoli scripting language to do the tape backups/restores.
Typically, when doing a duplication (prod to dev) or restore (prod to prod), I delete the database and then restore it. There is a "over write" parameter in Tivoli, but it has a bug and a solution has come.
What I was hoping to do is bring SQL Server up in single user mode (-c -m) then over write the databases. That is not working, so I am trying to find an alternative. I have not found a way to delete/drop/detach system databases.
This has mainly been an issue because of Disaster recover procedures. For the SQL systems we have, I have scripts to drop/create users and permissions. Made notes about jobs, dts, ect to re-create manually. Management has deemed this unacceptable and I have to recover the entire SQL instance, not just the user databases.
Thanks for any advice/hints you may have.
Joseph Devereaux
October 12, 2004 at 11:53 am
Could try this....stop the services. That takes all the databases offline. COPY them to another location (or to a new name like master.old). Then delete the system databases.
Then do your restore.
-SQLBill
October 12, 2004 at 12:16 pm
A copy/paste of the master works.
But this does not work for the restore. When Tivoli does a restore, it tries to connect to the database. If the instance is not up, it stops and errors out.
This did give me an idea. I am going to stop the sql server instance. Delete the model/msdb. Start up sql server in -c-m mode, then do the restores.
It might be good enough for me to install an instance OR use the SQL utility to rebuild the master. Then restore the system database, then the user databases. I will post my results.
Thanks,
Joseph Devereaux
October 13, 2004 at 12:52 am
Restoring the master Database from a Current Backup
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4g4w.asp
Tivoli Data Protection for Microsoft SQL Server Installation and User's Guide
(Restoring the master database)
http://publib.boulder.ibm.com/tividd/td/DPSQLN/SH26-4111-02/en_US/HTML/ab5m1m13.htm#Header_122
Julian Kuiters
juliankuiters.id.au
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply