April 29, 2008 at 12:00 am
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
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 29, 2008 at 5:53 am
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 Lead Database Administrator1st Source BankMCP, MCTS
April 29, 2008 at 8:27 am
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.
April 29, 2008 at 8:36 am
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 Lead Database Administrator1st Source BankMCP, MCTS
April 29, 2008 at 9:08 am
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.
April 30, 2008 at 12:56 am
mike please can you explain with examples to make it more clear
April 30, 2008 at 1:49 am
Mike Hinds (4/29/2008)
I act as though my fellow IT workers (especially "superusers") are my biggest danger.
And they are !
That the top reason to remove builtin\administrators from your sql instances !
- backup full at least once or twice a week to local disk.
(most of the smaller servers have a daily full)
(fullbackup job step is preceeded by inc. logbackup )
- 30 minutes after fullbackup job, a logbackup job runs with init of all log bu devices.
- differential backups: only if the logbackup volume is high and restore takes more time than the retsore scenario using the diff-backups.
Mainly because of the IO overhead diff backups bring to the system.
- back up log to disk every 5-15 minutes, depending.
- with every backup job always include fullbackup of master and msdb.
- After backup (any) completes, immediately copy to remote disk.(wich is then copied to tape by veritas filebackup with a retention period (at least 2 weeks).)
- keep 1 full, all logs since last full backup on local disk.
- 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. (mainly to produce sox evidence)
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 30, 2008 at 2:53 am
great topic with useful things here ... thnx friends for everything that you reply here!
:hehe::hehe::hehe::hehe:
April 30, 2008 at 4:14 am
Thanks mike and all other guys who has give grt explaination ...
Only think what is left here How to copy my backup to another disk.. I tried xp.cmdshell but it's not working for me... could someone please let me know syntax for this....
One more thing
Will Full or Diff backup truncate log file or not .. or if i put in this waty that will log shipping work if I use Full or diff backup regular way .. won't it break log chain...
April 30, 2008 at 5:09 am
sharon (4/30/2008)
mike please can you explain with examples to make it more clear
nkm129 (4/30/2008)
Thanks mike and all other guys who has give grt explaination ...Only think what is left here How to copy my backup to another disk.. I tried xp.cmdshell but it's not working for me... could someone please let me know syntax for this....
One more thing ...
I may have to set aside a little time and work my thoughts into an article, and address it in a complete, structured fashion, rather than just in "sound bytes".
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
April 30, 2008 at 5:18 am
nkm129 (4/30/2008)
Thanks mike and all other guys who has give grt explaination ...Only think what is left here How to copy my backup to another disk.. I tried xp.cmdshell but it's not working for me... could someone please let me know syntax for this....
One more thing
Will Full or Diff backup truncate log file or not .. or if i put in this waty that will log shipping work if I use Full or diff backup regular way .. won't it break log chain...
- don't use xp_cmdshell for this. With a sqlagent job, you can choose a jobstep to use "operating system command (cmdexec)" type and then issue an xcopy yourbackuppath\*.bak yourUNCsafezonepath
- "Will Full or Diff backup truncate log file " No ! Only log backups free up the log.
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 30, 2008 at 6:06 am
Thanks Mike and ALZDBA.... It would be great mike if you publish an article.. it would be really great help for new dba like me to understand all process....
I think now I have idea to create backup and copy data from one place to anothe and understand that log shipping will not break log chain untill we take back up of log files...
thansk to you guys...
Only thing :- How to append datetime stamp to backup file...
like I want to backup test database in another folder with name having test_20080430...
I tried this but no luck
BACKUP LOG [ACCOUNT_MANAGEMENT_V1.2] TO DISK = 'C:\ACCOUNT_MANAGEMENT_V1.2_LOG '+getdate()+'.bak'
Thanks!
April 30, 2008 at 6:24 am
nkm129 (4/30/2008)
Only thing :- How to append datetime stamp to backup file...like I want to backup test database in another folder with name having test_20080430...
I tried this but no luck
BACKUP LOG [ACCOUNT_MANAGEMENT_V1.2] TO DISK = 'C:\ACCOUNT_MANAGEMENT_V1.2_LOG '+getdate()+'.bak'
select 'C:\ACCOUNT_MANAGEMENT_V1.2_LOG '+convert(char(8),getdate(),112)+'.bak'
Adding the hour and minutes is possible but takes some extra work. Your example didn't show that, so I was lazy 🙂
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
April 30, 2008 at 6:46 am
This is showing error
Incorrect syntax near '+'.
May 1, 2008 at 2:00 am
Mh,
I'm not sure if we're all thinking the same thing when you say the size of your database. What is the size of the files on the disk? How much is actually used by the data & log files?
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply