More Intelligent Backup and Restore

  • Kevin,

    I'm working on updates to the procedures to meet these requests. I have the backup log stuff working, and the {ServerName} replacement as well. What I'm currently debating is how best to handle the asterisk when restoring transaction logs.

    Applying just the latest file doesn't sound like the right answer, since it won't work if there was another log backup since the last database backup. I can't assume the database backup is in the same directory or conforms to a certain naming convention. What I need to do is either:

    1. Find out the MinLSN that the database is using, and find the transaction logs that follow using the RESTORE HEADERONLY command. This is what I'd prefer, but I'm not certain how to read the MinLSN from the database boot page. I've asked a question on a forum, and I'll see if I get a good reply.

    2. Start with the most recent transaction log and attempt to restore it. If it fails, attempt to restore the next most recent, etc., till it works, then restore all the ones that didn't. This may not work since I'm not certain I can capture the errors without it blowing up the batch.

    I know I can skip these difficulties for the restore and just publish the backup that's working, but I really mean for these routines to be complimentary. Give me a little bit to see if I can work out a good answer.


  • Thank you

    We will wait patiently.


  • I update the scripts to work on SQL Server 2005.

  • fyi, I updated the scripts to work on SQL Server 2005.

  • Came across scripts today and they work a treat. Also as per prevoius posts any update on transaction logs capability?



  • Herman,

    Sorry, no. Job time constraints and family responsibilities leaves me not enough time to finish. At this point I wouldn't hold your breath...


  • I have modified the scripts quite a bit and I have log file backups scripted.  I was not too concened about log file restores because any DR will have a lot of hands on attention and scripts will more than likely not be used.

    My scripts are chopped up quite a bit.  I also added some additional features.  If anyone wants to my scripts to clean them up, document and publish them, let me know and I will send them to you.


  • I donwloaded the scripts from Phil Cruz


    The Backup part works like a charm but when I tried to run the Restore part I got the following error:


    EXEC sp_ABRestoreDb 'dnndb', 'c:\Temp\dnndb.BAK'


    RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'

    Msg 22001, Level 1, State 1

    Msg 213, Level 16, State 7, Line 1

    Insert Error: Column name or number of supplied values does not match table definition.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE FILELIST is terminating abnormally.

    Msg 50000, Level 16, State 1, Procedure sp_ABRestoreDb, Line 458

    Couldn't restore database dnndb from file c:\Temp\dnndb.BAK since the number/type of logical devices do not match.

    Any Ideas ????

  • Luis,

    I didn't know the scripts were posted on Phil Cruz's blog. The link seems broken when I try it. Have you tried following the link from the article (the original source code)? If that doesn't work reply here and I should be able to help.


  • Vince,

    As allways, thank you very much for your help. Here is the link to Phil Cruz's website. It should work this time.

    I downloaded your original source code and have been using it for a while. When I upgraded to SQL 2005, I started to get errors and found Phil Cruz's fixes.

    Do you have a newer version that supports SQL 2005?


  • Hey Vince, still around?

    I was wondering if you have a newer version that supports SQL Server 2005. I tried to "fix" the issue myself but my little brain doesn't get that far.


  • Luis,

    Yes, still here and watching the thread. I need a week or so to clean this up, but haven't been able to get the time. The day job is demanding lately.

    Along with SQL 2005 improvements, I need to handle the next version of SQLBackup. They really messed me up by returning two result sets, the first one with one column and the second with two. There's no way to EXEC ... INSERT that into a table, so there's no great solution at this point.

    Anyway, I will post when I get a chance to update it...


  • Vince,

    Thank you for your reply. I'll be watching this thread anxiously for any updates you may have.

  • Hi Vince, I just wanted to know if you have any updates on your scritps to support SQL Server 2005, mainly the Restore one.


  • Luis, what I have isn't ready for general publication, but it kinda works. Send your personal email to me at my first name dot my last name at gmail dot com and I will send you what I have.

    Sorry, just too busy these days to finish it off.

Viewing 15 posts - 16 through 30 (of 44 total)

You must be logged in to reply to this topic. Login to reply