SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Risky Backups


Risky Backups

Author
Message
Maurice Pelchat
Maurice Pelchat
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 51
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.
Manie Verster
Manie Verster
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1649 Visits: 1022
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.

:-PManie 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)
Chuck Lathrope
Chuck Lathrope
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 354
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
Wayne West
Wayne West
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 3702
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. Hehe

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! BigGrin

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
David Reed-223505
David Reed-223505
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 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! BigGrin


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

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?
Wayne West
Wayne West
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 3702
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! BigGrin


Um, Wayne. You're standing in the neighbors' yard. It's time to come back inside and take your meds. Tongue
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.... Hehe

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64645 Visits: 19118
Review? Source Code? It's this point and click?!?!? Tongue

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
My Blog: www.voiceofthedba.com
Manie Verster
Manie Verster
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1649 Visits: 1022
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?!?!? Tongue

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.Alien

:-PManie 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)
Wayne West
Wayne West
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 3702
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.Alien

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.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
harvey.wigfall
harvey.wigfall
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search