Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Risky Backups Expand / Collapse
Author
Message
Posted Monday, January 12, 2009 8:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
Comments posted to this topic are about the item Risky Backups






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #635139
Posted Monday, January 12, 2009 10:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 16, 2012 7:28 PM
Points: 34, Visits: 137
Our rule of thumb is:

1. Never overwrite an existing backup file.

2. Always have at least 2 separate, usable backup files on availiable media.

The first was to handle the situation where an ad-hoc backup was run outside of the usual backup cycle, plus it makes it easy to quickly check if a backup file exists. The second was insurance against media failure.

The solution was to timestamp all backup files to give them a unique name. It does make the backup/recovery scripts more complex to write, but the extra effort is worth the peace of mind.



Post #635153
Posted Tuesday, January 13, 2009 1:15 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 566, Visits: 1,582
I usually have about two weeks worth of backups on the server, each database in its own folder and separate files, which are baked up nightly to a remote location. I think it's a case of belt and braces (and maybe a second set of each) just to be safe.

Once bitten once fired


-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible.
Post #635195
Posted Tuesday, January 13, 2009 1:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:57 AM
Points: 1,430, Visits: 706
I've only ever backed up to a single file when taking short-term baselines of environments.

But as a general rule it's back up each of my databases to their own individual file.
Post #635199
Posted Tuesday, January 13, 2009 3:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 08, 2013 2:59 PM
Points: 383, Visits: 57
We have a separate folder for each database, and a separate file for each backup. Backup files are stored on a separate hard disk and on tape. Having separate files for each backup, full, differential & transaction logs, allows us to quickly copy backups from one machine to another as we only need to copy the latest backup file.

Keith
Post #635254
Posted Tuesday, January 13, 2009 4:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:32 PM
Points: 91, Visits: 351
The only time I put more than one backup in a file is when I am setting up mirroring and I put in the full then transaction log backup to save time.

For actual backup purposes I prefer to give each backup it's own file with the date and timestamp - this way if you need to restore you know exactly when the backup was taken.

Post #635260
Posted Tuesday, January 13, 2009 4:19 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711
Steve,
As a general rule, I backup to a different file every time - the only exception would be if I'm working in Dev or test to do an ad-hoc backup that I'm going to use once.

It's a good idea to keep at least a couple of backups on the machine in case something goes wrong for a quick restore... in some cases though there's just not enough disk!

Mark
Post #635265
Posted Tuesday, January 13, 2009 5:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 18, 2010 5:25 AM
Points: 162, Visits: 694
I agree with your state concerning multiple backups in a single file. Even with all of the redundancies built into our infrastructure.

For our investment accounting system I created a procedure to dump the dbs (full) twice a day. Once before nightly processing, and once after. Then between the two, during the day, dump the transaction logs every 10 minutes.

The procedure tags all filenames with a time stamp. TSM copies all of the files to our DR site. I'm told intra-daily, not sure of the exact time frame. But we keep a rolling 3 days worth of files here, two weeks on disk at DR, and indefinately on tape at DR and also at Iron Mountain.




Honor Super Omnia-
Jason Miller
Post #635286
Posted Tuesday, January 13, 2009 5:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:11 AM
Points: 171, Visits: 444
I do backups for various solutions - not just SQL server, and with every one I backup to individual files.
I just keep the filename format the same eg. ddmmyyyyDatabaseBackup.mdb = 13012009DatabaseBackup.mdb

This removes the single point of failure, and also makes it easy to delete the older files.
Because I use the same syntax, restores are easy because they can be scripted.
Post #635295
Posted Tuesday, January 13, 2009 5:48 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 18, 2010 5:25 AM
Points: 162, Visits: 694
Ben Moorhouse (1/13/2009)
I do backups for various solutions - not just SQL server, and with every one I backup to individual files.
I just keep the filename format the same eg. ddmmyyyyDatabaseBackup.mdb = 13012009DatabaseBackup.mdb

This removes the single point of failure, and also makes it easy to delete the older files.
Because I use the same syntax, restores are easy because they can be scripted.



I prefer the dates YYYYMMDD easier to sort, at least for me.




Honor Super Omnia-
Jason Miller
Post #635300
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse