question

srivivek avatar image
srivivek asked

differential backup

hi i want to take differential backup on all my (user) databases.if there is no full backup taken before on any of the database, i need to take a full backup and then take a differential backup on it. can anyone please give the script for the above issue.

differential-backup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered

The problem is, IMHO, not if a full backup was ever taken, but if a full backup EXISTS, and can be found and restored succesfully.

Otherwise your DIFFERENTIAL backup is useless.

But here is how to see the last date a full Database backup was taken:

SELECT 
T1.Name as DatabaseName, 
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2 
ON T2.database_name = T1.name AND TYPE='D'
GROUP BY T1.Name
ORDER BY T1.Name

Copied from mdsn

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

I agree with Henrik and Grant. Make sure your full backups are available, not corrupted, and relatively recent (exact definition of relatively recent depends on a number of factors, primarily how fast your data changes) before the thought of differential backups even comes to mind.

With that said, let me point out that some 3rd party tools make this process much simpler than doing it with just SSMS and T-SQL. I personally prefer Red Gate SQL Backup, but several other companies also have good offerings. These tools generally give you an easy to use graph of what databases have been backed up when and how and will write the scripts to automate future backups for you. I cannot speak for the others but Red Gate also has nice features like compression and encryption.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered

Hi Srivivek,

I do agree with Henrik. However you should run the full backup your self before you start the differential backup. So that will be save to start your database backup in case you have full backup already and that might not a good full backup.

hope it helps.

Leo

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

I'm with Leo, taking differential backups makes no sense unless you already have the full backup in place. The differential backups are simply the changes made since the last full backup. One doesn't exist without the other. It sounds to me like you don't have automated backups running on the system. I hope it's not a production environment. If it is, I'd concentrate on first getting the full backups running before you start worrying about differentials.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

srivivek avatar image
srivivek answered

Hi all thanks for your answers....

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.