Solved

Efficient SQL DB Differential Backup

Posted on 2010-11-30
4
520 Views
Last Modified: 2012-05-10
Hi,

I use the following script to do differential backups of the database.  Is there a way to only run the backup if something has changed within the DB?  

I notice that the backup always generates an output file, even if there have been no changes.

DECLARE @dateString CHAR(13), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2), @dbPath VARCHAR(255) 
--month variable 
IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2 
   SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2)) 
ELSE 
   SET @monthSTR= '0' + CAST(MONTH(GETDATE()) AS CHAR(2)) 
--day variable 
IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2 
   SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2)) 
ELSE 
   SET @daySTR='0' + CAST(DAY(GETDATE()) AS CHAR(2)) 
--hour variable 
IF (SELECT LEN(DATEPART(hh, GETDATE())))=2 
   SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2)) 
ELSE 
   SET @hourStr= '0' + CAST(DATEPART(hh, GETDATE()) AS CHAR(2)) 
--minute variable 
IF (SELECT LEN(DATEPART(mi, GETDATE())))=2 
   SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2)) 
ELSE 
   SET @minStr= '0' + CAST(DATEPART(mi, GETDATE()) AS CHAR(2)) 
--name variable based on time stamp 
SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + '_' + @hourStr + @minStr 

--================================================================= 
Set @dbPath='c:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\sgMaster_Diff_' + @dateString + '.bak'

BACKUP DATABASE [sgmaster] TO  DISK = @dbPath WITH  DIFFERENTIAL ,  DESCRIPTION = N'Backup of the SourceGear Vault Master SQL Database', NOFORMAT, NOINIT,  NAME = N'sgmaster-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Open in new window


Thanks in advance
0
Comment
Question by:ITPOL
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
adamsjs earned 500 total points
ID: 34238113
Not really.  There isn't a mechanism for determining if database changes have been made, unless you were to use undocumented and unsupported features for examining the content of database pages.  The resources you use figuring out how do this, and then doing it, will never be offset by what you save in avoiding a "unnecessary" backup.

When a backup is run, the backup will always generate a file (or send data to tape/backup device). There is header/metadata about the backup in the backup file in addition to the actual backed-up data.

Also, keep in mind that the differential will backup all database pages which have changed since the last full backup.  Even if there are no changes since the last differential, the same pages will need to be backed up.

How often are you performing your differential backup, and is there a problem with having a the backup file generated when no data changes have occurred?  If so, aren't you having this same issue with your transaction log backups?  The sgmaster database for Vault is typically run in full recovery model, so I would anticipate you are performing regular transaction log backups, and those are generally more frequent than differential backups.

I'd like to offer a tip for generating your backup file name as well.  I have some tasks where I have to generate a file with a timestamp as part of the name, and generally do this using CONVERT and REPLACE functions, converting current date and time to a varchar, then replacing spaces, colons, and dashes with empty strings.  Here is an example:
DECLARE @dbpath varchar(255)

SET @dbpath = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\sgMaster_Diff_' + 
		REPLACE(REPLACE(REPLACE(CONVERT(varchar,CURRENT_TIMESTAMP,120),'-',''),' ',''),':','') + '.bak'

PRINT @dbpath

--- Sample @dbpath output:  C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\sgMaster_Diff_20101130061422.bak

Open in new window


If you wanted to maintain the name format you are currently using, you could do this:
DECLARE 
	  @dbpath varchar(255)
	, @now datetime

SELECT
	  @now = CURRENT_TIMESTAMP
	, @dbpath = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\sgMaster_Diff_' + 
		LEFT(REPLACE(CONVERT(varchar,@now,120),'-',''),8) + '_' +
		REPLACE(CONVERT(varchar(5),@now,108),':','') + '.bak'

PRINT @dbpath

Open in new window


Lastly, if you haven't yet looked at maintenance plans for managing your backups and other database maintenance tasks, you might consider doing so.  They are not always the best solution (especially in large, complex SQL Server environments), they will handle scheduling backups, naming of the backup files with timestamps, purging old backup files, etc. easily.
0
 
LVL 5

Expert Comment

by:adamsjs
ID: 34275931
Just wanted to know if this information answered your questions.  Please let us know.
0
 
LVL 1

Author Comment

by:ITPOL
ID: 34281368
Apologies for the delay and thanks!  yes, that makes perfect sense (the diff backups).  Its not causing a problem, I was just interested in whether it could be made more efficient, but of course, now that you've pointed it out, its obvious its working exactly as it should.

In regards to backup management, this is the only production database we have at the mo, and im really the only one that uses it, so I just have scheduled backups with the option to do a manual one when I want aswell.  I'll bear it in mind though if we start having a larger more complex DB environment.

Thanks for your suggestion on the backup script, that certainly simplifies it a bit.  I've not implemented it yet, but will as soon as i get a chance.

Thanks for all your help.

0
 
LVL 1

Author Closing Comment

by:ITPOL
ID: 34281375
Fantastic response, understood the question completely. Cheers
0

Featured Post

Google Storage: Standard vs. Nearline vs. Coldline

Google Cloud Storage has a number of classes to choose from. Although there are a lot in common, they vary in price and usage terms. This post explains Google Cloud Storage classes and helps to understand which  one to choose.

Join & Write a Comment

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now