Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database backups best practice


Database backups best practice

Author
Message
Mike Hinds
Mike Hinds
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 1074
OOPS!

I had said, 'The job that runs the Full or Diff at 7 pm also runs an INIT on the "DbName.Bak" file' -- WRONG!

The Diff job does INIT on the "DbName_Log.Bak" file, not the "DbName.Bak" file. Otherwise I would lose the Full backup, and my Diffs would be worthless.

Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6972 Visits: 8839
Today I've received this link and found it to be very to the point !

Windows ITPro
Best Practices for Backup and Restore in SQL Server 2005
http://www.windowsitpro.com/whitepapers/Index.cfm?fuseaction=ShowWP&wpid=415a010b-e742-46be-808a-f037e8d6630b

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Mike Hinds
Mike Hinds
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 1074
Reply to nkm123 - Sorry, I was not able to reach you via the email you used.

- - - - - - -

>>> "Mike Hinds" 2008-04-24 10:29:16 AM >>>

You are doing well. You are on the right track with INIT.

Add another job step to the Sunday Full and the Mon-Fri Diff. The code is easy.

-- Log Init Step
backup log [test] to [test_Log] with init

This step must run immediately after the Full or Diff step is completed.
It empties your log backup device file every day.

I use backup devices to make coding easier. In SQL 2000 they are in
Management | Backup, and in SQL 2005 they are in Server Objects | Backup Devices. I name the data device the same as the database, 'Test.Bak' and the log backup device would be 'Test_Log.bak'. If you don't do this you can use the whole path:

-- Log Init Step
backup log [test] to disk = 'C:\Log\test_Log.bak' with init

and this should work just as well.

Let me know how this works for you.

Thanks,
- Mike

----------
Mike Hinds, Database Administrator
1st Source Bank - IT Technical Services
PO Box 1602
South Bend, IN 46634-1602

>>>
Hi Mike,

Sorry for trouble you but when I was going through one of post for
searching my issue I found solution written by you but thing is that I am
new and still don't know how to write this code so please help me to write
this code :---

Senario:-
1) Full BackUp :- Every sunday (7PM)
2) Diff BackUp :- Mon- Sat (7PM)
3) Trans Log :- 15 min (daily)

Two folder 1)Data Folder 2) Log Folder

I append diff backup to Full in Data Folder
Log files are in Log Folder

This is same as your case.. But I just want to remove or delete all logs
when full or diff backup happen... I have seen your answer to use INIT but
I don't know how to use this .. is any wizard there which will delete or I
have to use some code in some job...

Please help me to let me know what code need to be used...

Dtatabse Name :- Test
Folder :- DATA and LOG

Thanks!

Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36042 Visits: 18736
I would never append backups to a file. As the size grows, you increase the chance of bad blocks or corruption and you could invalidate mutliple backups this way.

I also never use devices.

Instead I would constantly create a new file with the date/time in the name, for every single backup.

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
Mike Hinds
Mike Hinds
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 1074
Steve Jones - Editor (4/28/2008)
I would never append backups to a file. As the size grows, you increase the chance of bad blocks or corruption and you could invalidate mutliple backups this way.

I also never use devices.

Instead I would constantly create a new file with the date/time in the name, for every single backup.


This surprises me. I have about 60 servers using this strategy, and have never seen the corruption you warn of, or heard of it happening to another. Surely the prominent placement in 2K at "Management | Backup", and in 2K5 under "Server Objects | Backup devices", and the convenient "Media Contents" indicates they were intended to be used?

Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6972 Visits: 8839
I always use backup devices and withing a cycle (between full backups) have my log-bacups incremental.
Only very rare I've noticed backups failing because of the device.
We regulary test pit-restores and didn't encounter issues.
I must admit most of or log-backups don't grow over 4Gb per cycle.

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Mike Hinds
Mike Hinds
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 1074
ALZDBA (4/29/2008)
I always use backup devices and within a cycle (between full backups) have my log-backups incremental.
Only very rare I've noticed backups failing because of the device.
We regulary test pit-restores and didn't encounter issues.
I must admit most of or log-backups don't grow over 4Gb per cycle.


I use two backup devices per database:
"DBName.bak" holds the Sunday full and up to six days of differentials. It is cleared Sunday night with INIT, so it is never > 6 days old.
"DBName_Log.bak" holds the incremental log backups since the last full or diff. It is cleared daily with INIT (following the successful full or diff), so it is never > 24 hours old.

Out of 60 servers, maybe two have the log backup device grow over 5 GB on a busy day.

The big issue would be if either the full or diff saw corruption. I could probably reduce that risk by adding FORMAT to the INIT, but never felt it necessary before. My IT guys can retrieve up to two weeks of previous files if they are needed (has happened once or twice when I needed to get us back before Sunday).

Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36042 Visits: 18736
Mike,

I've never had issues restoring from a good file, but I have had files get corrupted and be unable to back up to them. granted it's typically when I've walked into a situation in the v6.5 and v7 days where someone had 20 or 30 days of backups in one file.

The backup devices are left over from historical code back in the Sybase days. I've never seen the need and never used them, using a new file every day for every backup.

That's what I'd recommend, but I'm conservative. I think that the more times to write to the file, the more chance you have of losing multiple backups. But that's me. If it's worked for you, great. I wouldn't say you were being negligent in any way with your system. If I started working with you, I'd go with your system, perhaps argue against it, but I'd keep it going if that's what the standard was.

Different experiences and preferences.

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
Mike Hinds
Mike Hinds
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 1074
Steve -

Thanks, and I appreciate your insights and experience. I was surprised at your original comment, and felt that perhaps I was endangering the bank's applications. Good to find out that I'm not betting my job on a known no-no.

And no, I never keep more than 7 days of stuff in any one file. I consider myself conservative as well, even paranoid. I act as though my fellow IT workers (especially "superusers") are my biggest danger.

Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36042 Visits: 18736
Those fellow worker are even more of a reason to separate stuff. Those guys/gals are dangerous!

I'd still argue with you, but you're not making a mistake. I think I'm just more conservative.

My large scale scheme has been:
- backup full once or twice a week to local disk
- back up diff every night to local disk (could be more than once a day depending on requirements)
- back up log to disk every 5-15 minutes, depending.
- After backup (any) completes, immediately copy to remote disk.
- keep 1 full, 1-2 diffs, all logs since earliest diff on local disk.
- Keep same on remote disk
- anything older gets copied to tape, then deleted.
- monitor all jobs, if one fails, alert, try to restart, DBA manually runs if needed, including copy/delete.
- test at least one restore once a week, get something off tape at least once a quarter and verify things.

Might be more cautious with more testing at a bank/hospital.

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