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 «««23456»»

Risky Backups Expand / Collapse
Author
Message
Posted Tuesday, January 13, 2009 2:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 3:47 PM
Points: 12, Visits: 48
I use the following strategy : At full backup time, two new file name are generated (including a timestamp in the name): one for full backups, one for log backups. Full backup is done to this new file, and log backup initialize log backup file with a first log backup. All other logs backups are appended to this file all the day.

To apply this discipline automatically, I put on SourceForge a free solution (YourSqlDba) for maintenance and backups. to do maintenance, optimizations and backups. It is all T-SQL Script based, and articulate around Sql Agent and database mail mechanisms. This solution has its own database and T-SQL code, with a detailed logging system for maintenance instructions and an email system that let you know when things go well as well than when things goes wrong.

By mailing if something goes wrong (ex: when a backup file can't be written), DBA knows that backups needs to be taken now again.

It is important not to mix complete and log backup into the same backup file, because of the risk of overwriting backup content.

This system allow to have many log backups to help recovery from a previous complete backup. By default only the complete backups of the day are kept, and week log backups. These files all bears different name, so it avoids accidental overwrite when copied. Full backup must be copied each day to another media to avoid filling local backup space. This is quite easy to do this by adding a second step to the sql agent's job that does the backup, especially for that sole purpose.
Post #635844
Posted Wednesday, January 14, 2009 6:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 1,205, Visits: 923
Wayne West (1/13/2009)
I both like and don't like the concept of the backup file containing the date of backup, right now I don't code the date into the file name on our systems, and I don't use maintenance plans for backups. It's nice in that you can look at the file and see the database name and backup date, but the file has a timestamp that also shows the date, so the information almost seems redundant since the file timestamp is not altered when the file is restored. Plus, the thought of digging in to the system tables (though I love working with system tables!) to find file names to delete just really bugs me for some reason. I prefer to have my system just overwrite the backup and not worry about it. On occasion a backup will fail and something will have squirreled-up the backup file, in which case I delete it and do a backup with overwrite and that seems to take care of it.


Hi Wayne,

I am clearly not as salted with SQL Server as what you are but I use maintenance plans to do my backups and it uses the dbname_yyyymmdd_sequencenumber as file name and I also have a maintenance plan to cleanup old backups so I have little to do in the way of backups. I come in every morning and do some checks on the database to make sure backups is ok etc. Now I suppose you will laugh at me but I have one user database that is about 2 GB in size. I dunno what I'll do with a 700 gb database.

I have never before heard even of multiple backups in one file so al my backups, user db, test db, system db's and other goes into different folders.


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #636203
Posted Wednesday, January 14, 2009 11:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 25, Visits: 319
I also created a huge proc and database to track backups. My backup proc is 1500 lines of code and can do litespeed or native backups and can do just about anything including simple/debug logging. I backup about 1.5 tb of data a day and a few of the databases have log backups every 5-15 minutes. I have a file per diff or full backup and one daily log file I append to for log backups...just for convenience to not have tons of files to deal with if I ever have the need.

I verify most of the databases, but often I don't have the time and want to offload the work to another server to verify at some point. I restore our most important database after the backup is done as a way of verification and to use the database for ad-hoc querying by our team. Everything is backed up to another server (SAN) and restored to on yet another server. A little speed is lost, but I backup to more than one file at a time (a proc parameter) to speed things up a bit.

I will try to post my backup script on my site soon for anyone that is interested.

Chuck Lathrope
www.sqlwebpedia.com
Post #636839
Posted Thursday, January 15, 2009 8:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Manie Verster (1/14/2009)
... I am clearly not as salted with SQL Server as what you are but I use maintenance plans to do my backups and it uses the dbname_yyyymmdd_sequencenumber as file name and I also have a maintenance plan to cleanup old backups so I have little to do in the way of backups. I come in every morning and do some checks on the database to make sure backups is ok etc. Now I suppose you will laugh at me but I have one user database that is about 2 GB in size. I dunno what I'll do with a 700 gb database. ...

I ain't that salty! Haven't even hit 50 yet, though it's disturbingly close.

I don't mind maintenance plans for backups, they do those fairly well. I do not like them for DBCCs, and it seems that most of the maintenance plans that I see people want them to do everything. That's fine, as far as it goes, but I want more details. I do full DBCCs every work night on everything (sp_msforeachdb, except for one server) via OSQL and send the results to a text file. I have an aging system to keep the last ten runs. I then pipe the results through Find looking for "errors" and that file is grabbed by a nightly Perl script so that I see a couple of dozen lines of DBCC results rather than thousands of lines. If I see non-zero numbers in that result set, I can probe deeper into the full file that's on the server.

This does two things for me. First, detailed information. As far as I've seen in 2000, maintenance plans don't give detailed results in their logs. Second, I wouldn't have confidence that a maintenance plan would pick up new databases whereas I know sp_msforeachdb will catch everything. But for backups, I guess they'd be OK.

Old habits die hard. I spent my formative years as a DBA in a 4.21/6.5 environment where you did everything by hand. It warn't like them fancy gewgaws and wizards them young kids got now! GET OFF MY LAWN! :D
Post #637255
Posted Thursday, January 15, 2009 9:14 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 24, 2013 11:26 AM
Points: 208, Visits: 380
Wayne West (1/15/2009)
Old habits die hard. I spent my formative years as a DBA in a 4.21/6.5 environment where you did everything by hand. It warn't like them fancy gewgaws and wizards them young kids got now! GET OFF MY LAWN! :D


Um, Wayne. You're standing in the neighbors' yard. It's time to come back inside and take your meds. :P

Seriously, though, I'm with Mr. West. The only thing that I might use the wizards for would be to generate a script, or create objects in a dev environment that I script out. All T-SQL to be run in production is subject to review from source control (several times by me and several more times by others) BEFORE it's executed anyway... right?
Post #637282
Posted Thursday, January 15, 2009 9:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
David Reed (1/15/2009)
Wayne West (1/15/2009)
Old habits die hard. I spent my formative years as a DBA in a 4.21/6.5 environment where you did everything by hand. It warn't like them fancy gewgaws and wizards them young kids got now! GET OFF MY LAWN! :D


Um, Wayne. You're standing in the neighbors' yard. It's time to come back inside and take your meds. :P
Seriously, though, I'm with Mr. West. The only thing that I might use the wizards for would be to generate a script, or create objects in a dev environment that I script out. All T-SQL to be run in production is subject to review from source control (several times by me and several more times by others) BEFORE it's executed anyway... right?

But I live in an apartment complex, the yard is community property! Maybe I took my wife's meds this AM by mistake....
Post #637286
Posted Thursday, January 15, 2009 9:32 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 4:02 PM
Points: 33,062, Visits: 15,176
Review? Source Code? It's this point and click?!?!? :P

The only place I used maintenance plans is when I don't have time to set something up or someone has installed their own instance and needs something setup. Easier to get them to do a maintenance plan at first and handle things rather than nothing.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #637303
Posted Friday, January 16, 2009 10:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 1,205, Visits: 923
don't mind maintenance plans for backups, they do those fairly well. I do not like them for DBCCs, and it seems that most of the maintenance plans that I see people want them to do everything. That's fine, as far as it goes, but I want more details. I do full DBCCs every work night on everything (sp_msforeachdb, except for one server) via OSQL and send the results to a text file. I have an aging system to keep the last ten runs. I then pipe the results through Find looking for "errors" and that file is grabbed by a nightly Perl script so that I see a couple of dozen lines of DBCC results rather than thousands of lines. If I see non-zero numbers in that result set, I can probe deeper into the full file that's on the server.


Steve Jones - Editor (1/15/2009)
Review? Source Code? It's this point and click?!?!? :P

The only place I used maintenance plans is when I don't have time to set something up or someone has installed their own instance and needs something setup. Easier to get them to do a maintenance plan at first and handle things rather than nothing.



Wayne,

Just to help you right. By salted (remember I am an Afrikaans (Dutch) speaking South African) I actually meant knowledgable or trained. Salted is a word we Afrikaners use to say that and not old or aged. Would you guys mind to post some of these scripts that you use and how do you schedule them to run on their own. I don't see you sitting their every night running all these scripts. Please help a wannabe DBA out will you or refer me to an article on SSC where I can learn that.
I'll appreciate that very much.


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #638652
Posted Tuesday, March 10, 2009 2:39 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Manie Verster (1/16/2009)
... Just to help you right. By salted (remember I am an Afrikaans (Dutch) speaking South African) I actually meant knowledgable or trained. Salted is a word we Afrikaners use to say that and not old or aged. Would you guys mind to post some of these scripts that you use and how do you schedule them to run on their own. I don't see you sitting their every night running all these scripts. Please help a wannabe DBA out will you or refer me to an article on SSC where I can learn that.
I'll appreciate that very much.

Manie, sorry for the lack of a reply. I worked three days in the month of February: out for carpal tunnel surgery, then came back and caught pneumonia! Just now getting back to working full-time and I'm supposed to be on light duty.

I would be happy to show you some stuff, but I'm not certain how soon I can get to it. I just wanted to let you know that you're not forgotten.
Post #672798
Posted Wednesday, October 30, 2013 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 8:45 AM
Points: 4, Visits: 18
Risky Backup:

I believe that backups written to the same file does create a single-point-of-failure; with a little work, backup scripting can create a date\time backup file to differentiate each backup file and is a lot easier to manage and restore. I have done appended file writes for reporting and error tracking in the past but for backups, such as database backups, I do recommend a separate backup file unless the loss of the backup file is a acceptable risk.

Harvey Wigfall
MCDBA
Post #1509818
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse