Solved

SQL Database Backup - Differential / Incremental?

Posted on 2008-10-17
5
694 Views
Last Modified: 2012-05-05
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,
0
Comment
Question by:lvneal
  • 2
  • 2
5 Comments
 

Assisted Solution

by:pap-deblanc
pap-deblanc earned 100 total points
ID: 22739221
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
 

Author Comment

by:lvneal
ID: 22739660
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
 
LVL 41

Accepted Solution

by:
graye earned 300 total points
ID: 22740536
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
 

Author Comment

by:lvneal
ID: 22740826
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
 
LVL 41

Assisted Solution

by:graye
graye earned 300 total points
ID: 22744690
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

708 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

15 Experts available now in Live!

Get 1:1 Help Now