none
the backup set holds a backup of a database other than the existing database

    Question

  • Hi all,

    I am facing an interesting problem.

    I have an application, that creates a database for it's own. Before, it used to create the database just with 

    CREATE DATABASE database_name 


     statement, and it used to have only one filegroup PRIMARY (two files, mdf and ldf). (MS SQL SERVER 2008 R2)

    Recently, we decided to add a new filegroup, to store photos, and wanted our application to be able to backup/restore the database with/without PHOTOS filegroup.

    So now, when I create a database through the application, it creates a database, and adds a filegroup and assigns a file to it.

    When it wants to restore a backup file from previous versions (with just one PRIMARY filegroup), it deletes the PHOTOS filegroup, and restores the old backup file, and once the restoration is done, adds this filegroup back. This works perfectly, well done.

    The problem occurs, when I try to restore the backup, which is made by the latest version.

    Lets say I create a database test1, and make a backup of it: test1.bak. And I create another database test2, and make a backup of it: test2.bak.
    If I restore test2 from test2.bak, it restores, but if I try to restore from test1.bak, it gives this error.

    RESTORE FILELISTONLY FROM DISK='C:\test1.bak' 

    and 

    RESTORE FILELISTONLY FROM DISK='C:\test2.bak' 


     give the same result except UniqueId fields.

    The following is the code that is uses for restoration:

    DECLARE 
    
    @DestDataBaseName varchar(255),
    
    @BackupFile varchar(255)
    
    
    
    SET @DestDataBaseName = 'test2'
    
    SET @BackupFile = 'C:\test.bak'
    
    
    
    RESTORE DATABASE @DestDataBaseName FROM DISK = @BackupFile
    
    WITH REPLACE,PARTIAL, 
    
    MOVE 'test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\test2.mdf', 
    
    MOVE 'PHOTOS' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\test2_PHOTOS.mdf', 
    
    MOVE 'test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\test2.ldf'
    
    

    Tried to restore through GUI, with "REPLACE EXISTING" option checked, doesn't work as well.
    It gives the same error. That "the backup set holds a backup of a database other than the existing database".

    Have been working on that for about a week, getting crazy.Any ideas what the hell the problem might be?

    Thanks in advance.


    Monday, July 25, 2011 6:53 AM

Answers

  • Hi,

    As error message mentioned, you could not restore a database from a backup file which is perform on another database. When you restore a database with partial backup file, make sure the backup is also performed in that database (that is [test7] in this case). For more information, see Partial Backups (http://msdn.microsoft.com/en-us/library/ms191539.aspx).


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by Stephanie Lv Friday, August 05, 2011 12:28 AM
    Monday, August 01, 2011 7:06 AM
    Moderator

All replies

  • Hi,

    If with replace option is not working then drop destination database and try to restore.

     


    Sivaprasad.L Together We can Achieve
    Monday, July 25, 2011 7:15 AM
  • Hi,

    You mean, delete the database, create again and restore?

    Monday, July 25, 2011 7:28 AM
  • That doesn't work.
    Monday, July 25, 2011 2:53 PM
  • Could you please elaborate this:

    The problem occurs, when I try to restore the backup, which is made by the latest version.

    Lets say I create a database test1, and make a backup of it: test1.bak. And I create another database test2, and make a backup of it: test2.bak.
    If I restore test2 from test2.bak, it restores, but if I try to restore from test1.bak, it gives this error.

     

    I am not able to understand the last part of your question, could you please explain in little more detail.

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Monday, July 25, 2011 4:07 PM
  • Hi Harsh Chawla, thank you for your responce.

    OK, let me explain it in more details.

    I have an application, which can create a database, consisting of 2 filegroups (PRIMARY and PHOTOS). Lets say I create a database: test1.

    And, I create a backup with this code:

    BACKUP DATABASE test1 FILEGROUP= 'PRIMARY' 
    TO DISK= 'C:\test1.bak'
    WITH FORMAT
    

    When I run "restore filelistonly" for this test1.bak, it says me that 'isPresent' for 'PHOTOS' filegroup is 0. That it doesn't exist in the backup.

    And then, to restore this test1.bak to test1, (without 'PHOTOS' filegroup), I run the following code:

    RESTORE DATABASE test1 FROM DISK = 'C:\test1.bak'
    WITH REPLACE, PARTIAL,
    MOVE 'test1' TO '[path]\test1.mdf', 
    MOVE 'test1_log' TO '[path]\test1.ldf'
    

    It restores well, sys.database_files says me that the state of 'PHOTOS' filegroup is 'RECOVERY_PENDING'. It's OK.

    Then I create a second database, test2, and the same way as above, create a new backup, test2.bak. Which does not include 'PHOTOS' filegroup.

    If I restore it to its native database, it restores. But the problem occurs when I want to restore the backup test2.bak to the database test1. Or, test1.bak to the database test2. It says that "the backup set holds a backup of a database other than the existing database".

    Assume that I have branches of a bank. And one of my branches sent me its databases backup, and I can not restore it to a database, to be able to explore the data in it.

    Thanks in advance.

    Tuesday, July 26, 2011 4:40 AM
  • Hello Friend,

     

    I tried to do the same using GUI by selecting the with replace option and I was able to do that. Here is the command which profiler trace captured for me while I was using GUI:

    RESTORE DATABASE [test7] FROM  DISK = N'C:\test2.bak' WITH  FILE = 1,  MOVE N'test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\test123.mdf',  MOVE N'test1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\test123.ndf',  MOVE N'test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\test_log123.LDF',  NOUNLOAD,  REPLACE,  STATS = 10

     

    Check this command and share the error message again, if it fails.


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Tuesday, July 26, 2011 8:30 AM
  • Hi, thanks for your attention.

    To make the code that you referred as much close as possible to my situation, I run the following code:

    create database test123
    alter database test123 add filegroup test1
    alter database test123
    add file (
    name = test1,
    filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test123.ndf'
    ) to filegroup test1
    backup database test123 filegroup= 'primary' 
    to disk= 'c:\test123.bak'
    with format
    
    create database test7
    alter database test7 add filegroup test1
    alter database test7
    add file (
    name = test1,
    filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test7.ndf'
    ) to filegroup test1
    
    RESTORE DATABASE [test7] FROM 
    DISK = N'C:\test123.bak' 
    WITH FILE = 1, 
    MOVE N'test123' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test7.mdf', 
    MOVE N'test1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test7.ndf', 
    MOVE N'test123_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test7_log.LDF', 
    NOUNLOAD, REPLACE, STATS = 10
    

    It creates a database test123, adds a file group and assigns a file to that filegroup (test1). And takes a backup of it, which includes just PRIMARY filegroup.

    And again, creates a new database, test7 and creates a filegroup and assigns a file. And then restores to test7 the backup test123.bak.

    Gives the same error.

    Basically, I want my application to look at backup, and restore all filegroups if exist, and only PRIMARY if test1 doesn't exist. And it's OK that the filegroup test1 remains offline after that all the time.

    Tuesday, July 26, 2011 10:18 AM
  • After doing some research and demo I found this :

     

    take the full backup of the source database and run the command:

     

    RESTORE


     DATABASE test7

     

     

    FROM DISK = 'c:\test12b3.bak'

     

     

    WITH FILE = 1 , replace

     

    then we can do filegroup level restore


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/

    Wednesday, July 27, 2011 12:18 PM
  • Yes, that I understand. In case of full backup, it works.

    So, there's no way to take a partial backup, and restore it somewhere else?

    Wednesday, July 27, 2011 1:13 PM
  • Hi,

    As error message mentioned, you could not restore a database from a backup file which is perform on another database. When you restore a database with partial backup file, make sure the backup is also performed in that database (that is [test7] in this case). For more information, see Partial Backups (http://msdn.microsoft.com/en-us/library/ms191539.aspx).


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by Stephanie Lv Friday, August 05, 2011 12:28 AM
    Monday, August 01, 2011 7:06 AM
    Moderator