August 20, 2012 at 8:12 am
I restore a Database from a Complete Backups:
RESTORE DATABASE BASSISControlCYP
FROM DISK = 'H:\Backups\Complete\BASSISControlCYP_backup_201208170809.bak'
WITH REPLACE,
NORECOVERY,
MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCYP.mdf',
MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCYP.ldf'
Processed 640 pages for database 'BASSISControlCYP', file 'BASSISControl' on file 1.
Processed 3 pages for database 'BASSISControlCYP', file 'BASSISControl_log' on file 1.
RESTORE DATABASE successfully processed 643 pages in 0.165 seconds (30.409 MB/sec)
Then I apply a differential backup without any issues:
RESTORE DATABASE BASSISControlCYP
FROM DISK = 'H:\Backups\Differential\BASSISControlCYP_backup_201208200745.bak'
WITH REPLACE,
NORECOVERY,
MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCYP.mdf',
MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCYP.ldf'
Processed 104 pages for database 'BASSISControlCYP', file 'BASSISControl' on file 1.
Processed 3 pages for database 'BASSISControlCYP', file 'BASSISControl_log' on file 1.
RESTORE DATABASE successfully processed 107 pages in 0.080 seconds (10.375 MB/sec).
Then I restore an almost Identiacal Database from a Complete Backup:
RESTORE DATABASE BASSISControlCTL
FROM DISK = 'H:\Backups\Complete\BASSISControlCTL_backup_201208170808.bak'
WITH REPLACE,
NORECOVERY,
MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCTL.mdf',
MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCTL.ldf'
Processed 760 pages for database 'BASSISControlCTL', file 'BASSISControl' on file 1.
Processed 3 pages for database 'BASSISControlCTL', file 'BASSISControl_log' on file 1.
RESTORE DATABASE successfully processed 763 pages in 0.124 seconds (48.024 MB/sec).
The I try and apply the differential but I get an error:
RESTORE DATABASE BASSISControlCTL
FROM DISK = 'H:\Backups\Differential\BASSISControlCTL_backup_201208200959.bak'
WITH REPLACE,
NORECOVERY,
MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCTL.mdf',
MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCTL.ldf'
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I checked the backup history to see if the chain was broken and that is not the case.
I used the following to do so:
SELECT
TOP 100
s.database_name,
CASE s.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO
I don't get it. I have done this before and no issues?
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2012 at 8:24 am
I just tried restoing two more database from differntial backups, the first worked the second did not.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2012 at 8:28 am
The diff backups in question are not "copy only" backups are they?
Is_copy_only from msdb.dbo.backupset
August 20, 2012 at 8:32 am
anthony.green (8/20/2012)
The diff backups in question are not "copy only" backups are they?Is_copy_only from msdb.dbo.backupset
They are not copy only they are full and differential backups.
What do you mean by Is_copy_only from msdb.dbo.backupset?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2012 at 8:35 am
The column Is_Copy_Only is backupset.
But forget that comment, Diff backups cannot be made in a copy_only state
http://msdn.microsoft.com/en-us/library/ms191495.aspx
Your full backups though could be created as copy_only and therefore will not allow the diff to be restored.
August 20, 2012 at 8:38 am
There are only two reasons I can think of for this:
(1) Another full backup was taken between the first full backup and the differential
(2) The differential backup is actually a backup of a different database.
What were the results from the query you ran?
John
August 20, 2012 at 8:57 am
BASSISControlCTLDifferential0 Seconds2012-08-20 09:59:03.000
BASSISControlCTLDifferential0 Seconds2012-08-20 07:46:09.000
BASSISControlCTLFull0 Seconds2012-08-17 08:08:55.000
I tried it with the first differential backup. Then I performed another differential backup and tried applying the second differential.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2012 at 10:36 am
Maybe I misunderstood, you're trying to apply the same differential that is matched to the first backup to a second, different backup? You can't do that. Differentials are tied directly to the last, full, backup. You can't restore a differential to any other full backup other than the last full one prior to the differential being run.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2012 at 10:46 am
Grant Fritchey (8/20/2012)
Maybe I misunderstood, you're trying to apply the same differential that is matched to the first backup to a second, different backup? You can't do that. Differentials are tied directly to the last, full, backup. You can't restore a differential to any other full backup other than the last full one prior to the differential being run.
Grant,
There was only one full backup for each of the Databases.
I restored the complete backup then I attempted to restore the first differential and it failed. I performed a second differential and it failed as well. Did you notice the backup history that you asked for?
I did this to a total of six Databases, 3 failed 3 sucessfull.
I only performed the 2nd differential on the 1st Database that failed.
I have 1 full backup then I have one differential for each of the other 6 databases.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2012 at 10:59 am
I'm backing up from SQL Server 2005 32bit and I'm restoring to SQL Server 2008 R2 64 bit.
I have not attempted to restore all 24 database but of the 6 I restored it fails every other Database same scripting methodology.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2012 at 11:14 am
The error you're getting, "This differential backup cannot be restored because the database has not been restored to the correct earlier state.", is usually indicative that the full backup and the differential are out of sync. It's a common error. I don't see precisely what's happening on your end, but I'm pretty sure, based on the error, that something is amiss with the alignment of full to differential. Is it possible the backups where taken without using INIT and the correct FULL or differential are stacked inside the file or something along those lines?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2012 at 12:20 pm
here is the code from the Maintenance Plan.
I noticed that it is set to NOINIT and I do see an option where you can set that.
I'm not sure about the Full Backup logic?
BACKUP DATABASE [BASSISControlCYP] TO DISK = N'D:\BACKUP\BASSISControlCYP_backup_201208201341.bak' WITH NOFORMAT, NOINIT, NAME = N'BASSISControlCYP_backup_20120820134115', SKIP, REWIND, NOUNLOAD, STATS = 10
I had created these jobs as well as the scripts back in January.
At that time the Server was going to move to another Data Center and it was going from SQL Server 2005 to SQL Server 2005.
I had all of the Databases restored and I was able to apply the differentials to all 24 Databases.
At first I encontered the same error and I found out that a Vendor was using a 3rd Party Tool to perform backups even though I had told them to stop all backups.
They stopped the backups, I ran the query that I sent you and I was good to go.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2012 at 12:23 pm
I just did a complete backup followed by a Differential Backup and restored both without issue.
It is almost as if sometning got backed up by that outside vendor with a 3rd party tool since my last backup but it is not showing in my query.
Back in January there were told to turn off the backups and they turned off one set but forgot to turn off another.
They were told to turn them back on when the Server move was postponed.
I may have used a different query last time.
I suspect that there may be something wrong with my query?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2012 at 12:31 pm
Since it's backing up with NOINIT, it's likely putting one backup on top of another. You may not be seeing the right backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2012 at 12:44 pm
I found this Script. I had two backups at 8:08:22 and 8:08:55.
The other script return one at 8:08 AM.
I was backing up a lot of Databases developing SSIS Packages and Admin Support all at the same time and hopefully this was just a case of unintentional operator error.
Thank you for the help.
USE MSDB
GO
SELECT
bs.server_name AS Server, -- Server name
bs.database_name AS DatabaseName , -- Database name
CASE bs.compatibility_level
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005 '
WHEN 100 THEN 'SQL Server 2008'
WHEN 110 THEN 'SQL Server 2011'
END AS CompatibilityLevel , -- Return backup compatibility level
recovery_model AS Recoverymodel , -- Database recovery model
CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'P'
WHEN 'Q' THEN 'Differential partial'
END AS BackupType, -- Type of database baclup
bs.backup_start_date AS BackupstartDate, -- Backup start date
bs.backup_finish_date AS BackupFinishDate, -- Backup finish date
bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
CASE device_type
WHEN 2 THEN 'Disk - Temporary'
WHEN 102 THEN 'Disk - Permanent'
WHEN 5 THEN 'Tape - Temporary'
WHEN 105 THEN 'Tape - Temporary'
else 'Other Device'
END AS DeviceType, -- Device type
bs.backup_size AS [BackupSize(In bytes)]
--, -- Normal backup size (In bytes)
--compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
WHERE bs.database_name = 'BASSISControlCTL'
ORDER BY bs.backup_start_date DESC
GO
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply