Risky Backups

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Risky Backups

49 posts, Page 1 of 5

1 2 3 4 5 »»»

Risky Backups

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Monday, January 12, 2009 8:59 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 9:24 AM
Points: 31,433, Visits: 13,748

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

Frank Rosser

Posted Monday, January 12, 2009 10:23 PM

SSC 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

John McC

Posted Tuesday, January 13, 2009 1:15 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:30 AM
Points: 562, Visits: 1,404

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

Post #635195

SQLPhil

Posted Tuesday, January 13, 2009 1:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:48 AM
Points: 1,413, Visits: 660

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

Code Monkey

Posted Tuesday, January 13, 2009 3:37 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, January 05, 2010 5:53 AM
Points: 383, Visits: 47

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

edwardelliott

Posted Tuesday, January 13, 2009 4:05 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 8:19 AM
Points: 91, Visits: 350

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

SuperDBA-207096

Posted Tuesday, January 13, 2009 4:19 AM

UDP 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

Jason Miller-476791

Posted Tuesday, January 13, 2009 5:24 AM

SSC-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

Ben Moorhouse

Posted Tuesday, January 13, 2009 5:43 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 09, 2012 2:36 AM
Points: 171, Visits: 442

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

Jason Miller-476791

Posted Tuesday, January 13, 2009 5:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 18, 2010 5:25 AM
Points: 162, Visits: 694

Ben Moorhouse (1/13/2009)

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

Honor Super Omnia-
Jason Miller

Post #635300

« Prev Topic | Next Topic »

49 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions