February 12, 2013 at 3:25 am
Hi
(I'm new here, and am in a new job where I don't know the setup and have nobody else to ask, so I'm sorry if I ask stupid questions. I'm used to having a senior DBA to run things by, but to cut a long story short, this place doesn't have one.)
My transaction log backup file (not the log file) is too big, to the extent that I'm running out of space on that drive. I wanted to back up the transaction log with over-write (it seems to have been set to append in the past, even though backups of it were never taken!), but there isn't enough space on the disk.
The details are
- SQL Server 2008R2
- Full recovery model (for what it's worth; see above re backups!)
- Production environment, but low transactions/few users
- Very little help from any sysadmins on the storage side
If I
1) Switch to Simple recovery model
2) Delete the backup.bak file (?)
3) Switch back to Full recovery model
4) Take a full backup
5) Schedule a regular backup of the transaction log, with overwrite
will that work ok and solve my problem?
Thanks in advance. As I said, sorry if this is basic stuff. It's a new job, I have no support, and I'd hate to mess this up.
February 12, 2013 at 3:29 am
Beatrix Kiddo (2/12/2013)
5) Schedule a regular backup of the transaction log, with overwritewill that work ok and solve my problem?
As long as you never want to restore that log backup, as it will be completely useless....
Please read through this - Managing Transaction Logs[/url]
Log backups form a chain, to restore to a point in time you need all the log backups from the full that you're using to the point you're restoring to, not the last one.
Set your backups to back up to individual files, preferably with the date as part of the name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2013 at 3:41 am
Coincidentally I was just reading that article and was coming back to edit my post, but too late!
Is everything else ok though, apart from the need to set the log to append, not overwrite?
Set your backups to back up to individual files, preferably with the date as part of the name.
This is interesting; is that standard practice? Having had a poke around here, that's definitely not what they've done here, and not in my last place either. If it's best practice I'd like to do it.
Many thanks again.
February 12, 2013 at 3:44 am
Beatrix Kiddo (2/12/2013)
Coincidentally I was just reading that article and was coming back to edit my post, but too late!Is everything else ok though, apart from the need to set the log to append, not overwrite?
Set your backups to back up to individual files, preferably with the date as part of the name.
This is interesting; is that standard practice? Having had a poke around here, that's definitely not what they've done here, and not in my last place either. If it's best practice I'd like to do it.
Many thanks again.
Generally yes. If you have 1 media set where your constantly backing up to, so appending to that file time after time, should anything happen to that file, you wont be able to recover using any of the subfiles contained within it, so you have lost everything.
Where as if each backup is to a seperate file, your only ever risking that 1 file becoming un-usable and not all your backups.
February 12, 2013 at 3:49 am
Thank you. I'll do that once I've done some more reading. I do wish everybody here hadn't quit, this is all a bit much!
Can I go ahead with steps 1-5 now (with the modifications already mentioned above)? It's deleting the backup.bak file that particularly worries me.
Cheers!
February 12, 2013 at 3:53 am
You dont need to do steps 1 and 3 as that wont do anything except break the log chain.
If you have somewhere big enough to store the backup.bak file, move it there for now. Then take a full backup to a timestamped bak file, then do your transaction logs to timestameped trn files.
Ensure that you have a routine in place that pulls the files to a tape drive or some other backup media, so that you have recoverability going back in time as well, you never know when you might need a backup from.
In my signature there is a link to Ola's website, there is a very good comprehensive script which will do the backups for you to individual files.
February 12, 2013 at 3:57 am
Now that you say that (about points 1 & 3) it is actually obvious; sorry about that.
We do have overnight backups going to tapes in an external data vault.
Thanks very much for the script; I'll do that now. I really appreciate the help. This new job is not what was advertised, but I'm trying to make the best of it as it's a good learning opportunity (assuming I don't break everything) ;-).
February 12, 2013 at 3:59 am
Breaking things is a good way to learn as you need to fix them, so you learn what not to do and what to do should it happen.
Granted would break things on a mock environment away from the production systems, but sometimes it cant be helped.
February 12, 2013 at 4:18 am
So I've done that and now the log_backup.bak file is actually larger than it was, and is now larger than the database.bak file. Have I done something wrong here?
February 12, 2013 at 4:20 am
Probably not, depends how long ago the transaction log backup was last run.
But it still sounds like your backing up to 1 big file and not individual files.
Can you post the script(s) you have used to perform the backups?
This book might help as well - http://www.sqlservercentral.com/articles/books/89519/
February 12, 2013 at 4:28 am
I would also add that if you have just taken over your environment then you will probably need to be as interested in your ability to restore your current backups as your backups/scheduling.
Its a good idea to get yourself a test environment and attempt to restore what you have to ensure you can.
Its an ongoing best practice, I would think anyway.
February 12, 2013 at 4:49 am
anthony.green (2/12/2013)
But it still sounds like your backing up to 1 big file and not individual files.
The database or the transaction log, is this? The space issues we have mean that we need to overwrite the backup every night, so it was initially set up (not by me) like this;
BACKUP DATABASE RLT TO DISK='E:\RLA_BACKUPS\RLTBACKUP.BAK'
WITH INIT, FORMAT, NAME = 'Full Database Backup', STATS = 10
I know how to produce a backup file with the date in the name, but where does that leave me when I need it to be overwritten each night?
(And don't worry, I've moved over to the dev environment for now.)
February 12, 2013 at 5:43 am
You will need to build in a clear down routine to remove files older than so many days.
Again Ola's scripts include this by passing in a value to the CleanupTime variable.
February 12, 2013 at 6:55 am
Beatrix Kiddo (2/12/2013)
I know how to produce a backup file with the date in the name, but where does that leave me when I need it to be overwritten each night?
In a very bad situation if you overwrite the old backup with a backup that proves to be unrestorable...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2013 at 7:54 am
GilaMonster (2/12/2013)
Beatrix Kiddo (2/12/2013)
I know how to produce a backup file with the date in the name, but where does that leave me when I need it to be overwritten each night?In a very bad situation if you overwrite the old backup with a backup that proves to be unrestorable...
But surely not if the previous day's backup has been backed up offsite before the file is overwritten each day?
I hate this job already :-D.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply