Post reply

DB backup and restore plan

  • July 9, 2009 at 12:03 am

    #384948

    In our company, we are using several servers, but two of those are the most used, the production server and the reports server. then we also have an external backup device in which we direct our full and differential backups.

    we want to implement a new plan for our regular backup and restore process.

    here goes:

    we want to backup our database from the production server which will be directed and restored to our report server, then we also want our external device to have a copy of our backup.

    I am thinking of automating the copying process of the backup file from the report server instead of the manual right click, copy, then paste to external drive.

    how can i automate copying of backup file from the report server to the external device? or is there any other way we can execute our backup/restore plan?

    Thank you,

    Isabel

  • July 9, 2009 at 5:40 am

    #1022276

    This looks like a case for logshipping. Logshipping is the automation of creating a backup and restoring it on a standby server. This article can help you with implementing logshipping: http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx

    Next to the logshipping you can create a job that copies the backups made to an external device on a regular interval using a copy job in a sql job like this:

    xp_cmdshell 'copy pathtofile\file newlocation'

  • July 9, 2009 at 10:07 am

    #1022519

    The problem with log shipping is it leaves the database recovering. You can set this up to be readable, but when a new log comes in, everyone has to be kicked out of the database, which can cause issues with the reports.

    If you want to automate this, VBScript is the way to go. I assume you are making a new backup file name every day. There are plenty of scripts on this site that will give you an idea of how to do this. Basically you use the FileSystemObject to move the file, then set a restore job on the report server. I'd make the name on the report server (when you copy) the same so that is a simple restore.

    Here are a few scripts:

    http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/31932/

    http://www.sqlservercentral.com/Scripts/Backup+%2f+Restore/

    http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/30829/

  • July 9, 2009 at 12:26 pm

    #1022596

    Not that I know how to do it, but I would bet that you could do it with Powershell and this would be a good opportunity to learn some Powershell as it is being heavily pushed/promoted by MS.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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