Post reply

Network backups

  • October 12, 2010 at 5:02 pm

    #227924

    I'm wondering is anyone backing up their databases to a central network location by specifying a UNC path in the Backup window of their Maintenance Plans?

    In the past, I've always just backed up locally on the server itself and then the tape software takes it from there, but now people are wanting me to backup all the databases to a central network location.

    Apart from the obvious being increased network traffic, is there anything else to be concerned about in doing this, or anything I really need to watch for?

  • October 12, 2010 at 7:26 pm

    #1235952

    In addition to traffic, network reliability varies in different organizations.

    We back up most of our SQL databases on various servers to one server, then those centralised backups go to tape. Very few issues to speak of so far.

  • October 12, 2010 at 7:33 pm

    #1235955

    Thanks for your response, good to hear you're having no problems as that's the exact setup I plan on doing. Are you doing the same thing for Transactional, Differential and Full backups?

    Are you doing anything fancy or just doing it within the backup window of a maintenance plan as per the attached?

  • October 12, 2010 at 8:14 pm

    #1235957

    Backing up across the network is much more open to failure. If there are any issues with the network, your backups will fail. Additionally, there are configuration settings that need to be considered - based upon your network settings and the central management server you are backing up to.

    With that said, if you have a very reliable network between the server being backed up and the network location it can work quite well. We did this at my former site with minimal issues.

    What I would recommend is that you consider setting up and using a backup network. This would be a totally separate network from the public network - using a separate NIC and setup redirection so the backup network is used when you backup to the network location.

    Not only will that help insure that there are no issues, but it will reduce the impact of the backup on the public network and will have less of an impact on your users while the backup is running.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • October 12, 2010 at 8:17 pm

    #1235958

    Thanks very much for taking the time to write that response Jeffrey, really helpful

  • October 12, 2010 at 8:56 pm

    #1235963

    We used to perform our database back ups directly to a network location.

    Until the SAN drive that was housing the backup directory had a hardware failure (very old hardware), and it took 3 days to get the replacement in. That was three days in which database backups were not being performed.

    Now, all backups are performed to a shared directory on a local drive (i.e. D:\Backups). On the file server, a schedule job runs to copy the backups from the shared directory to computer specific directories, utilizing RoboCopy with the /MIR option so that the network location is a mirror of the local computer. In that way, as files are deleted out of the local directory as part of the cleanup process, they will also be removed from the network location.

    If the SAN goes down again, backups are still being performed to the local drive. When the SAN drive comes up again, the scheduled task will catch up all of the files on the SAN drive.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 12, 2010 at 9:01 pm

    #1235964

    Quite nice Wayne, and I was thinking along a similiar line earlier actually.

    What came to my mind though is that you need twice the amount of space to actually do that though don't you, which I bet they'll frown upon.

  • October 14, 2010 at 11:05 am

    #1237069

    We have moved from backing up to local drives to using a UNC path to central storage. We haven't had any significant issues so far.

    Some of the replies to your question mention the possibility of network or SAN failure. If you have SAN and/or network instability, you probably have other issues with a higher profile than a simple backup job failing.

    As much as is feasible, reduce or eliminate single points of failure.

    Depending on the size of your company, a single outage of five minutes can justify spending thousands of dollars on more reliable hardware, software, and other high availability strategies. It comes down to the cost of an outage versus the cost of preventing them.

  • October 14, 2010 at 3:32 pm

    #1237241

    Thanks Nelson - good to hear that.

Viewing 9 posts - 1 through 8 (of 8 total)

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