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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-07 : 08:13:39
|
| Shay writes "I'm trying to decide about the right backup strategy for SQL 2000 Servers.I Decided to backup aFULL DATABASE BACKUP every night as abaseline and backup the transaction log every 15 minutes during the day.If I'll need to use those backups because of asystem failure I'd have to restore the full db backup first and than all the t-log backups.the question is:if I'll backup my t-log with the NO TRUNCATE option would the latest t-log backup will contain all the previous t-logs backups ? according to Microsft Books the BACKUP LOG statement backs up only the info frm the last BACKUP LOG statement.but would it be different if I use NO TRUNCATE option ?if this option means that the last t-log backup will include all the info of the hall t-log including info that was backed up by the previous BACKUP LOG statement I would be able to restore only one copy of t-log backup instead of many.So...would it work ? can I save time this way ?or would I'd have to restore aseria of t-log backups anyway ?" |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-07 : 09:56:30
|
| The way I do it is 1 full every day, 1 differential every hour and 1 tlog at 15, 30, and 45 minutes past the hour.At most I will have to restore 1 diff or full and 3 tlogs.You can also write a script to identify the latest set of backups to restore and perform that operation all at once.You'd use the command shell sp to execute a dir command and insert it into a table. Then run sql to extract the names of the backup that last ran based on file name (that is if you named the files in such a fashion as to have the time embedded in the name) and then loop around a restore command. |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-07 : 11:49:31
|
| I'm not sure, but somehow, I don't think that would work. Should be easy enough for you to test though.Even if it works, I don't think it is a good idea. Your T-Log would grow without ever being truncated. Valter's suggestion is much better if you are worried about the number of backups you will have to restore.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-07 : 12:34:51
|
| I do it the same way as ValterBorges but without the differential (we do ours through log shipping). If we ever have to do a RESTORE, we just write a script that would write out each of the RESTORE LOG commands for us instead of having to type them all out.Tara |
|
|
|
|
|
|
Transaction log Backup and Restore.