?
Solved

Efficient SQL DB Differential Backup

Posted on 2010-11-30
4
Medium Priority
?
529 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
adamsjs earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

801 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