Solved

SQL Database Backup - Differential / Incremental?

Posted on 2008-10-17
5
710 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
[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
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

Industry Leaders: 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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

717 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