Restore problem - Normal SQL bakcup in between Redgate's ba

Khanderkar

I am evaluating the Backup Pro software.

I took first Full backup from Redgate & updated my test table
2nd differential backup from Redgate & updated my test table
3rd differential backup from Redgate & updated my test table
Full backup from SQL Studio & updated my test table
4th differential backup from Redgate & updated my test table
5th differential backup from Redgate & updated my test table


While restore I am restoring the full backup of Redgate with Restore with norecovery
Then I have restored the 3rd differential backup from Redgate with recovery which worked.

In the other Test I :
Restored the full backup of Redgate with Restore with norecovery
Then I have restored the 5th differential backup from Redgate with recovery which did not worked.

My conclusion here is I can restore the DBs before the full backup of SQL studio (Regular)

Is there any way out for this …

I know if we start using Red gate We should not use regular Backup but what if someone takes the backup in my absence with regular backup tool & I continue using Redgate ?


Regards
Kedar Khanderkar
India

petey

That's the way backups work in SQL Server i.e. a differential backup is always based off the last full backup taken, regardless of the tool used to make the backup.

If you run into problems restoring a differential backups, you can always check the SQL Server backup history tables (msdb..backupset, msdb..backupmediafamily etc) for the last full backup made prior to your differential backup.

I know if we start using Red gate We should not use regular Backup but what if someone takes the backup in my absence with regular backup tool & I continue using Redgate ?

You are free to continue using regular backups, as there might be situations where a DBA is not around and its critical that a backup needs to be made by users who are not accustomed to using SQL Backup. The backup history tables contain everything you need to trace the sequence of backup files you need to restore.

On another note, if you need to make a regular full backup for whatever reason but don't want to break your SQL Backup chain of backups, you can use the COPY_ONLY option for your regular backup. Subsequent differential backups will then ignore this COPY_ONLY backup while identifying pages to back up.