January 3, 2008 at 11:59 am
I have a large SQL database that is in SQL 2005 but in compatiblity mode for SQL 2000. The recovery method is Bulk-Logged. When I use different backup methods the results produce different sized files. I am not sure why so looking for a little explanation.
The databse is 21GB,
When I right click the database in the SQL Manager and back it up the backup is 21GB.
When I run sqlmaint in a job the file is 21GB
When I use this script the backup size is huge and varies.
BACKUP DATABASE database
TO DISK = 'E:\DBBACKUPS\DATABASE.BAK'
Some times the file is 86GB others it is 108GB
I am really confused as to what is different in these methods that make the database backup so big.
I am also trying to find out what impact differential backups have with TLOGS. I do a full every night and TLOGS every hour. I would like to do a differential before I start a certain process and want to know how this will effect the TLOGS in time of a restore.
Thank you in advance.
January 4, 2008 at 7:47 am
One of the reason that you see the file size is big when you use BACKUP DATABASE command could be because your backup file under E:\ drive is append with multiple back up.
With first 2 steps you must be doing backup with INIT thus new files are create every time and not appended.
For the differential backup you should be able to do it but if you doing the full backup every nite and log backup, other suggestion is to do log backup before you do any implementation to cut the time.
Hope this will help.
January 4, 2008 at 11:57 pm
As Amu stated, its most likely that you are appending to an existing file.
Regarding differential backups, they are purely a copy of all pages that have changed since the last full backup. If you take a diff and then need to restore shortly afterwards, you save yourself the effort of restoring any log backups taken before the diff. A better option (if you have Enterprise edition and just want to make a restore point before the process begins) may be to take a snapshot - extremely quick to create (and restore from if needed), but there is some overhead as existing pages change. Unfortunately I don't remember if these are available in 2000 compatability mode.
Matt.
January 9, 2008 at 8:26 am
Thank you both, after reading your posts I have made some changes to my backup script. I think INIT was what I needed so that my files are not huge. My changes should get used today so I should know for sure by the end of the day. If not then I will probably go with the differential prior to these processes to capture the current state of the database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply