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.
Answer by Henrik Staun Poulsen ·
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
Answer by TimothyAWiseman ·
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.
Answer by Leo ·
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.
Answer by Grant Fritchey ·
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.