02-17-06, 13:40 #1Registered User
- Join Date
- Aug 2005
Unanswered: script .bak restore w/ .trn files
I'm the DBA in an environment with approximately 240 identical (structurally) MSSQL 2000 databases in an environment. Each one of these databases are different business units around the country. For each of these DBs, I have my backup schedule set up to do a full backup every friday night, along w/ doing a transactional every 4 hours in between.
Every once in a while (about once every 3 weeks), I get the call to do a restore to pull some piece of data that some end user accidentally deleted or broke. Now most of the time it isn't a big deal, because I can use enterprise mgr to restore to a new DB from the live backup set of the database in question.
But, several of the databases seem to have an issue where (for some reason) my predecessor renamed the logical file names of the data and log files to various goofy things. It doesn't affect their operation, but when I try to do a 'restore from database,' it gives me a bunch of big fat errors. This is also the case when the backup I want to restore something that has been written to tape and has been rolled out of the backup history.
SO... basically for these databases, now I go into Enterprise mgr and first restore the base .bak file to a new database, leaving it marked 'read only' and 'able to restore additional transactions.' I then have to do the same thing with restoring each additional TRN file, all the while making sure I don't forget to set the 'able to restore additional transactions.' Whenever I forget to set that I have to start all over. And honestly, it happens more often than not when I have to restore 5 days out from the .bak file, which means that I have restore 30 trn files on top of the .bak. Not hard, but incredibly tedious.
So here's my idea. I know I can script restores thru QA. I've been looking quite a bit at this MS doc and have come up with some good ideas. What I want to eventually do is create a script that you can just paste the .bak file and the list of .trn files. The script basically creates a temp table w/ those file names and cursors thru the restore process. The cursor part is easy for me, I'm just not sure about scripting the trn restore. The MS doc is a bit vague and really only shows the Enterprise Mgr method.
Here's what I've been playing w/ so far for restoring the base .bak file:
restore database restore_dbname --NAME OF NEW DB from disk = 's:\mssql\backup\user\dbname\dbname_db_200601201841.bak' --PATH TO BAK FILE with move 'pm65mr1_default_data_OldDBName_Data' --LOGICAL FILE NAME OF ORIGINAL MDF to 'I:\MSSQL$MSSQLSERVER2\Data\restore_dbname.mdf' --PATH TO NEW MDF move 'pm65mr1_default_data_OldDBName_Log' to --LOGICAL FILE NAME OF ORIGINAL LDF 'U:\MSSQL$MSSQLSERVER2\Log\restore_dbname.ldf' --PATH TO NEW LDF partial, recovery
02-17-06, 14:44 #29th inning DBA
- Join Date
- Jan 2004
- In a large office with bad lighting
Here is a template for your log restore:
restore log DBAOnly from disk = 'D:\MSSQL\Backup\InterchangeBTM\InterchangeBTM' , with NORECOVERY -- change to 'with RECOVERY' for final log
RESTORE database <DBName> WITH RECOVERY
that will finish the roll forward and rollback and make the database available.
-- This is all just a Figment of my Imagination --
02-17-06, 16:21 #3Registered User
- Join Date
- Aug 2005
awesome! thanks... I figured it would be something simple like that. I'm gonna have a go at it later today and see if I can get a manual DB restored.