SQL Database Backup - Differential / Incremental?

Hi,

I have a databse with tables that update every minute. It is approximately 20GB and I would like to perform daily backups to a tape drive. I have managed to perform an initial backup (through SQL Server Management Studio) using the queries suggested here: http://support.microsoft.com/kb/927797. It took approx 20mins.

My next task is to ensure the database is backed up every night - what should I use - differential or incremental backups? I tried the Transact-SQL suggested here: http://msdn.microsoft.com/en-us/library/ms191180.aspx but the query is still executing and it has been over 40mins now.

Can anyone provide me with some guidance please?

Thanks,
lvnealAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
grayeConnect With a Mentor Commented:
Let's start with some basics....
  • SQL Server's BACKUP command really doesn't have an "Incremental" option (so that's one less decision to make!)
  • No, the database BACKUP does not include the transaction logs... a separate transaction log backup will be required
  • I'd agree that 2hrs is way too long.  I'd recommend you perform a "test" backup to disk (if you have the space) to get a time reference for both a "full" and "diff" backup
0
 
pap-deblancConnect With a Mentor Commented:
Differential Backups are highly dependant on the pattern of activity within your database.

The logic behind doing them is to limit the amount of disk space taken up by the backups, and not necessarily to limit the amount of time taken to carry out the backup.

That said, 40 mins does sound excessive - have you checked all the event logs to see if any errors are being flagged up?

I have not come across Incremental Backups of SQL Server before, although I understand their application in File Backups.

With a 20Gb Database which updates every minute, I presume you are using a Full recovery strategy and therefore also backing up the Transaction Log. In this case the time taken for both full and differential backups is likely to vary depending on the level of uncommitted transactions etc.. in the logs.

Hope some of this is helpful!

0
 
lvnealAuthor Commented:
Thanks for the info. The query is still running - its now been 2hours 10minutes.

The query I'm using to perform this differential backup is:
BACKUP DATABASE <db name>
TO tapedump1
WITH DIFFERENTIAL
GO

Is there any reason it should be taking this long? Also, Does the BACKUP DATABASE command also include backing up of the transaction log or is that something to do separately?

Thanks,
0
 
lvnealAuthor Commented:
If you backup the database, do you need to backup the transaction log also? Specifically if you do a differential backup?

Also, there was an issue with the tape which is why the query was taking so long - all sorted now.

Thanks,
0
 
grayeConnect With a Mentor Commented:
Yes, you need to perform both a Database backup and a Log backup.   The requirement for log backups has nothing to do with the type  of database backup (full or diff)
If you'd like a bit more background on the subject (particularly log maintenance), I'd recommend the following article: http://home.hot.rr.com/graye/Articles/SQL_LogMaintenance.htm
0
All Courses

From novice to tech pro — start learning today.