Solved

SQL Database Backup - Differential / Incremental?

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS  - Parameters with comma 10 40
Sub form showing data is being saved but cannot be displayed.. 31 68
What is GIS method of Geometry data type? 6 33
invoke-sqlcmd help 5 33
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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