none
restore 2005 database into 2008 error 3154

    Question

  • I am trying to restore a 2005 database to a 2008 database.  This is the code that was generated by Management studio:

    RESTORE

     

    DATABASE [PROD1] FILE = N'PROD_Data', FILE = N'Prod_user_data' FROM DISK = N'J:\MSSQL\Backup\Prodstart\PROD_db_201010262102.DMP' WITH FILE = 1, MOVE N'PROD_Data' TO N'G:\Program Files\Microsoft SQL Server\MSSQL$PROD\PROD1.mdf', MOVE N'Prod_user_data' TO N'I:\MSSQL\PROD\Data\Prod1_user_data.ndf', NOUNLOAD, REPLACE, STATS = 10

    This is a fresh install of a clustered SQL 2008 instance with all patches. I have tried using the same database name and a different name, neither works.  This is my error message:

    Msg 3154, Level 16, State 4, Line 1

    The backup set holds a backup of a database other than the existing 'PROD1' database.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Any ideas?

     

    Thursday, October 28, 2010 2:33 PM

Answers

  • Your syntax is to do FILE restores, normally used for recovering a damaged file.  There are limits and issues with this type of restore: http://msdn.microsoft.com/en-us/library/ms190710.aspx

    I see that you already had the REPLACE directive, but I guess that the FILE restore has an integrity problem with restoring a file with a different database name to a database.  (If that is the problem, I suppose that the message could have been more helpful.)

    However, if you simply remove the two FILE references at the start of your restore, then you will be doing a full database restore.  Since you are moving to a new server, this is doubtless what you actually intend to do.

    RESTORE DATABASE [PROD1] 
    /* FILE = N'PROD_Data', FILE = N'Prod_user_data' -- NOT NEEDED FOR FULL RESTORE */
    FROM DISK = N'J:\MSSQL\Backup\Prodstart\PROD_db_201010262102.DMP' WITH FILE = 1, 
    MOVE N'PROD_Data' TO N'G:\Program Files\Microsoft SQL Server\MSSQL$PROD\PROD1.mdf', 
    MOVE N'Prod_user_data' TO N'I:\MSSQL\PROD\Data\Prod1_user_data.ndf', 
    NOUNLOAD, REPLACE, STATS = 10
    

    REPLACE is valuable for overwriting a database, but if there is no PROD1 database on your new server, you should not need this option.  Also see the cautions about REPLACE at: 
    http://msdn.microsoft.com/en-us/library/ms191315.aspx

    Hope this helps,
    RLF

    Friday, October 29, 2010 3:49 PM

All replies

  • I am trying to restore a 2005 database to a 2008 database.  This is the code that was generated by Management studio:

    RESTORE

     

    DATABASE [PROD1] FILE = N'PROD_Data', FILE = N'Prod_user_data' FROM DISK = N'J:\MSSQL\Backup\Prodstart\PROD_db_201010262102.DMP' WITH FILE = 1, MOVE N'PROD_Data' TO N'G:\Program Files\Microsoft SQL Server\MSSQL$PROD\PROD1.mdf', MOVE N'Prod_user_data' TO N'I:\MSSQL\PROD\Data\Prod1_user_data.ndf', NOUNLOAD, REPLACE, STATS = 10

    This is a fresh install of a clustered SQL 2008 instance with all patches. I have tried using the same database name and a different name, neither works.  This is my error message:

    Msg 3154, Level 16, State 4, Line 1

    The backup set holds a backup of a database other than the existing 'PROD1' database.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Any ideas?

     

    Thursday, October 28, 2010 2:33 PM
  • <p>Please run below command and provide output</p>
    
    Restore headeronly FROM DISK = N'J:\MSSQL\Backup\Prodstart\PROD_db_201010262102.DMP' 
    go
    Restore filelistonly FROM DISK = N'J:\MSSQL\Backup\Prodstart\PROD_db_201010262102.DMP' 
    go
    

     


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Thursday, October 28, 2010 2:45 PM
    Moderator
  • Already replied on duplicate post
    http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/162eddf3-a6c5-47f6-9a2d-99851174f740
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Thursday, October 28, 2010 2:46 PM
    Moderator
  • BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BackupStartDate BackupFinishDate SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags BindingID RecoveryForkID Collation FamilyGUID HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID ForkPointLSN RecoveryModel DifferentialBaseLSN DifferentialBaseGUID BackupTypeDescription BackupSetGUID CompressedBackupSize

    ------------------------------------ --------------------

    NULL NULL 1 NULL 0 1 2 JCEXCHANGE\produser BUDDI\PROD PROD 611 2004-12-15 13:55:57.000 65743059456 1634789000001807300007 1634797000000527200001 1634791000000037100003 1633584000000665200256 2010-10-26 21:02:01.000 2010-10-26 21:33:19.000 0 0 1033 0 80 4608 9 0 3215 BUDDI 512 4F752855-E5DF-4855-8F7F-1812EBF331E6 EBC4C55D-52AD-403C-8706-9CFD4733CE5F Latin1_General_BIN 1C6E8EA4-C6A3-4508-9DD4-5960106DFE00 0 0 0 0 0 0 0 0 0 0 EBC4C55D-52AD-403C-8706-9CFD4733CE5F NULL FULL NULL NULL Database D8CF681A-7CFE-4FD1-B2DC-D4B248968C63 65743059456

    (1 row(s) affected)

    LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint

    --------------------------------------------------------------------------------------------------------------------------------

    PROD_Data G:\Program Files\Microsoft SQL Server\MSSQL$PROD\data\PROD_Data.MDF D PRIMARY 8485797888 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 7693336576 512 1 NULL 1633584000000665200256 3A36DC52-2A76-489B-A916-6CFE68E50A7D 0 1 NULL

    Prod_user_data I:\mssql\prod\data\Prod_user_Data.NDF D prod_data 59732262912 35184372080640 3 5000000005500001 0 00000000-0000-0000-0000-000000000000 0 0 58001063936 512 2 NULL 1633584000000665200256 3A36DC52-2A76-489B-A916-6CFE68E50A7D 0 1 NULL

    PROD_Log H:\MSSQL\prod\logs\PROD_Log.LDF L NULL 7668760576 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL

    (3 row(s) affected)

    Here is the results

    Thursday, October 28, 2010 2:53 PM
  • BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BackupStartDate BackupFinishDate SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags BindingID RecoveryForkID Collation FamilyGUID HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID ForkPointLSN RecoveryModel DifferentialBaseLSN DifferentialBaseGUID BackupTypeDescription BackupSetGUID CompressedBackupSize

    ------------------------------------ --------------------

    NULL NULL 1 NULL 0 1 2 JCEXCHANGE\produser BUDDI\PROD PROD 611 2004-12-15 13:55:57.000 65743059456 1634789000001807300007 1634797000000527200001 1634791000000037100003 1633584000000665200256 2010-10-26 21:02:01.000 2010-10-26 21:33:19.000 0 0 1033 0 80 4608 9 0 3215 BUDDI 512 4F752855-E5DF-4855-8F7F-1812EBF331E6 EBC4C55D-52AD-403C-8706-9CFD4733CE5F Latin1_General_BIN 1C6E8EA4-C6A3-4508-9DD4-5960106DFE00 0 0 0 0 0 0 0 0 0 0 EBC4C55D-52AD-403C-8706-9CFD4733CE5F NULL FULL NULL NULL Database D8CF681A-7CFE-4FD1-B2DC-D4B248968C63 65743059456

    (1 row(s) affected)

    LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint

    --------------------------------------------------------------------------------------------------------------------------------

    PROD_Data G:\Program Files\Microsoft SQL Server\MSSQL$PROD\data\PROD_Data.MDF D PRIMARY 8485797888 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 7693336576 512 1 NULL 1633584000000665200256 3A36DC52-2A76-489B-A916-6CFE68E50A7D 0 1 NULL

    Prod_user_data I:\mssql\prod\data\Prod_user_Data.NDF D prod_data 59732262912 35184372080640 3 5000000005500001 0 00000000-0000-0000-0000-000000000000 0 0 58001063936 512 2 NULL 1633584000000665200256 3A36DC52-2A76-489B-A916-6CFE68E50A7D 0 1 NULL

    PROD_Log H:\MSSQL\prod\logs\PROD_Log.LDF L NULL 7668760576 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL

    (3 row(s) affected)

    Here is the results

    Thursday, October 28, 2010 2:53 PM
  • Your syntax is to do FILE restores, normally used for recovering a damaged file.  There are limits and issues with this type of restore: http://msdn.microsoft.com/en-us/library/ms190710.aspx

    I see that you already had the REPLACE directive, but I guess that the FILE restore has an integrity problem with restoring a file with a different database name to a database.  (If that is the problem, I suppose that the message could have been more helpful.)

    However, if you simply remove the two FILE references at the start of your restore, then you will be doing a full database restore.  Since you are moving to a new server, this is doubtless what you actually intend to do.

    RESTORE DATABASE [PROD1] 
    /* FILE = N'PROD_Data', FILE = N'Prod_user_data' -- NOT NEEDED FOR FULL RESTORE */
    FROM DISK = N'J:\MSSQL\Backup\Prodstart\PROD_db_201010262102.DMP' WITH FILE = 1, 
    MOVE N'PROD_Data' TO N'G:\Program Files\Microsoft SQL Server\MSSQL$PROD\PROD1.mdf', 
    MOVE N'Prod_user_data' TO N'I:\MSSQL\PROD\Data\Prod1_user_data.ndf', 
    NOUNLOAD, REPLACE, STATS = 10
    

    REPLACE is valuable for overwriting a database, but if there is no PROD1 database on your new server, you should not need this option.  Also see the cautions about REPLACE at: 
    http://msdn.microsoft.com/en-us/library/ms191315.aspx

    Hope this helps,
    RLF

    Friday, October 29, 2010 3:49 PM