| Author |
Topic |
|
mark_b
Starting Member
24 Posts |
 Posted - 2007-07-09 : 12:35:04
|
| Hi,I have just taken over the DBA role for an application which at best you can describe their DR plans as woeful! So basically i am starting from scratch and trying to automate the database re-creation as much as possible. I have managed to write some bespoke scripts for backup devices, and user ids etc., but the main bulk of the work is in the re-creation of the 300 databases they have (boy i wish i had been here when they did this database design!!).Anyway, onto my question. I know that i can go to each database in SQL Enterprise manager (SQL 2000 by the way), and right click and select generate scripts and it will do it for me, but i was wondering (and hoping) that there was a API interface that i could utilise which would allow me to pass in the database name and for it to generate the scripts automatically. Height of laziness i know, but it would allow me to generate the scripts for the databases on a regular basis in order to keep the DR recovery scripts up to date.Thank you in advance for your help, even though i am guessing the answer will be "Tough, you have to do it by hand!"RegardsMark |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-09 : 12:58:58
|
| I agree with Brett.Scripting out the objects is not what is needed for a disaster recovery. You should be working on getting your backups to another server for DR.So when a disaster occurs, what do you intend to do with all of these scripts you've generated? What is the purpose? What about the data?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-09 : 13:20:12
|
| There is a script here which, given a folder full of BAK files, will Restore the whole lot. That would sure help with getting 300 Databases created and the latest Backup restored!You'd still need to sort out the Logins in MASTER etc.Getting your DR machine identical to the Production server (i.e. same drivers & folders and Service Pack) solves that - just copy the MDF/LDF for the System databases to the appropriate folder - that gets you all the logins, backup jobs, DTS stuff, and the rest - then restore all the latest backups and you are back in business. (I've slightly glossed over some things there, but that's the outline)Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-07-09 : 13:34:49
|
| Do you have a backup box?Is it in the same site?Tara, I'm suprised you didn't post thishttp://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspxIn any Case, let's assume you have another box you can use, on the networkUse Tara's procedure, ceate a cursor usingSELECT name FROM sysdatabasesThen build in dynamic sql that calls the proc with the name from the cursorCopy all of those backups to the other box, and restore them to make sure they are good (Tara, you have that somewhere)Then make sure you set each database up to dump the logs every n minutes, ship those over top the other boxSet up a job to do a night dump, copy the dumps across, delete the old tranny logs, and restore the latest dumpwhen the box goes down flip the ip and you're back in businessBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
mark_b
Starting Member
24 Posts |
Posted - 2007-07-09 : 13:35:30
|
| ok ... your outline pretty much covers what i was trying to do with the scripts re-creating the .mdf and .ldf files, setting up the security, and then restoring the .bak files. But if all i have to do is restore these files from the OS Backup tapes, then we are golden!Getting machine identical to prod wont be a problem, so that looks like it pretty much covers it.Many thanks for all your help. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-09 : 13:42:56
|
| "But if all i have to do is restore these files from the OS Backup tapes"Well you could ...... but better would be to be doing that hourly (or more often) on the DR machine so that a) it is bang up to date and b) you KNOW that you backups files CAN be restored.Getting the stuff off tape when the Worst Happens is a bit fraught:Is the tape readable (can we buy a tape drive of the same type any more?)Did the database grow huge because of some exceptional transaction just before the last backup? (and now its too big to restore)... and so on and so forth ...I'm sure you get the picture! Not a good time to turn a Drama into a three-part-mini-series!!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-09 : 13:47:58
|
| All skins have the overlap issue. The skin I chose had the least issues with my code.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-07-09 : 13:52:37
|
| I wish Bill had never changed themDo you compile these things in master?Cannot add rows to sysdepends for the current stored procedure because it depends on the missingobject 'master.dbo.xp_backup_database'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'master.dbo.xp_backup_log'. The stored procedure will still be created.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
mark_b
Starting Member
24 Posts |
Posted - 2007-07-09 : 13:52:39
|
| oh how i wish we had the hardware to do that!! however, the DR plans for this place utilise hardware at a 3rd party supplier that we get to use in the case of a disaster (ie its shared and not owned by us!). So basically ... things go BOOOM, we call this 3rd party who start building hardware to our specifications whilst we are in transit (if we are still alive that is!) and once we are onsite, we have 48 hours to get everything back up and running. They do not seem to believe in fail over, or cluster switching on remote sites etc which would be my preferred option as you dont even have to worry about backups so much then just transaction log replication ... but hey, you have to make do with what you are given. Thank you for all your help on this one, it is much appreciated. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mark_b
Starting Member
24 Posts |
Posted - 2007-07-09 : 13:57:24
|
| that includes all the travel / OS restore / firewall & network connectivity. my part on the database side will be how ever long it takes me to instal SQL Server and restore the databases ... hopefully less than 4 hours depending on tape restore speeds! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-09 : 13:57:38
|
quote:
Originally posted by X002548
I wish Bill had never changed themDo you compile these things in master?Cannot add rows to sysdepends for the current stored procedure because it depends on the missingobject 'master.dbo.xp_backup_database'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'master.dbo.xp_backup_log'. The stored procedure will still be created.
Those aren't errors, just warnings. You don't have SQL Litespeed installed, so you won't have those extended stored procedures. My code supports both native backups and LS backups. You can still use my code even if LS isn't installed.I put everything in a database named Admin, which will be renaming to DBA shortly. I don't add any code to master.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-09 : 14:51:57
|
| "but hey, you have to make do with what you are given"Hehehe ... OK, fair enuf!Just make sure you restore those BAK files form Tape often and prove that you can restore them OK - e.g. to a TEMP database on your normal server, but better still to A.N.Other server (just to check no dependence on any specific server).("Often" for 24/7 shops would be every backup, including TLog backups.)After you have restored the BAK file do a DBCC CHECKDB('MyTempDatabaseName') WITH NO_INFOMSGSto check that the restore was good, then you can throw the Temp Database awayKristen |
|
|
|
|
DR Scripting Automation