DB backup/restore process

  • 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

  • isabel (7/9/2009)


    In our company, we are using several servers, but :

    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.

    Isabel

    Cheers,

    Got an idea..share it !!

    DBA_Vishal

  • isabel (7/9/2009)


    In our company, we are using several servers, but :

    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 this case you can backup the DB to two different location . 1. Report server 2. External device using the backup command.

    example from BOL:

    BACKUP DATABASE AdventureWorks

    TO DISK='C:\AdventureWorks1a.bak', DISK='C:\AdventureWorks2a.bak',

    DISK='C:\AdventureWorks3a.bak'

    MIRROR TO DISK='C:\AdventureWorks1b.bak', DISK='C:\AdventureWorks2b.bak',

    DISK='C:\AdventureWorks3b.bak'

    WITH FORMAT

    Cheers,

    Got an idea..share it !!

    DBA_Vishal

  • I think this will work

    Cheers,

    Got an idea..share it !!

    DBA_Vishal

  • Too many replies

    "More Green More Oxygen !! Plant a tree today"

  • You should be able to backup locally and then use xcopy or robocopy in a batch script to move the database backup over to the external device.

  • Unless the report server has to be read/write, I would suggest log shipping from the production server to the report server, and continuing to do your backups of production how you do them now.

    Dave

  • .

  • SA (7/9/2009)


    You should be able to backup locally and then use xcopy or robocopy in a batch script to move the database backup over to the external device.

    I don't have an idea about xcopy or robocopy. can you guide me on how i can use it?

  • Why dont use plain xp_cmdshell instead to copy the backups to different location?

    These URL's will tell you how to enable this command(if not already enabled). Use plain DOS command to copy the file using XP_cmdshell.

    http://www.mssqltips.com/tip.asp?tip=1020

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



    Pradeep Singh

  • I don't have an idea about xcopy or robocopy. can you guide me on how i can use it?

    I've used robocopy in the past and its worked for, though from what I've read online xcopy is not much different. You will need to compare the two to see which works in your environment.

    http://www.ss64.com/nt/robocopy.html

Viewing 11 posts - 1 through 10 (of 10 total)

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