Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

moving a database with differentials Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2014 10:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:46 AM
Points: 254, Visits: 330
Is it possible to RESTORE WITH MOVE and subsequently RESTORE the remaining backup chain (DIFF's and LOG's) to the new location?

I'm getting this error when I try to RESTORE the DIFF after RESTORE WITH MOVE on the FULL:

Msg 3136, Level 16, State 1, Line 2
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Post #1599840
Posted Thursday, August 7, 2014 12:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 4:20 AM
Points: 124, Visits: 175
Make sure your database not in use while taking backups. Please follow following steps sequentially:

At first restore the full backup

Restore Database file from DISK ='d: \file.bak' With NoRecovery

Then restore the differential backup file from DISK ='d: \file_differential.bak' With Recovery
Post #1600476
Posted Thursday, August 7, 2014 2:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:24 AM
Points: 498, Visits: 1,846
Yes, you have to restore the full backup first or you won't be able to restore that differential.
Post #1600498
Posted Thursday, August 7, 2014 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 5,430, Visits: 10,099
jjturner (8/5/2014)
Is it possible to RESTORE WITH MOVE and subsequently RESTORE the remaining backup chain (DIFF's and LOG's) to the new location?

I'm getting this error when I try to RESTORE the DIFF after RESTORE WITH MOVE on the FULL:

Msg 3136, Level 16, State 1, Line 2
This differential backup cannot be restored because the database has not been restored to the correct earlier state.

From what you say, it sounds as if you didn't use the WITH NORECOVERY clause in your original RESTORE statement. Please post your RESTORE statements if that isn't the case.

You don't need to make sure the database is not in use when backing up - SQL Server handles open transactions during recovery.

John
Post #1600503
Posted Thursday, August 7, 2014 5:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:46 AM
Points: 254, Visits: 330
use master;
go
restore database new_loc_testdb
from disk = '\\backup_dir\full\prod_full_20140803_233000.bak'
with
--replace
checksum
,move 'prod01' to 'e:\temp_restore\data01.mdf'
,move 'prod02' to 'e:\temp_restore\data02.ndf'
,move 'prod03' to 'e:\temp_restore\data03.ndf'
,move 'prod04' to 'e:\temp_restore\data04.ndf'
,move 'prod_log' to 'e:\temp_restore\testlog.ldf'
,norecovery
go

restore database new_loc_testdb
from disk = '\\backup_dir\diff\prod_diff_20140806_233000.bak'
with
checksum
,move 'prod01' to 'e:\temp_restore\data01.mdf'
,move 'prod02' to 'e:\temp_restore\data02.ndf'
,move 'prod03' to 'e:\temp_restore\data03.ndf'
,move 'prod04' to 'e:\temp_restore\data04.ndf'
,move 'prod_log' to 'e:\temp_restore\testlog.ldf'
,recovery;

Processed 177488 pages for database 'new_loc_testdb', file 'prod01' on file 1.
Processed 39448 pages for database 'new_loc_testdb', file 'prod02' on file 1.
Processed 41664 pages for database 'new_loc_testdb', file 'prod03' on file 1.
Processed 26144 pages for database 'new_loc_testdb', file 'prod04' on file 1.
Processed 3 pages for database 'new_loc_testdb', file 'prod_log' on file 1.
RESTORE DATABASE successfully processed 284747 pages in 13.255 seconds (167.829 MB/sec).
Msg 3136, Level 16, State 1, Line 13
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 13
RESTORE DATABASE is terminating abnormally.
Post #1600550
Posted Thursday, August 7, 2014 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
The full backup isn't a copy only backup is it?

Can you check the server where the backup was done from, in its msdb.dbo.backupset there is a is_copy_only column, if its 1 then that might be a reason for the error




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1600570
Posted Thursday, August 7, 2014 5:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 5,430, Visits: 10,099
Yes, or maybe another full backup was taking between the two backups you're working from? Again, check in msdb.dbo.backupset.

By the way, you don't need the MOVE clauses when you're restoring a differential.

John
Post #1600572
Posted Thursday, August 7, 2014 7:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 4:20 AM
Points: 124, Visits: 175
Please check your differential backup databases and log file before recovery that they are created properly or not.
Post #1600603
Posted Thursday, August 7, 2014 7:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:46 AM
Points: 254, Visits: 330
Thanks folks for looking into this for me -

I'm using Ola Hallengren's backup scripts and the @CopyOnly switch has not been altered from its default of 'N':
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\backup_dir', @BackupType = 'FULL', @Verify = 'Y', @Compress = 'Y', @CleanupTime = 337, @CheckSum = 'Y', @LogToTable = 'Y'" -b

And 20140803 was definitely the last FULL backup on this database taken via that job.

But speaking of intervening FULL backups...
I do see something odd with msdb..backupset -
I have a unique service account running for my SQL Server services, but NT AUTHORITY\SYSTEM is still producing backups...
My backup script job takes a FULL once per week, but some process using NT AUTHORITY\SYSTEM is taking a FULL backup every night... where is this coming from and where are those backups going???
Post #1600605
Posted Thursday, August 7, 2014 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 5,430, Visits: 10,099
That'll be the SQL Server VSS Writer service. You should switch it off if you have a complete database backup regime in place (which, of course, you should).

John
Post #1600609
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse