December 27, 2007 at 3:18 pm
We are planning to implement the following backup plan, please let me know your ideas:
1. Full database backup - weekly once with Truncate
2. Differential db backup - once every day with Truncate
3. Transaction log backups every 30 minutes.
For the full and differential db backup's - Truncate is it Okay??
If so, can you please refer me to the scripts which also includes truncate. Thanks!!
December 27, 2007 at 8:17 pm
Full and diff backups don't use truncate.
The backup scheme depends on what works for your business. If they can tolerate finding a full, then a diff, then a ton of logs if the db fails at 7pm, then that's fine. If not, you might want to add other diff backups in there as well.
Be sure you keep everything you need since the last full.
January 1, 2008 at 12:56 am
Hi,
What is the size of the database?
What is the data loss can the your company afford?
let us know about it, then we can goa head about the suggestion for the backup Strategy.
Regards
Kumar
January 1, 2008 at 1:11 am
Hi
A backup strategy mainly depends on the amount of data loss that you can take and also the maximum time you can spend on restoring the database in case anything happens.
Less frequent the backups more is the data loss and more the number of backups to be restored, more time taken to restore the database.
Need more details to come up with valuable suggestions but i think you may need more diff backups.
"Keep Trying"
February 8, 2008 at 12:54 pm
As the backup file size is big, we are thinking of truncating them, is this okay?
The database size we have is about 60 GB. Thanks.
February 11, 2008 at 2:22 pm
Are you talking about truncating or shrinking?
Truncating is only applicable to log backups.
Shrinking can apply to data and log files but not backup files.
If the backup file is 60GB, then that is how much data is in the database. The only way to reduce that is to clean out data from the database, which may or may not be appropriate. If the size of the backup is an issue, there are 3rd party tools which can compress them on the fly (e.g. RedGate SQL Backup or Quest LiteSpeed)
If your database is still growing, then shrinking is not a good idea - it will just grow again anyway.
If the log file got blown out for some reason, preferably one that is not going to reoccur, you could shrink it. Lookup DBCC SHRINKFILE in BOL.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 12, 2008 at 7:08 am
We have a similar plan to what you propose, as our standard for about 60 servers, varying in size from 1/2 GB to 128 GB.
Sun 7:00 pm - Full backup WITH INIT
Mon-Sat 7 pm - Diff backup (append) to same backup device file
Midnite - 6:59, and 8:00 pm - 11:59, Sun - Sat : tran log backup every 15 mins. These are to a different backup device file (i.e, "DBname_Log.bak"), which gets an INIT every eve with either the Diff or the Full.
We can restore back to point-in-time TODAY, or 7 pm THIS WEEK. This has happened in practice a few times in the last two years.
If we need to go previous to Sun. Eve, we will pull needed files from a disk image on tape.
I think the INIT is what you're referring to by Truncate - it clears the old backup file on Sunday night, and the old Log backup file every evening.
For two of the larger applications we use LiteSpeed backup to save drive space and time, but otherwise the same strategy.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
February 12, 2008 at 7:21 am
I should have said "7 pm ANY DAY this week".
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
February 14, 2008 at 10:04 am
Mike Hinds (2/12/2008)
We have a similar plan to what you propose, as our standard for about 60 servers, varying in size from 1/2 GB to 128 GB.Sun 7:00 pm - Full backup WITH INIT
Mon-Sat 7 pm - Diff backup (append) to same backup device file
Midnite - 6:59, and 8:00 pm - 11:59, Sun - Sat : tran log backup every 15 mins. These are to a different backup device file (i.e, "DBname_Log.bak"), which gets an INIT every eve with either the Diff or the Full.
We can restore back to point-in-time TODAY, or 7 pm THIS WEEK. This has happened in practice a few times in the last two years.
If we need to go previous to Sun. Eve, we will pull needed files from a disk image on tape.
I think the INIT is what you're referring to by Truncate - it clears the old backup file on Sunday night, and the old Log backup file every evening.
For two of the larger applications we use LiteSpeed backup to save drive space and time, but otherwise the same strategy.
Mike: Thanks for your response,
With the below:
"These are to a different backup device file (i.e, "DBname_Log.bak"), which gets an INIT every eve with either the Diff or the Full"
Did you mean that for the full/diff db backup files you are opting to have 1 days worth of files?
Actually with truncate I meant truncating the db bak files if possible, as the bak files are big I was thinking of truncating the size if possible, not sure if this is possible. Please advice. Thanks!
February 14, 2008 at 11:05 am
Mh, I have two separate backup files, "DbName.Bak" and "DbName_Log.bak". Each Sunday evening, when I run the Full backup, I add the WITH INIT command, which completely empties the file. You are correct that this can be thought of as truncating, but usually that word is used for the logfile, which is truncated whenever you do a Full or Differential backup of the database itself (not the log).
Mon - Sat I do a differential (which truncates the log), but this one does not INIT or empty the "DbName.Bak" file - instead the Differential backups append, so the "DbName.Bak" file grows each night until Sunday, when it is emptied with another INIT. I can look at the contents of the backup device and see a backup for each day starting at Sunday evening, through last night.
The log backups go into "DbName_Log.Bak" every 15 minutes between midnight and 7 pm. The job that runs the Full or Diff at 7 pm also runs an INIT on the "DbName.Bak" file, so it starts out empty at midnight, all 7 days. This means that during the day I can restore to any point-in-time, but if I go to a previous day I can only choose the 7 pm marks, which seems like a resonable compromise.
I hope this helps. Let me know if you'd like some example code to set up your jobs.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
February 14, 2008 at 11:50 am
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 Lead Database Administrator1st Source BankMCP, MCTS
February 14, 2008 at 12:34 pm
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
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
April 28, 2008 at 8:38 am
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 Lead Database Administrator1st Source BankMCP, MCTS
April 28, 2008 at 8:43 am
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.
April 28, 2008 at 11:30 am
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 Lead Database Administrator1st Source BankMCP, MCTS
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply