Backup plan confusion!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Backup plan confusion!

Backup plan confusion!

Rate Topic

Display Mode

Topic Options

Author

Message

Abu Dina

Posted Sunday, February 10, 2013 9:02 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:16 PM
Points: 494, Visits: 2,151

Please consider the following setup:

5 SQL Server instances writing backup files to the same NAS device 7.5TB capacity

Backup Schedule (Fortnightly FULLs + Daily DIFFs

Databases are all set to SIMPLE recovery (no point in time restores required). Largest DB 500GB and largest instance is 1.2TB data.

I'm unable to perform the FULL backups on the same day for all 5 sql instances (NAS problems) so I've got it setup to do Server A on Monday, B on Tuesday and so on.

Now I need to implement off-site backups and I'm not sure how to work it out.

IT manager has suggested that for off-site backups we perform FULLs once a week (the night before the collection) to USB drives attached to each server. But now I'm worried that the suggested weekly FULLs will break my Fiortnightly FULLS+Daily DIFFs chain? Hope I'm making sense?

-----------------------------------
http://www.SQL4n00bs.com

Post #1418111

SQL_Bob

Posted Sunday, February 10, 2013 9:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 18, 2013 8:01 AM
Points: 30, Visits: 288

Probably the easiest option (if possible) is for the offsite backups to be copied off the NAS you're already using, so you don't have to create extra backups.

If that's not possible though, use the WITH COPY_ONLY option in your backup command for the weekly full backups - this way it won't break the differential chain from the system you already have set up.

_______________________________________________________________________________________________
Bob Pusateri
MCITP, SQL Server 2008 Administration & Development

Blog: The Outer Join
Twitter: @SQLBob

Post #1418114

Abu Dina

Posted Sunday, February 10, 2013 9:37 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:16 PM
Points: 494, Visits: 2,151

Thanks :) I didn't know about WITH COPY_ONLY

-----------------------------------
http://www.SQL4n00bs.com

Post #1418115

SQLmountain

Posted Sunday, February 10, 2013 10:03 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, April 29, 2013 6:28 AM
Points: 436, Visits: 712

If you forgo the "with copy_only" option and decide to have the offsite backups be copied from your already existing backups you could use SSIS, robocopy, etc.

SSIS packages are good for copying, naming, archiving, your offsite backup files, while providing scheduling and alerting in SQL Server Agent. Good for overall management of your offsite backups.

Post #1418119

Abu Dina

Posted Monday, February 11, 2013 2:31 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:16 PM
Points: 494, Visits: 2,151

Because I can only perform FULL backups to the NAS from one sql server per day and the offsite backup collection is on Thursday morning, I really need a fresh backup of the databases going to the USB drive.

Copying an existing FULL backup would be okay but it could be well over a week old.

I will try the with COPY_ONLY and see how it goes.

Cheers for the input.

-----------------------------------
http://www.SQL4n00bs.com

Post #1418262

« Prev Topic | Next Topic »

Permissions