Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
 Posted - 2006-12-04 : 18:28:40
|
| Hello everyone!I am pretty new to SQL here. Been using it for about 3 months now.I have some questions on how to properly setup my SQL server to be backed up properly so I can restore as needed. Let me lay down some ground work.We have one SQL server with one database, our primary database. We are using Veritas BackupExec 11D for our backup solution (SQL option).We have LT-03 backup tape, with gives us 400gb backup and 800gb when compressed. our Database, with logs, is about 55gigs. We have plenty of network bandwidth to have speedy backups.here is my question and please bare with me, I am really new and learning here.Right now, we have setup backup exec to backup the databse FULL each night. This is ok for us because we have the space on our tapes. My question comes with regards to the transaction logs. In the veritas agent, I have the option of backing up the logs and either truncating them, or not truncating them. The question is, im not sure which way to go.Anyone want to lend some suggestions?Such as, should I keep the backup recover method as FULL or should I switch to SIMPLE?Secondly, since I can backup the database FULL each now, what would be the best way to backup the t-logs? Should I backup up the t-logs on say Friday and truncate them that same night and then just backup the logs Monday-Thursday and NOT truncate them? That would mean that I would need to keep Friday-Thursday in case of a restore?Little fuzzy and nervous here. All help is greatly appreciated.Thanks, |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-12-05 : 00:08:45
|
| My advice:Don't use Veritas for database backups. Don't use any backup software that handles both disk/file and database backups. They're optimized for files and not for databases, and won't handle them properly. I've heard many times that Veritas FUBARs log backups too, and doesn't restore them correctly even if they're valid. Use standard SQL Server BACKUP commands or use SQL LiteSpeed from Quest Software. Idera also has a SQL Server backup product. These products only back up SQL Server databases and they treat them as such, not as files.Don't back up databases directly to tape. Back up to disk files and then back up those files to tape. In this case, Veritas would be adequate to back up the files. It's also faster to back up to disk, and you can use a separate disk volume to improve write performance. See below for more.As to how and when to back up transaction logs: that depends on how short an interval you can safely lose if you have to restore. Can you lose an entire day? 4 hours? 10 minutes? This interval will determine your log backup schedule.If you are doing a full backup every night, and don't need to restore to a smaller interval, then you can safely truncate the transaction log and/or set the database recovery to SIMPLE. If you need to restore to a smaller interval (say 1 hour) then back up your transaction logs every hour. DO NOT truncate the transaction logs if you are going to back them up (backup will clear them automatically).If you have a spare SQL Server, test your backups by restoring them. A backup is no good if it can't be restored. This is another reason to back up to disk; it's easier and faster to restore and therefore easier to validate that the backup is sound. Using the SQL Server verify option does NOT guarantee the backup is valid (sadly). This way, if your backup file is invalid you can avoid putting it on tape.Other people will weigh in with their advice, but generally they'll recommend backing up to disk files, then backing up those files to tape. If you don't have disk space available, it's more than worth it to buy some. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-05 : 01:03:28
|
| What Rob said.If you need to be able to (or would like to be able to!) restore to a point-in-time then you need Recovery Model = FULL. If restoring to just a (daily) full backup is good enough then Recovery model = SIMPLE. Do not keep FULL if you are not going to make frequent TLog backups - it will just cause your LDF file to be bigger - and in turn that will cause any Restore operations to be slower - and waste disk space that you might need for something else.I would lay down the following rules (don't mean to sound too authoritarian about it though!):Do NOT consider a once-a-week style backups of TLogs. Do NOT consider Truncating Tlogs as a valid approachDo NOT shrink the Database or Log files (except in exceptional circumstances)If you would like to restore to point-in-time then make TLog backups every 10 - 15 minutes - ask yourself: Can you afford to lose 10 minutes? Why is 15 minutes, or an hour, or a day, more acceptable? I'll bet it isn't!Backup to disk. Use unique names for each file (i.e. including Date + Time). Store those for as long as you are likely to need to restore from them, and until a replacement has been created (so if you do a Full Backup daily and Tlogs every 10 minutes you need to store Full backups for at least 2 days, and TLog backups for at least 25 hours)Then you can restore from the disk files if you need to.If you need to restore to last week / last year then it is very unlikely that you are going to replace your current live database with that restore. You are more likely to restore to a "new temporary" database, compare against live, find out what happened / audit / fraud / hacking / etc. and then sort out the Lice database and drop the Temporary restore.Then organize your Tape Backup to copy ALL the disk backups regularly. If your system is mission critical that means you need to backup the Tlog backups as soon as they are made. Most commonly that is done by copying them elsewhere on the LAN, but if you can get them to tape that's fine (assuming you have arrangements for off-site / remote location of tapes, and multiple tape units that give Disaster Recovery opportunity etc. etc.)And as Rob said you do need to test-restore your database. The mission critical operations here will test restore EVERY backup (on a different machine). After restore you need to do a DBCC CHECKDB to prove the backup was healthy (sadly just doing a restore is not proof that the backup worked OK, although I believe there are options in SQL2005 to validate a backup file)Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-05 : 01:05:14
|
| See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75782 |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-12-05 : 07:38:12
|
| I was going to post that one. In fact For our other servers we were using Veritas Backup exec, and I managed to stop the server admins from using it for SQL Server. Not that it was doing any good as they were only backing the servers up once a day (As recomended by the app vendors!), and transaction logs . . . no need to back them up. This is actually another good set of guidelines about backups that need to go into my SQL Server Principles document-- RegardsTony The DBA |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 2006-12-05 : 13:35:41
|
| Hello Kristen,Can you plese elaborate on this point of yours.Do NOT shrink the Database or Log files (except in exceptional circumstances)Because in my maintenace plan , I shrink the user databases every 2 days. Is this wrong or dangerous.Thanks |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-05 : 13:54:48
|
| Thanks everyone for youre great responses and help. It has been tremndous.Right now, I am gathering information from my manager on specifics such as, how long can we be down and what is our limit on how far back we can afford to lose; 1 hour, 4 hours etc.In the meantime, I will take the recommendation here and go ahead and NOT push my database backup to tape. Instead, I will go from DB -> disk -> tape, and keep a copy on the drive for the time being.Just a few more questions I have.First, on the transaction logs. After I fully back them up, I should truncate them correct? This is recommended in the SQL analyzer? ANd do NOT use veritas to backup the t-logs correct?That will make the 36gig file smaller?Ok, next question is, how often should I truncate the log? Or is that dependent upon how frequently I am going to backup the t-logs?Here is what I am thinking, and please feel free to give advice as I am still learning (but I have to say SQL and databases are a lot of fun)I can backup the database FULL each night.I was thinking of backing up the t-logs every 4-5 hours.Both of these should be kept on the disk for at least 2 days?How often should I "sweep" the t-logs to the tape during the day? Just some finer details I need to figure out to keep moving forward.I can't thank everyone enough for their help and suggestions. Cheers,TheCoffeeGuy |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-05 : 14:15:54
|
"Is this wrong or dangerous."IMHO Wrong - bit dangerous, per se, but very inefficient.The issue is that you have a bunch of things that your database does in a day - or perhaps a week is a better time unit.The database handles a bunch of queries, but it also handles a bunch of other stuff. Maybe some "heavy" queries for some integration to other systems. Maybe some REINDEX and UPDATE STATISTICS (which turn out to be heavy queries, but you may not have realised that). Whatever. For whatever reason those processes need xxxGB of log file to complete. They need the same xxxGB every time they run.So, basically, your database needs xxxGB as "working space" to get through the week.If you Shrink the database you remove that working space. And the next time that routine runs it has to extend the database.Trouble is a Backup [for example] has run in the meantime, so there is no longer enough contiguous disk space, so the re-allocated space for extension of the database is [in physical disk terms] fragmented. Less efficient.And in fact its a bit worse than that. In SQL2000 there is quite a "hit" on the system when the database is extended. The extension has to be pre-initialised. For an extension of several-hundred-MB this takes several minutes. If you have a very active database people's query response slows up. Perhaps they hit RETRY - which means that the queries start over - compounding the problem. Even if they don't, before their queries can finish more people's queries are started. The server is being asked to do lots more work than normal, and it can lead to TIMEOUTS and so on; Timeouts are BAD. Do you test your application for the effects of Timeout on each and every query? No, I didn;t think so, me neither! - so there is a good chance that you OUGHT to have a transaction block on a block of updates, but maybe in testing it never showed up that you hadn't; but a timeout will cause some of the data to be updated, and the rest not. Now the timeout is actually going to manifest itself as a database inconsistency - and you are hosed!If you have deleted millions of rows of stale data, then a one-time shrink might be justified. Likewise if you are about to move your database to a new machine and you need the transfer to be as quick (and therefore as small) as possible. But that's about it for Shrink.The rest of the time you want your TLog backups to be as often as practicable. IME that is between about 10 minutes and 15 minutes.Do NOT use a different schedule for "out of hours". Run the same 10-15 minutes schedule 24 hours a day (backing up an empty LDF/TLog file costs you almost nothing. However, some bright-spark will decide to create an Index Rebuild routine in the quiet time, and that will generate huge log files, and if they do NOT get backed up that will increase the size of your LDF files. And THAT will mean that your database files are unnaturally large - and when you need tor restore the backups in a hurry  it will take longer because Restore has to pre-initialise a load of disk space that is only needed because your files are unnaturally large.So: once-in-a-while defrag the physical files (e.g. using CONTIG from sysinternals.com)See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Database%20Shrinking,Shrink,ShrinkingKristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-12-05 : 15:13:47
|
quote:
Ok, next question is, how often should I truncate the log?
Best practice is never, because: quote:
Or is that dependent upon how frequently I am going to backup the t-logs?
Backing up the log truncates it. In fact, the command to truncate is BACKUP LOG...WITH NO_LOG.If you are making log backups, the only thing truncate will do is screw up your log chain, and prevent you from restoring. The only reason you should consider truncating the log is if you will never, ever back it up. You're better off backing it up, you can always discard the backup files if you have to.As to when you should backup files to tape, your network admin would be the best person to ask. They may have a particular schedule they need to adhere to. I definitely suggest keeping your backup files on disk for at least 2 days, or 2 backup cycles, whichever is longer. That way you have backups on disk and at least 2 tapes. Also find out how often they cycle tapes and how often they take tapes offsite. In a crunch restore scenario, you don't want to be stuck with your only backup offsite and not available for several hours.I'd also recommend doing some run throughs of an emergency restore operation, just so you get the hang of it. It's definitely not something you want to be figuring out while your DB is offline, and good practice for your whole IT group too. Might want to test getting an offsite tape as well. |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-05 : 15:33:13
|
quote:
Originally posted by robvolk
quote:
Ok, next question is, how often should I truncate the log?
Best practice is never, because: quote:
Or is that dependent upon how frequently I am going to backup the t-logs?
Backing up the log truncates it. In fact, the command to truncate is BACKUP LOG...WITH NO_LOG.If you are making log backups, the only thing truncate will do is screw up your log chain, and prevent you from restoring. The only reason you should consider truncating the log is if you will never, ever back it up. You're better off backing it up, you can always discard the backup files if you have to.As to when you should backup files to tape, your network admin would be the best person to ask. They may have a particular schedule they need to adhere to. I definitely suggest keeping your backup files on disk for at least 2 days, or 2 backup cycles, whichever is longer. That way you have backups on disk and at least 2 tapes. Also find out how often they cycle tapes and how often they take tapes offsite. In a crunch restore scenario, you don't want to be stuck with your only backup offsite and not available for several hours.I'd also recommend doing some run throughs of an emergency restore operation, just so you get the hang of it. It's definitely not something you want to be figuring out while your DB is offline, and good practice for your whole IT group too. Might want to test getting an offsite tape as well.
Thanks. I appreciate it.So just so I am clear, I should never truncate the logs, just back them up? And by backing them up, it truncates them? Sorry, just making sure I understand.Im the network guy here too (jack of all trades) and I was going to start the backups around 10pm. That is off-peak times (really, 10-7 are "heavy" hours). That should give me plenty of time to perform the backups.So here is my plan:Starting tonight, I was going to perform a a FULL database backup, followed by a FULL log backup, using Enterprise manager. They will go to disk and copied to tape.Then, starting tomorrow, from 10-7, I will perform transactional log backups every 3 hours. And those will be put to disk first, then to tape. Sound good?Also, right now, the Recovery model is "Full." Should I keep it like that? Should the master DB be FULL or SIMPLE?I think that is it for now.Thank's for the help.TCG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-05 : 16:25:44
|
| Just to check: Did you see my reply at "12/05/2006 : 14:15:54"? That pretty much crossed with yours ..."And by backing them up, it truncates them? "In effect, yes. (The space used by the transactions which are backed up becomes available for reuse)"I was thinking of backing up the t-logs every 4-5 hours."Why not every 10 minutes? Ask yourself: Can I afford to lose 5 hours work, rather than 10 minutes work?"I was going to start the backups around 10pm"It probably doesn;t matter too much, but if 67PM to 10Am are "quiet" hours then I suggest backing up at 00:00 - that way your full backup coincides with the new day. Reason: sometimes it seems to management to be important to create a scenario that matches the date!!!!"starting tomorrow, from 10-7, I will perform transactional log backups every 3 hours."Nah, don't make it complicated. Schedule the TLog backup every 10 minutes, 24 hours a day. During the night you will make Index Rebuilds, Update Statistics, Import data, ... and they will be every bit as CPU/Disk intensive as the users during the day. Prevent the TLog growing huge by backing up the Tlog as regularly during the night as you do during the day. (And if the Tlog backup is 0-bytes so what?!"Also, right now, the Recovery model is "Full." Should I keep it like that?"Yes, I reckon. Only reason NOT to is if you can recreate your database from somewhere else - realistically that only applies to Data Warehouse type operations - and NOT to OLTP.Kristen |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-06 : 12:04:38
|
| Thanks for the follow up. Since my last reply, looks like we are going to backup the t-logs every hour; the database will have a FULL backup at midnight Monday-Friday (our working hours).Just a couple of quick questions about properly setting up the backup within Enterprise manager.DATABASE: This is pretty straightforward. You backup the database to the local disk (which is then backed up later). One quesiton though: I want to automate this job so it runs Monday-Friday at midnight. When you select the location on where you want the backup to be placed, you give it a name. Now, since I want keep two days worth in that location, how will it add another backup without overwriting the existing backup?TRANSACTION LOGS: Pretty much very similiar to the above. Same question though; does it overwrite the exisiting backup?Just want to make sure I set it up properly.Secondly, after I backup the t-logs hourly, how often should I run my veritas backup agent? Nightly? Hourly?I will start there, schedule my jobs and if I have any questions, I will respond back.Thank you very much.-Jason |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-06 : 12:25:19
|
| One other question on restoring properly.After I run a full backup of the database and I run t-log backups every hour, what is the process of keeping a chain to properly restore? I am confused here.For example, if I backup my database at midnight, backup my t-logs hourly, then have a crash at say 3pm, would I just restore the full backup and t-logs? Little iffy here.Thanks |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-12-06 : 12:56:59
|
quote:
Originally posted by thecoffeeguy
For example, if I backup my database at midnight, backup my t-logs hourly, then have a crash at say 3pm, would I just restore the full backup and t-logs? Little iffy here.
The restore of the full backup would put you back as you were close of business the previous day (including anything else that happened between close of business and taking the backup). You then restore all the logs since that full backup was taken. That will apply all the changes made between the full backup and the last log backup you took before the crash. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-06 : 12:57:57
|
| "looks like we are going to backup the t-logs every hour"Best to ask yourself: Are you are comfortable losing an hours work, when scheduling your Tlog backup every 10 minutes would mean only losing 10 minutes work?"the database will have a FULL backup at midnight Monday-Friday"I would do it every night. If you have to restore it just makes for increased risk of operator error if the operator has to consider when the most recent backup was."how often should I run my veritas backup agent?"Well, you want to get the backup files copied off the server as soon after they are written as possible. Copying them to another server would achieve that - and then the tape becomes the backup-of-last-resort.If you can run Veritas to append to tape frequently that's great - but it assumes that there is a suitable tape in the drive, and its not being used for something else at the time of course ... but it may be dedicated to your machine / needs."what is the process of keeping a chain to properly restore?"Basically include the date/time in the filename of the backup.It is possible to query MSDB to see what was backed up an when - which can help get a restore list together. But assuming a disaster and MSDB has been lost, then having filenames which include date + time helps too."Little iffy here"TLog backups and Full backups are independent of each other. You can restore last nights FULL and each of the TLog backups since then (all, and in sequence) up to the point your want to restore to.Or you can restore the previous nights FULL backup, and all the TLog backups, in sequence, since the FULL backup (i.e. more Tlog backups to restore). This can help if for some reason last nights FULL backup is damaged.See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,BackupKristen |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-06 : 13:17:55
|
quote:
Originally posted by Kristen
"looks like we are going to backup the t-logs every hour"Best to ask yourself: Are you are comfortable losing an hours work, when scheduling your Tlog backup every 10 minutes would mean only losing 10 minutes work?
Good point. I might just do that. Every 15-30 minutes range.quote:
"the database will have a FULL backup at midnight Monday-Friday"I would do it every night. If you have to restore it just makes for increased risk of operator error if the operator has to consider when the most recent backup was.
Oh, that is what I meant; Monday through Friday, full backups at midnight.  quote:
"how often should I run my veritas backup agent?"Well, you want to get the backup files copied off the server as soon after they are written as possible. Copying them to another server would achieve that - and then the tape becomes the backup-of-last-resort. If you can run Veritas to append to tape frequently that's great - but it assumes that there is a suitable tape in the drive, and its not being used for something else at the time of course ... but it may be dedicated to your machine / needs..
I was going to play for Veritas to backup the SQL backup just after the backup completed. THat way, I have it on tape.Now, when I am running the t-log backups during the day, should I try to get those pushed onto the tape drive as well?quote:
"what is the process of keeping a chain to properly restore?"Basically include the date/time in the filename of the backup.It is possible to query MSDB to see what was backed up an when - which can help get a restore list together. But assuming a disaster and MSDB has been lost, then having filenames which include date + time helps too.
Is it possible, somehow, to have the Enterprise manager put a timestamp on the name of the backup or somehow? Or is that just something I need to do?quote:
"Little iffy here"TLog backups and Full backups are independent of each other. You can restore last nights FULL and each of the TLog backups since then (all, and in sequence) up to the point your want to restore to.Or you can restore the previous nights FULL backup, and all the TLog backups, in sequence, since the FULL backup (i.e. more Tlog backups to restore). This can help if for some reason last nights FULL backup is damaged.Kristen
In the last part, when you say "all the TLog backups,", how far back would that go back? Before the full? Or just after the full?Really appreciate your help and patience Kristen.Thanks,TCG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-06 : 13:23:19
|
| "Oh, that is what I meant; Monday through Friday, full backups at midnight"Still sounds different to what I meant! Make FULL backups every night - i.e. all 7 nights in the week. Do not do something different on Saturday and Sunday"Is it possible, somehow, to have the Enterprise manager put a timestamp on the name of the backup"Dunno ... but backups made via the Maintenance Plan Wizard will."[/i]how far back would that go back?[/i]"You have to restore ALL Tlog backups that were made AFTER the Full backup you are restoring from.Kristen |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-06 : 13:30:41
|
Got it...It is starting to sink in now.Talk about crash course. I need to let this sink in a bit more and do some more thinking. Overall, I have a FAR better understanding now of how to implement better backups and how SQL backups work (DB backups and TLog backups).I am a perfectionist (which can be a curse and a good thing), so I always want to be very thorough and make sure I get it right.  In the meantime, I appreciate everyones help here and feedback. It has been tremendous help and boost for me.Cheers,TCG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-06 : 13:53:19
|
| Have a read of the "how backups work" via the link I posted earlier. That's what I wrote when I "got it" - so my ramblings then might help the thinking process. |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-06 : 14:30:44
|
| Just a quick question, regarding backing up the TLogs in EM.When you selec to backup the DB, select Transaction log, there is a "Options" tab at the top. Do I need worry about selecting any of those during the backup?The one that stands out is:"Remove inactive entires from transaction log"Wasn't sure, and before I back it up, I wanted to ask.Also, after I run this backup, SQL truncates the log?Thanks again.TCG |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-06 : 19:40:01
|
| Just a follow up on how to properly back up TLogs within EM.Is it pretty straightforward? Just backup, select transactional logs, select the location and that is it?Just want to make sure I do this right.THanks |
|
|
|
Next Page
|
|
|
|
|
Backing up Databases: little help?