moving a database with differentials

  • 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:

    [font="Courier New"]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.[/font]

  • This was removed by the editor as SPAM

  • Yes, you have to restore the full backup first or you won't be able to restore that differential.

  • 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:

    [font="Courier New"]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.[/font]

    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

  • 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;

    [font="Courier New"]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.[/font]

  • 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

  • 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

  • This was removed by the editor as SPAM

  • 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???

  • 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

  • aha - that's sneaky!

    Thanks for pinning this down for me John!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply