Post reply

Database Restore via Maintenance Task

  • June 2, 2008 at 10:29 am

    #187335

    Hello Everyone

    I would like to take the latest Database backup file that one of my maintenance tasks creates, and restore that backup to the development server, where there is an identical database stored. How can I select the latest database backup file, since it has the time and date stamp as part of the file name? And how can I restore that file over top of, and over write the database that is currently on the development server? I will want to fire off this process every day.

    I need to be able to select the backup file that is named something like:

    DatabaseName_FullBackup_200806020330.bak

    And restore that over a database of the same name as where this backup was taken. This is going from Prod to Dev. I will then modify the login and user for this database, since I do not want the Dev and Prod environments using the same login.

    Can I perform this task using a Maintenance Task? Or will I have to create a SSIS Package?

    Thanks

    Andrew SQLDBA

  • June 2, 2008 at 10:39 am

    #823082

    You can't easily do this in the maintenance plan. There needs to be logic to find the latest file, or read the msdb tables, to do this. It's not trivial, but not that hard.

    There are scripts there on the site that can help with finding the latest backup, and an Execute SQL task can run a restore.

    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

  • June 2, 2008 at 10:54 am

    #823096

    Thanks for the reply

    I have been looking at some of the scripts that I have found in other posts. However, we do not allow the use of xp_cmdshell to be used.

    Is there a way to turn it on and then back off?

    Thanks

    Andrew SQLDBA

  • June 2, 2008 at 11:21 am

    #823130

    Try the code provided here:

    http://msdn.microsoft.com/en-us/library/ms190693.aspx

    Kyle

  • June 2, 2008 at 12:35 pm

    #823188

    Insert the Restore Header only information into a table and check the latest backup and restore...

    http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp

    MohammedU
    Microsoft SQL Server MVP

Viewing 5 posts - 1 through 4 (of 4 total)

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