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

Database backups best practice Expand / Collapse
Author
Message
Posted Thursday, February 14, 2008 11:50 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 12, 2014 6:31 AM
Points: 617, Visits: 866
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.


Regards, Mike
Post #455904
Posted Thursday, February 14, 2008 12:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 7,010, Visits: 8,462
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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #455939
Posted Monday, April 28, 2008 8:38 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 12, 2014 6:31 AM
Points: 617, Visits: 866
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!


Regards, Mike
Post #491469
Posted Monday, April 28, 2008 8:43 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 9:36 AM
Points: 33,275, Visits: 15,443
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
Post #491478
Posted Monday, April 28, 2008 11:30 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 12, 2014 6:31 AM
Points: 617, Visits: 866
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?


Regards, Mike
Post #491625
Posted Tuesday, April 29, 2008 12:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 7,010, Visits: 8,462
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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #491841
Posted Tuesday, April 29, 2008 5:53 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 12, 2014 6:31 AM
Points: 617, Visits: 866
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).


Regards, Mike
Post #492003
Posted Tuesday, April 29, 2008 8:27 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 9:36 AM
Points: 33,275, Visits: 15,443
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
Post #492161
Posted Tuesday, April 29, 2008 8:36 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 12, 2014 6:31 AM
Points: 617, Visits: 866
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.


Regards, Mike
Post #492172
Posted Tuesday, April 29, 2008 9:08 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 9:36 AM
Points: 33,275, Visits: 15,443
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
Post #492228
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse