May 23, 2012 at 10:22 am
I created a maintenance plan for my database that will create Full database backups every week. I noticed that the size of the .BAK file is decreased from previous week's file size? i thought i should always see the file growing, but why is it decreased?
can someone tell me why this happens?
1st Sunday(4/24)- 5.76 MB(full db backup)
2nd Sunday(4/29)- 12.2 MB(full db backup)
3rd Sunday(5/6)- 6.83 MB(full db backup)
May 23, 2012 at 10:27 am
Based on what you have provided, no idea. We can't see from here what you see there. Just not enough information to tell you.
If I was at your desk I would start by looking at the maintenance plans, how are they configured? Am I appending each backup into the same file? How long am I retaining these files?
May 23, 2012 at 10:28 am
Has DBCC SHRINKDATABASE been run since? Or any DELETEs from the database? Has the tx log shrunk? Various reasons for this, it's normal tbh.
It must be nice to have a 5MB database to look after. Ours are monsters :crazy:
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
May 23, 2012 at 10:58 am
we just went live with our new database.i created the plan using the defualt setting from the wizard.
attached is my full db backup maintenance plan screen.
May 23, 2012 at 11:12 am
preetid2 (5/23/2012)
we just went live with our new database.i created the plan using the defualt setting from the wizard.attached is my full db backup maintenance plan screen.
Nope, still not able to give you any real answer, so I'm not even going to try to take a shot in the dark.
May 23, 2012 at 11:18 am
Shrink database doesn't matter for the backup sizes. Whether you have 1% free space or 80% free space, the full backup is just the data.
If the data and objects in the database grow, the database grows. If you delete things, the full backup may be smaller. There's no reason to necessarily be concerned, and backups should not always grow.
More tables, more data, less tables, less indexes, all can affect the size of the full backup. I only look at full backup size as a method of determining if something changes, or long term trending for space planning. There's no reason to be concerned if the backup varies a little.
In your example, you are down 6MB, but 12MB isn't much size either. I wouldn't be concerned, unless you are missing data. You'd have to check what happened.
The backup size has nothing to do with maintenance plans.
May 23, 2012 at 11:24 am
Thanks Steve
what is Append to existing mode means in the maintenance plan?
I am moving the .BAK file everyday into the archive folder. so the folder is empty when the next new .BAK file is created.
May 23, 2012 at 11:35 am
Append adds the new backup data to the same file. Essentially you get one file like this:
-----------------------------------------
backup 1 | backup 2 | backup 3 |
-----------------------------------------
all stored in one file in the file system. IF you need to restore, you have to specify the particular backup from the file. It's a risky thing to do since the corruption of this file means you lose multiple backups. However some people do it since they can keep the backup file name the same every day.
I do not recommend you append.
May 23, 2012 at 11:37 am
There is a difference between the .bak file and the backup device. SQL Server needs an object called a backup device to which it passes the backup data. The backup device then processes the backup. I don't know all the dirty details, but I do know that when using T-SQL, you have to create the device outright, where as the device is auto-created / implied when you use maintenance plans or third party tools. You also don't see the devices in our GUI when using plans or third party tools.
Append means exactly what the word says. It appends data to the existing backup device (and by extension, the backup file) if there is an existing file (attached to the device) for the data to be appended to.
A device can have multiple files, but generally speaking a file is only connected to one device.
BTW, when I speak of backup devices, I mean logical backup devices, not physical backup devices (Different other horse, different other color... Sort of.)
May 23, 2012 at 12:20 pm
oh, ok Steve. so , if i don't have the previous file in the exisiting the folder, since there's no file exists my next backup will create the new file right?
I don't need to risk to use the append mode.
btw, can you please look at the attached screen shot and tell me if the maintenance plan that i created using wizard by default is in append existing mode?
May 23, 2012 at 12:23 pm
preetid2 (5/23/2012)
oh, ok Steve. so , if i don't have the previous file in the exisiting the folder, since there's no file exists my next backup will create the new file right?I don't need to risk to use the append mode.
btw, can you please look at the attached screen shot and tell me if the maintenance plan that i created by wizard by default is in append existing mode?
Nope, not from what you showed.
May 23, 2012 at 12:23 pm
I believe that the way you have the maint plan set, it will create a new file by default. If you pick a specific file, you can access the drop down that says "append".
May 23, 2012 at 12:25 pm
preetid2 (5/23/2012)
oh, ok Steve. so , if i don't have the previous file in the exisiting the folder, since there's no file exists my next backup will create the new file right?I don't need to risk to use the append mode.
If there's no file in the destination, the next backup will create a new backup. My experience with maintenance plans is that they don't actually append to existing files unless you tell them to (and I don't recall if that's possible or not). They create a new file with a date / time "stamp" on the end of the backup file name every time they back up. Full backups usually have _full_ in the name, differentials usually have _diff_ in the name, and log files usually have _log_ in the name and a different suffix.
May 23, 2012 at 12:27 pm
My problem is I haven't created or used Maintenance Plans in so long, I have to look things up regarding them. I have been rolling my own processes for so long I don't worry about them.
May 23, 2012 at 1:33 pm
Thanks to you all for the responses!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply