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 |
|
toclark
Starting Member
3 Posts |
 Posted - 2004-08-05 : 12:43:48
|
| I've spent the afternoon reading as much as possible on how to backup and restore databases except there are a couple of small areas I can't quite get my head round, so apologies for the beginners question but here goes:I can set up a disk backup device and then every night run the 'Backup Database' command to back up our database to a local folder, on top of this I can run the 'backup log with truncate_only' every 20 minutes to provide even better restore capability. I have tested these functions and successfully restored the DB, but in this scenario the backup device is one file and will simlpy keep growing every day. How can I overcome this? Should I be creating a new file for every day and then deleting old files? Also, should each transaction log be backed up to a new file - if not then how do you know when to start a new backup file for the transaction logs without the possibility of loosing data?Cheers, Tom |
|
|
MuadDBA
628 Posts |
Posted - 2004-08-05 : 13:11:13
|
| It seems there are more than a couple areas your head hasn't wrapped around. :)If you do a backup log with truncate_only you are clearing out the transaction log without actually storing any of the data it contained, ie you make your database non-recoverable to a point-in-time. I don't really know how this provides "better restore capability."When you do your backup database statement, look up use of the WITH FORMAT or WITH INIT keywords so that you overwrite the previous backup with the current backup.Also read up a little more on transaction log backups. |
 |
|
|
toclark
Starting Member
3 Posts |
Posted - 2004-08-05 : 13:48:14
|
| Alright, I'll stop being a spaz with the truncate option... I'm a bit edgy about using the INIT option though, because if the .BAK file isn't backed up and the INIT deletes the only good backup of the DB from the previous day, then if the server crashed during the backup I would have to get a two day old file from tape.I may just trust the 'Maintenance Plan' to run the backups and create new files for every day, but in general I don't like running stuff where I'm not sure what's happening under the hood. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-05 : 14:10:28
|
| Don't ever run TRUNCATE_ONLY or NO_LOG option unless you immediately take a full backup afterwards. The only time I run it is when we are out of disk space on the server and we don't have the option of backing up the transaction log to a file. You can use WITH INIT but just don't have it write to the same file over and over again. It is recommended that you put a timestamp on the filename. That way you won't ever overwrite a previous backup. Have a look at my maintenance routines for solutions to avoiding the maintenance plans:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxTara |
|
|
|
toclark
Starting Member
3 Posts |
Posted - 2004-08-06 : 07:15:47
|
| Thanks for the tip and the scripts look very useful! |
|
|
|
|
|
|
|
|
Beginner at backups