Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


moving a database with differentials


moving a database with differentials

Author
Message
jjturner
jjturner
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 509
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.

JasonClark
JasonClark
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 787
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
Beatrix Kiddo
Beatrix Kiddo
SSC Eights!
SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)

Group: General Forum Members
Points: 940 Visits: 3515
Yes, you have to restore the full backup first or you won't be able to restore that differential.
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7361 Visits: 15079
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
jjturner
jjturner
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 509
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.

anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
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
When a question, really isn't a question - Jeff Smith
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


John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7361 Visits: 15079
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
JasonClark
JasonClark
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 787
Please check your differential backup databases and log file before recovery that they are created properly or not.
jjturner
jjturner
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 509
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... Alien
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???
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7361 Visits: 15079
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search