January 12, 2011 at 12:10 pm
Hi all,
I'm using SQL Server Express 2008 and want to create a nica backup plan for a number of non-critial databases:
Once a week (friday, 22:00) I make a full backup of all online databases. Weekdays I make a differential backup, every 4 hours.
But the diff. backup files is getting larger and larger. Is there a method to delete them when I create a full backup or is this the wrong kind of approach ?
any tips and help are welcome !
Kind regards, Piet
January 12, 2011 at 12:22 pm
The differential backup will contain all changes since the last full backup, so logically they will get bigger over time unless you have taken another full backup.
You might want to take a look at doing transaction log backups or doing Fulls more often.
January 12, 2011 at 1:49 pm
piet.kouwer (1/12/2011)
I'm using SQL Server Express 2008 and want to create a nica backup plan for a number of non-critial databases:Once a week (friday, 22:00) I make a full backup of all online databases. Weekdays I make a differential backup, every 4 hours.
But the diff. backup files is getting larger and larger. Is there a method to delete them when I create a full backup or is this the wrong kind of approach ?
As mentioned by Henry, the behaviour is normal.
If you only want to restore to a point-in-time in the last 24 hours, you can discard the differential backups older than a day.
Every time you create a full backup, you can discard the previous differential backups (unless you want to go back to a point before your last full backup).
What is your recovery model? If it is full, you should also be taking transaction log backups, as Henry mentioned.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 12, 2011 at 2:13 pm
at the moment the recovery model is simple, but I can change it easily. I want to backup databases every 4 hours. I always thought that when I create a new full backup, the differential backup automatically would be cleaned. At least not growing and growing.
I will considder using transactional backups. Is this also available on Express editions or only on Workgroup and above ?
January 12, 2011 at 4:53 pm
piet.kouwer (1/12/2011)
at the moment the recovery model is simple, but I can change it easily. I want to backup databases every 4 hours. I always thought that when I create a new full backup, the differential backup automatically would be cleaned. At least not growing and growing.I will considder using transactional backups. Is this also available on Express editions or only on Workgroup and above ?
First, I have a question - why do you want to backup the database every 4 hours? Not saying this is right or wrong (good or bad), but curious about the business requirement for performing a differential backup every 4 hours.
Second, are you running your backups to the same file every time? If so, can you show us the command you are using for your full backups and differential backups?
My guess is that your differential backups are done to the same file every time - and that you are not initializing the backup device. This would cause the file to continue growing larger and larger as new backups are performed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 12, 2011 at 11:44 pm
piet.kouwer (1/12/2011)
at the moment the recovery model is simple, but I can change it easily. I want to backup databases every 4 hours. I always thought that when I create a new full backup, the differential backup automatically would be cleaned. At least not growing and growing.I will considder using transactional backups. Is this also available on Express editions or only on Workgroup and above ?
According to the following URL:
http://msdn.microsoft.com/en-us/library/ms165636.aspx
you should be able to take transaction log backups (when the database is in full recovery or bulk logged mode of course).
(actually, the msdn page doesn't say anything about backups, so I assume you can do it)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 13, 2011 at 1:19 am
Restoring transactions is not available in SQL Express (at least not in the SSMS), so I guess this isn't an option.
I just tested some stuff and I can restore my differential backup to a date e.g. 16-11-2010, while my last full backup is made last friday, 22:00. I supose that SQL drops all sets in the diff-file at the time a full backup is made ?
January 14, 2011 at 3:56 am
piet.kouwer (1/13/2011)
Restoring transactions is not available in SQL Express (at least not in the SSMS), so I guess this isn't an option.
It isn't an option because you're running the database in Simple recovery mode--you can't make or restore log backups. Set up a database in Full recovery and you'd be able to do transaction log backups, even in SQL Server Express.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply