January 12, 2009 at 8:59 pm
Comments posted to this topic are about the item Risky Backups
January 12, 2009 at 10:23 pm
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.
January 13, 2009 at 1:15 am
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 :w00t:
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
January 13, 2009 at 1:22 am
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.
January 13, 2009 at 3:37 am
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
January 13, 2009 at 4:05 am
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.
January 13, 2009 at 4:19 am
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
January 13, 2009 at 5:24 am
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
January 13, 2009 at 5:43 am
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.
January 13, 2009 at 5:48 am
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
January 13, 2009 at 5:58 am
Functionally, I don't see any major difference between the two. Seriously. However, since we're dealing with human beings, I agree with you. Never over-write an existing back-up, using a single backup per file, name the backup files meaningfully so that you can find them later and understand what they are (usually this entails using a date as well as the db name)... All very good practices to prevent human beings (or me) from screwing up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2009 at 6:59 am
I never really understood (unless I try to disconnect reality and travel back in time to the days when SQL was created and tape drives were directly attached to the database servers) why this option exists. Using a single file for multiple backups is completely outdated. Really, even backing up to individual files on disk is somewhat outdated given the power of current backup software. Using a hierarchical model in our backup software, we keep multiple days worth of backups (either fulls & logs or just fulls) on disk. Those are migrated to tape for offsite and longer term retention during the day.
If all you have available is standard SQL backups and the integrated tools, then certainly individual files, in a scheme that meets your business requirements will keep you out of your bosses office. A strong redundancy/backup solution above and beyond the free tools, or better still, a replication strategy to an off site location, may keep you from needing the backup for more than something to fill your spare time.
January 13, 2009 at 7:25 am
I always like to have more than one place to restore from. We backup to both the hard drives (SAN based storage) as well as sending backups to Tivoli with TDPSql. We only store one backup per file and they also get moved to Tivoli as part of the OS drive backups.
January 13, 2009 at 7:55 am
I too agree with the one fire per backup. I use the one folder per database with the naming convention of dbname_TYPE_YYYYMMDD.bak
January 13, 2009 at 8:01 am
Maybe I'm just naive here, but if you're doing multiple backups to a single file, wouldn't that file become pretty large after even a few runs? Granted, you do several individual backup files it's the same amount of space required, but I know in my experience working with a single large file becomes more cumbersome than a few smaller ones. Not to mention the whole single point of failure thing.
The distance between genius and insanity is measured only by success.
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply