Solved

SQL Database Backup - Differential / Incremental?

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 35
Stored Proc - Rewrite 42 61
Select single row of data for each ID in Select Statement 7 28
Setting variables in a stored procedure 5 24
I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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