Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I´m new to SQL Server administration.

I´d like to create automatically backup database every Sunday night, but only if there was any change in previous week (if not, backup is unnecessary).

Is there some way how to do that only with SQL Server, like some automatic procedure or something like that?

share|improve this question
There are many reasons why this isn't practical. Use a differential backup and then don't worry about it... –  Joel Mansford Sep 3 '12 at 15:58
@VilemRousi, I just answered your question on how to backup only the changes. However if we need to explain how to automate something for example "making backups" in general please say so. I've no idea on how new you are to MS SQL. –  Edward Dortland Sep 3 '12 at 16:49
@Edward Dortland - Thank you, for quick answer. About my database skills - I´m not new in SQL, but this is my first time, I want to administrate MSSQL. This question is last step of my school project for. I have a program C# which read and write data to SQL DB. Originally I thought about backup DB from program when user will updating the DB, but at last I don´t want to burden DB in every update, so I decide to use some automatic procedure or something like that for automatic backup in side of server. –  VilemRousi Sep 3 '12 at 18:10
How big is the database? –  Edward Dortland Sep 3 '12 at 18:14
It´s very small. Only 8 tables, with 50 rows in each table. –  VilemRousi Sep 3 '12 at 18:18

1 Answer 1

up vote 3 down vote accepted

Welcome to MS SQL :-)

To start with your question:

Yes there is. First you make a full database backup:

BACKUP DATABASE yourdatabasename
TO DISK = "pathtobackupdir\backupfilename.bak"

Now from that moment on a so called differential bitmap is keeping track of all pages changed.

From that moment every time you create a DIFFERENTIAL backup, only the pages that changed since your last FULL backup will be backupped.

 BACKUP DATABASE yourdatabasename
    TO DISK = "pathtobackupdir\backupfilename.bak" WITH DIFFERENTIAL

So every sunday, asuming you had changes every week, the diff backup will be bigger then the previous one. Untill one sunday, it's just as big as a full backup would be. That would imply that all data pages had changed. At that moment you want to do a new full backup. Since that will reset the differential bitmap and your next diff backup will be small again. (depending on the amount of changes)

You could automate the process of making diff backups untill a certain percentage of the data has changed. Have a look at this blog post: http://www.sqlskills.com/BLOGS/PAUL/post/New-script-How-much-of-the-database-has-changed-since-the-last-full-backup.aspx It explains a way on how to programatically look up the percentage of database changes. That way you could say.. make diff backups if percentage changed < 75% otherwise make a full backup.

Now could you tell me why you want this? Because this isn't a setup I would advise a starter (with exceptions). So please elaborate a bit and we can see if this is the best solution for your setup. How big is your database? how much changes do you have? is batch based changes? Which version and Edition of SQL do you have do you have?

share|improve this answer
+ 1 for the answer :) Is it ok to do this kind of backup? Because in my work I do backup in the on the server generously..Our database is quite big because I am working in a College Institution. –  Prince Jea Sep 4 '12 at 3:05
The only problem that I see with the diff backups is that if some one takes an ad-hoc full backup without COPY_ONLY, then the diff would be useless affecting the recovery process. –  Kin yesterday

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.