none
Nightly backup to another (development) server

    Question

  • My boss wants to have the main SQL server do a nightly backup to another server that will serve as a backup repository and also a dedicated development server.  How do we go about this? 
    The Ox is slow, but the Earth is patient.
    Monday, October 31, 2011 3:36 AM

Answers

  • Another method would be

    BACKUP DATABASE dbname TO DISK = 

        N'\\DEV_SERVER\sharedfolder\dbname.BAK' 

    BTW, do you need then to perform restore?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Peja Tao Wednesday, November 02, 2011 2:42 AM
    • Marked as answer by Malleus55 Thursday, November 03, 2011 1:46 AM
    Monday, October 31, 2011 7:27 AM

All replies

  • I would do the following.

    1. Create a Maintenance Plan to Backup the Database to a local drive.
    2. Create a Scheduled Task or SQL Server Agent job to copy the backup files using ROBOCOPY to the development server. 

    Even though it is possible to take the database backup directly to the development server using Maintenance Plans, I don't recommend it since the possibility of having a corrupt backup file is more in this method.




    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @PradeepAdiga

    • Proposed as answer by Peja Tao Wednesday, November 02, 2011 2:41 AM
    Monday, October 31, 2011 4:53 AM
  • Another method would be

    BACKUP DATABASE dbname TO DISK = 

        N'\\DEV_SERVER\sharedfolder\dbname.BAK' 

    BTW, do you need then to perform restore?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Peja Tao Wednesday, November 02, 2011 2:42 AM
    • Marked as answer by Malleus55 Thursday, November 03, 2011 1:46 AM
    Monday, October 31, 2011 7:27 AM
  • Another method would be

    BACKUP DATABASE dbname TO DISK = 

        N'\\DEV_SERVER\sharedfolder\dbname.BAK' 

    BTW, do you need then to perform restore?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 31, 2011 7:27 AM
  • You have got two good answers on how, now let me just add a warning. If your development server is going to be the single reference point for a period of time for a certain number of backups (however brief this period will be) then you are obviously running a risk under this strategy.

    Development servers have a tendandacy to be treated as less important than production (rightly or wrongly) by almost everyone who uses them so remember that any backups housed on this server could be lost due to user error. Is this acceptable to your recovery point objective and recovery time objectives?

    Another alternative you could do is to set up a separate secondary backup to this server using copy only backups which will not effect your primary backup chain http://msdn.microsoft.com/en-us/library/ms191495.aspx

     


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Proposed as answer by Peja Tao Wednesday, November 02, 2011 2:42 AM
    Monday, October 31, 2011 9:07 AM
  • We would only do a restore in case of a catastrophic failure on the main DB. 
    The Ox is slow, but the Earth is patient.
    Tuesday, November 01, 2011 8:39 PM
  • I'm pretty sure that that's what he wants.  He wanted a fresh copy of the database to develop against and also have a backup for the main production DB. 
    The Ox is slow, but the Earth is patient.
    Tuesday, November 01, 2011 8:42 PM