Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2005
    Posts
    75

    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:
    Code:
    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
    I think I'm pretty close with this, but I'm not really sure how I can stack the trn restores on top of that. Any ideas? I've been putzing around the web and so far haven't been able to come up w/ anything really useful.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Here is a template for your log restore:

    Code:
     
    restore log DBAOnly
    from disk = 'D:\MSSQL\Backup\InterchangeBTM\InterchangeBTM'  
     , with NORECOVERY 
     
    -- change to 'with RECOVERY' for final log
    Now if you are going to use a cursor to restore your logs, just use NORECOVERY in the cursor loop, then when your fetch <> 0 and you exit and deallocate the cursor, build and execute a line like this:

    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 --

  3. #3
    Join Date
    Aug 2005
    Posts
    75
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •