April 2, 2012 at 1:43 am
Hi Everyone,
I have hit a bit of a bump with my backup and restore plan.
I'm using SQL2008 R2 and have created a Maintenance Plan that creates a Full Backup of my business critical database each morning at 7:00am. From 8:00am to 8:00pm I do a differential backup every hour on the hour. (so far so good)
From 8:10 am to 7:59pm I do a transaction log backup every 10 minutes.
Following that I do some other backups to prepare for offsite storage.
The plan is if I need to restore to a previous point in time I restore the full backup from 7:00am with replace and the norecover option selected, Then restore the most recent differential backup prior to the required recovery time with norecover selected, then restore every transactional log backup up to the time I want and the last one with recovery option ticked.
My problem is that I can't restore the differential backup. I receive the error message.
"Restore failed for server 'ServerName' (Microsoft.sqlserver.smoextended)
Additional Information:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (microsoft.sqlserver.smo)"
I have read various Microsoft articles so it appears I'm doing things the right way during the restore phase so it kind of says to me that my differential backups are stuffed?
I can restore the initial 7:00am backup then restore the 10 minutes transaction logs one after the other no problem but restoring to 7:00pm at night would be a nightmare.
If I open the maintenance plan and go into the step that does the differential backup I can find the generated code. I cut out all the other databases and only left the code for the DB I’m testing. As I said this is all generated from the wizard.
Any idea why I can’t restore the differential backups?
Thanks in Advance
David
EXECUTE master.dbo.xp_create_subdir N'\\buffalo\Backup\SQL2008\diff\Strata'
GO
BACKUP DATABASE [Strata] TO DISK = N'\\buffalo\Backup\SQL2008\diff\Strata\Strata_backup_2012_04_02_173543_8665080.bak' WITH DIFFERENTIAL , RETAINDAYS = 5, NOFORMAT, NOINIT, NAME = N'Strata_backup_2012_04_02_173543_8665080', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'Strata' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Strata' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Strata'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'\\buffalo\Backup\SQL2008\diff\Strata\Strata_backup_2012_04_02_173543_8665080.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
April 2, 2012 at 3:39 am
Someone took an ad-hoc full backup between the scheduled 7am backup and the diff you are trying to restore.
Differential backups are based on the last full backup taken, so if someone took a full backup between then and the time that the diff backup was taken.
Try earlier diff backups, one will likely succeed then you can restore the log backups since that diff backup.
Once done, check the MSDB backup tables and the error log and see if you can find out when the full backup was taken and by who. Teach that person to take ad-hoc full backups with the copy_only option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2012 at 10:37 pm
Hi Gail,
Thanks for the speedy response. I have done the required restore to the database by other means so the pressure is off and I can now investigate why I can't restore from the differentials. I have read other posts that mention the problem with ad hock full backups and I do understand this process. I'm the only person making backups on this server. Any add hock backups I create I do I turn on the Copy Only tick box in the GUI.
Sorry this is a bit of a novel. Hopefully some if it will point to where my problem is.
The full backup was taken at 7:00am and the first differential backup was taken at 8:00am and this couldn't be restored. So the only chance (I can think of) of a full backup being taken between those times would be from the scheduler and there don't appear to be any.
I have taken your advice and investigated the msdb.dbo.backupset table.
The results were not what I was expecting. It seems like the Backup Type is incorrect. (See Below)
I would expect the initial backup to be Full (assuming this would be F)
The hourly should be D and the Log backups should be L
I assume the I is for Incremental and I wasn't expecting that.
I then revisited my Maintenance Plan
The 7:00am Backup Type is definitely "Full" in the GUI
The generated TSQL for this backup
BACKUP DATABASE [Strata] TO DISK = N'\\buffalo\Backup\SQL2008\FULL\Strata\Strata_backup_2012_04_03_141521_3949080.bak' WITH RETAINDAYS = 10, NOFORMAT, NOINIT, NAME = N'Strata_backup_2012_04_03_141521_3949080', SKIP, REWIND, NOUNLOAD, STATS = 10
The 8:00am Backup Type is definitely "Differential" in the GUI
The generated TSQL for this backup
BACKUP DATABASE [Strata] TO DISK = N'\\buffalo\Backup\SQL2008\diff\Strata\Strata_backup_2012_04_03_141945_4519224.bak' WITH DIFFERENTIAL , RETAINDAYS = 5, NOFORMAT, NOINIT, NAME = N'Strata_backup_2012_04_03_141945_4519224', SKIP, REWIND, NOUNLOAD, STATS = 10
The 8:10am Backup Type is definitely "Transaction Log" in the GUI
The generated TSQL for this backup
BACKUP LOG [Strata] TO DISK = N'\\buffalo\Backup\SQL2008\diff\Strata\Strata_backup_2012_04_03_142311_2567000.trn' WITH RETAINDAYS = 6, NOFORMAT, NOINIT, NAME = N'Strata_backup_2012_04_03_142311_2567000', SKIP, REWIND, NOUNLOAD, STATS = 10
Thanks in Advance
David
Extract from msdb.dbo.backupset table
backup_set_idbackup_start_datebackup_finish_datetypebackup_sizeflagsbegins_log_chainis_copy_only
787702012-04-02 07:03:53.0002012-04-02 07:06:45.000D239397785651200
787992012-04-02 08:00:09.0002012-04-02 08:00:09.000I1127424256000
788022012-04-02 08:10:02.0002012-04-02 08:10:02.000L27340851200
788182012-04-02 08:20:04.0002012-04-02 08:20:04.000L7680051200
788192012-04-02 08:30:02.0002012-04-02 08:30:02.000L1024051200
788202012-04-02 08:40:02.0002012-04-02 08:40:02.000L1024051200
788212012-04-02 08:50:02.0002012-04-02 08:50:02.000L1024051200
788222012-04-02 09:00:02.0002012-04-02 09:00:02.000L14336051200
788342012-04-02 09:00:09.0002012-04-02 09:00:09.000I1127424256000
788372012-04-02 09:10:02.0002012-04-02 09:10:02.000L7680051200
788532012-04-02 09:20:02.0002012-04-02 09:20:02.000L7680051200
788542012-04-02 09:30:02.0002012-04-02 09:30:02.000L7680051200
788552012-04-02 09:40:02.0002012-04-02 09:40:02.000L14233651200
788562012-04-02 09:50:02.0002012-04-02 09:50:02.000L7680051200
788572012-04-02 10:00:03.0002012-04-02 10:00:03.000L14336051200
788692012-04-02 10:00:10.0002012-04-02 10:00:11.000I10564608256000
April 3, 2012 at 1:23 am
dpollard 96305 (4/2/2012)
The results were not what I was expecting. It seems like the Backup Type is incorrect. (See Below)I would expect the initial backup to be Full (assuming this would be F)
The hourly should be D and the Log backups should be L
I assume the I is for Incremental and I wasn't expecting that.
As per Books Online:
type
char(1)
Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.
Absolutely sure you were restoring the right backups?
Do you have anything like backupexec backing up the SQL database?
Is there anything in the SQL error log that indicates any odd backup activity between the time of the full and the time of the diff that couldn't be restored
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 4:54 am
This was removed by the editor as SPAM
April 3, 2012 at 5:38 pm
Hi Rook,
I am following the sequence you recommend. See my first post.
"The plan is if I need to restore to a previous point in time I restore the full backup from 7:00am with replace and the norecover option selected, Then restore the most recent differential backup prior to the required recovery time with norecover selected, then restore every transactional log backup up to the time I want and the last one with recovery option ticked."
Thanks for your input 🙂
Hi Gail,
I used to use Backupexec but we stopped using it about 4 months ago. It is completely disabled now and all the services are stopped.
I now use a volume level backup from Storage Craft called Shadow protect. Apart from the maze of configuration options and a few licencing traps it is quite a good product. However my understanding is that it is unaware of SQLServer and it backs up the volume and not the files.
(Dave runs off to Google)
Hmm, this article even though a little old may hold a clue http://support.storagecraft.com/sites/support.storagecraft.com/files/old/00000174%20Configuration%20of%20SQL%20VSS%20writer%20to%20avoid%20SQL-VDI%20errors.pdf
When I look at the event logs I see an entry like this for each database yet I see nothing specific in the StorageCraft screens about database level backups. I have a support contract with StorageCraft. I’ll follow it up with them and post back here with the answer in case someone else comes across the same problem.
Event Type:Information
Event Source:MSSQL$SQLSTD2008
Event Category:Backup
Event ID:18264
Date:04/04/2012
Time:7:19:05 AM
User:NT AUTHORITY\SYSTEM
Computer:SQLSERVER
Description:
Database backed up. Database: Strata, creation date(time): 2011/01/24(14:01:43), pages dumped: 293145, first LSN: 82638:432:37, last LSN: 82638:448:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{645C6078-4FA8-48DD-9BA3-54644F75E957}6'}). This is an informational message only. No user action is required.
Thanks heaps for your help so far with this.
David
April 3, 2012 at 9:23 pm
I found the answer.
http://support.storagecraft.com/white-paper/article/64
ShaddowProtect even though it is marketed as a sector level backup it uses VSS and while backing up a volume that contains a SQL database it somehow effects the differental backups LSN numbers. I don't fully understand how VSS and MSQL works, it just comes under "microsoft magic"
StorageCraft's answer is to either use SQL backup or StorageCraft but not both.
Funny how they left that out of the sales pitch.
David
April 4, 2012 at 12:44 am
I was asleep when you posted the first comment about Shadow protect or I could have told you that. (not used that product, just from general experience)
The VSS-type backups reset the differential base, it's fine (well, usually) if you are just doing full and log backups on SQL, but as soon as you're doing differential and VSS-type backups you're asking for trouble.
VSS backups that backup SQL files have to interact with SQL or they risk useless backups (the data and log files aren't independent). You'll usually see IO freeze/thaw messages in the SQL error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 12:58 am
I didn't mention StorageCraft at first as I was led to believe it is a Disk Sector backup an unaware of SQL files.
I should know when sales people use techinical terms they are talking out their back side.
I couldn't figure out how it did this type of backup on a live database without getting a hotch potch of half baked transactions if I ever needed to do a restore.
I have notice the freeze thaw stuff in the event logs. It only lasts for about 1 second in total.
My manager is reluctant to put all our eggs in the one backup basket as the business sinks or swims on this db.
I'll leave the ShadowProtect backup running as it is and do full SQL backups hourly.
I'll continue with the transaction log backup every 10 minutes.
As you say it is just the differental that are broken.
The down side is the size and time required to create the full backups. Currently about 2 minutes and 2GB so not the end of the world.
Thanks again for the help
April 4, 2012 at 1:06 am
dpollard 96305 (4/4/2012)
I'll leave the ShadowProtect backup running as it is and do full SQL backups hourly.I'll continue with the transaction log backup every 10 minutes.
As you say it is just the differental that are broken.
My personal recommendation (from experience) is use one or the other, but not both. You're not adding protection with the second backup method, just confusing things. SQL backup to disk and let the VSS tool take a copy of the backup file, test a restore on another server, put the backup either to tape or to another server (and somewhere offsite to be safe too)
Do try a restore from full and log backups and make sure you can do that. I've seen one VSS backup tool (that will remain unnamed) that broke the log backup chain when it ran.
What's the RTO and RPO of this DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 1:25 am
There is nothing written about required recovery times are we are a relatively small business.
With the recent restore I lost about an hours worth of transactions which wasn't that big a deal but the idea of the 10minutes transaction log backups is that we should never loose more than 10 minutes.
It took me about 2 hours to figure out what was going on and then decide to recover from the StorageCraft backup.
This is nothing fancy just mount the backup file stop the sqlserver and plonk the mdf and ldf back over top of the existing files.
25 or so people become non productive while the database is off line. If I have a cronic hardware failure then my downtime is probably going to be longer while I rebuild a database server somewhere else and restore the db.
I currently do backups during the day to a different server on site then StorageCraft copies an image of the Volume changes offsite over night.
It all appeared to be working perfectly until we gave it the acid test.
I did test my Maintenance Plan by restoring right down to the transaction log level but that was before we changed from BackupExec to StoragCraft.
Any way Dinner time now 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply