InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 06:44:16 GMT20http://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspx[quote][b]calvo (2/12/2013)[/b][hr][quote][b]Beatrix Kiddo (2/12/2013)[/b][hr]I hate this job already :-D.[/quote]Congratulations on your new Senior DBA position![/quote]Made me laugh![quote]How long does it take to fetch the offsite backup, and how long will you have to restore a DB in the case of a disaster?[/quote]It takes about 5 minutes to get the file back, and I would have an hour or two to do a restore in the case of a disaster. This place is fairly slack. Each day a backup is taken, and then a copy of [i]that[/i] is sent to an offsite repository. Daily backups are kept for a month, then weekly ones for 4 weeks, then monthly ones thereafter (if you follow me). So at all times we have the last 30 days' daily backups for each database, as well as weekly ones before that, and monthly ones before that. I do get your general point though; the current set-up is not ideal. (The backup jobs were already scheduled before I arrived, although there's no documentation to say why they've done what they have- and it probably goes without saying at this point in the thread; the person who did it has left!)I have loads more reading to do, I think. I'm sure I'll be back as I haven't quite resolved the first issue yet, but in the mean time thanks very much for all the help. I really appreciate it.Tue, 12 Feb 2013 08:47:45 GMTBeatrix Kiddohttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxI don't believe anyone has mentioned it but are you using backup compression?Tue, 12 Feb 2013 08:45:08 GMTcalvohttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxWelcome to trial by fire! :-DFor the transaction logs, there's a lot to understand about the different recovery models. This site has a good stairway on the topic at [url]http://www.sqlservercentral.com/stairway/73776/[/url].For the backups, I would [b]not[/b] want to keep a single day's backup for each database and call it good. Look at writing a stored procedure to do your backups for you and schedule it in a database job. That way, you could generate the SQL to do the backup and include the date in the filename. If that seems a bit daunting, (which it should given the other work you have do do at this point) look at Ola Hallengren's maintenance scripts. They are free, very well-respected and maintained. The work's already been done. Anthony.Green included a link to the site it his signature. [url]http://ola.hallengren.com/[/url]My two cents. Welcome to your new position and good luck!Tue, 12 Feb 2013 08:31:13 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspx[quote][b]Beatrix Kiddo (2/12/2013)[/b][hr]I hate this job already :-D.[/quote]Congratulations on your new Senior DBA position!Tue, 12 Feb 2013 08:02:58 GMTcalvohttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspx[quote][b]Beatrix Kiddo (2/12/2013)[/b][hr][quote][b]GilaMonster (2/12/2013)[/b][hr][quote][b]Beatrix Kiddo (2/12/2013)[/b][hr]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?[/quote]In a very bad situation if you overwrite the old backup with a backup that proves to be unrestorable...[/quote]But surely not if the previous day's backup has been backed up offsite before the file is overwritten each day?[/quote]How long does it take to fetch the offsite backup, and how long will you have to restore a DB in the case of a disaster?Tue, 12 Feb 2013 07:56:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspx[quote][b]GilaMonster (2/12/2013)[/b][hr][quote][b]Beatrix Kiddo (2/12/2013)[/b][hr]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?[/quote]In a very bad situation if you overwrite the old backup with a backup that proves to be unrestorable...[/quote]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.Tue, 12 Feb 2013 07:54:38 GMTBeatrix Kiddohttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspx[quote][b]Beatrix Kiddo (2/12/2013)[/b][hr]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?[/quote]In a very bad situation if you overwrite the old backup with a backup that proves to be unrestorable...Tue, 12 Feb 2013 06:55:43 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxYou 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.Tue, 12 Feb 2013 05:43:33 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspx[quote][b]anthony.green (2/12/2013)[/b]But it still sounds like your backing up to 1 big file and not individual files.[/quote]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;[i]BACKUP DATABASE RLT TO DISK='E:\RLA_BACKUPS\RLTBACKUP.BAK'WITH INIT, FORMAT, NAME = 'Full Database Backup', STATS = 10[/i] 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.)Tue, 12 Feb 2013 04:49:48 GMTBeatrix Kiddohttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxI 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.Tue, 12 Feb 2013 04:28:15 GMTOTFhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxProbably 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 - [url]http://www.sqlservercentral.com/articles/books/89519/[/url]Tue, 12 Feb 2013 04:20:57 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxSo 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?Tue, 12 Feb 2013 04:18:20 GMTBeatrix Kiddohttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxBreaking 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.Tue, 12 Feb 2013 03:59:48 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxNow 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) ;-).Tue, 12 Feb 2013 03:57:32 GMTBeatrix Kiddohttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxYou 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.Tue, 12 Feb 2013 03:53:46 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxThank 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!Tue, 12 Feb 2013 03:49:36 GMTBeatrix Kiddohttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspx[quote][b]Beatrix Kiddo (2/12/2013)[/b][hr]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?[quote]Set your backups to back up to individual files, preferably with the date as part of the name.[/quote]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.[/quote]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.Tue, 12 Feb 2013 03:44:24 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxCoincidentally 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?[quote]Set your backups to back up to individual files, preferably with the date as part of the name.[/quote]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.Tue, 12 Feb 2013 03:41:15 GMTBeatrix Kiddohttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspx[quote][b]Beatrix Kiddo (2/12/2013)[/b][hr]5) Schedule a regular backup of the transaction log, [b]with overwrite[/b]will that work ok and solve my problem? [/quote]As long as you never want to restore that log backup, as it will be completely useless....Please read through this - [url=http://www.sqlservercentral.com/articles/64582/]Managing Transaction Logs[/url]Log backups form a chain, to restore to a point in time you need [b]all[/b] 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.Tue, 12 Feb 2013 03:29:04 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1418837-1292-1.aspxHi(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 [b]backup[/b] 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 sideIf I1) Switch to Simple recovery model2) Delete the backup.bak file (?)3) Switch back to Full recovery model4) Take a full backup5) Schedule a regular backup of the transaction log, with overwritewill 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.Tue, 12 Feb 2013 03:24:59 GMTBeatrix Kiddo