Post reply

Trouble with Restore The backup set holds a backup of a database other than the existing database.

  • July 1, 2016 at 3:15 am

    #319715

    RESTORE DATABASE PrismDataBillingTest

    FROM DISK = 'G:\MSSQL11.MSSQLSERVER\MSSQL\Backup\PrismData\PrismData_backup_2016_06_26_130000_9108635.bak'

    WITH MOVE 'PrismData_Data' TO 'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBillingTest_Data.mdf',

    MOVE 'PrismData_FG2_0'TO 'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBilling_Indices_0.ndf',

    MOVE 'PrismData_FG2_1' TO' D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBillingTest_Indices_0.ndf',

    MOVE 'PrismData_FG1_0' TO 'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBillingTest_FG1_0.ndf',

    MOVE 'PrismData_FG1_1' TO 'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismData_FG1_1.ndf',

    MOVE 'PrismData_Log' TO 'F:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBillingTest_Log.ldf'

    Msg 3154, Level 16, State 4, Line 2

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

    Msg 3013, Level 16, State 1, Line 2

    RESTORE DATABASE is terminating abnormally.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 1, 2016 at 3:46 am

    #1888028

    You're attempting to restore PrismDataBillingTest from a backup of a database with a different name. Use RESTORE HEADERONLY to find out what database(s) are in your backup file. Use the REPLACE option in your RESTORE statement if you want to overwrite your existing PrismDataBillingTest database with the database from the backup file.

    John

  • July 1, 2016 at 3:50 am

    #1888030

    RESTORE FILELISTONLY

    FROM DISK = 'G:\MSSQL11.MSSQLSERVER\MSSQL\Backup\PrismData\PrismData_backup_2016_07_01_051239_6046267.bak'

    GO

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 1, 2016 at 3:51 am

    #1888031

    Thanks John could you kindly provide me with the syntax?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 1, 2016 at 4:11 am

    #1888034

    John Mitchell-245523 (7/1/2016)

    You're attempting to restore PrismDataBillingTest from a backup of a database with a different name. Use RESTORE HEADERONLY to find out what database(s) are in your backup file. Use the REPLACE option in your RESTORE statement if you want to overwrite your existing PrismDataBillingTest database with the database from the backup file.

    John

    How do I do that?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 1, 2016 at 4:18 am

    #1888036

    As John said, add the REPLACE option to your restore statement.

    See https://msdn.microsoft.com/en-us/library/ms186858.aspx for the options and syntax for a RESTORE

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 1, 2016 at 4:22 am

    #1888037

    Welsh Corgi (7/1/2016)

    Thanks John could you kindly provide me with the syntax?

    ermmm.... add "WITH REPLACE" to your command, as documented in the RESTORE documentation?

    Check the impact of WITH REPLACE if you're not sure what you're doing.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • July 1, 2016 at 4:46 am

    #1888041

    GilaMonster (7/1/2016)

    As John said, add the REPLACE option to your restore statement.

    See https://msdn.microsoft.com/en-us/library/ms186858.aspx for the options and syntax for a RESTORE

    ok, I'm tired could you please help me with the syntax please?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 1, 2016 at 4:55 am

    #1888044

    John Mitchell-245523 (7/1/2016)

    You're attempting to restore PrismDataBillingTest from a backup of a database with a different name. Use RESTORE HEADERONLY to find out what database(s) are in your backup file. Use the REPLACE option in your RESTORE statement if you want to overwrite your existing PrismDataBillingTest database with the database from the backup file.

    John

    Could you please provide me with the syntax?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 1, 2016 at 5:39 am

    #1888054

    could you please provide me with the syntax?

    You've got a big WITH clause in your RESTORE statement which contains lots of options to move files around. Just add another option to that clause that simply says REPLACE

    You might want to put a comma in there to separate it from the previous and/or next option in the list.

    I'm not saying whereabouts in the list of WITH options it should go - I think that's down to your own preference.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • July 1, 2016 at 5:41 am

    #1888056

    Welsh Corgi (7/1/2016)

    John Mitchell-245523 (7/1/2016)

    You're attempting to restore PrismDataBillingTest from a backup of a database with a different name. Use RESTORE HEADERONLY to find out what database(s) are in your backup file. Use the REPLACE option in your RESTORE statement if you want to overwrite your existing PrismDataBillingTest database with the database from the backup file.

    John

    Could you please provide me with the syntax?

    The syntax is described on the page that both Gail and Thomas provided a link to - the restore statement.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • July 1, 2016 at 5:58 am

    #1888062

    ok, thanks I'm not feeling that well. I thought you might be aqble to help me out.

    Have a nice day.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 1, 2016 at 5:59 am

    #1888063

    ThomasRushton (7/1/2016)

    could you please provide me with the syntax?

    You've got a big WITH clause in your RESTORE statement which contains lots of options to move files around. Just add another option to that clause that simply says REPLACE

    You might want to put a comma in there to separate it from the previous and/or next option in the list.

    I'm not saying whereabouts in the list of WITH options it should go - I think that's down to your own preference.

    Could you please try and compile he syntax?

    Thank you.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 1, 2016 at 6:05 am

    #1888066

    RESTORE DATABASE PrismDataBillingTest

    FROM DISK = 'G:\MSSQL11.MSSQLSERVER\MSSQL\Backup\PrismData\PrismData_backup_2016_06_26_130000_9108635.bak'

    WITH REPLACE,

    MOVE 'PrismData_Data' TO 'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBillingTest_Data.mdf',

    MOVE' PrismData_FG2_0' TO 'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBilling_Indices_0.ndf',

    MOVE 'PrismData_FG2_1' TO' D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBillingTest_Indices_0.ndf',

    MOVE 'PrismData_FG1_0' TO 'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBillingTest_FG1_0.ndf',

    MOVE 'PrismData_FG1_1' TO 'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismData_FG1_1.ndf',

    MOVE 'PrismData_Log' TO 'F:\MSSQL11.MSSQLSERVER\MSSQL\DATA\PrismDataBillingTest_Log.ldf'

    Msg 3234, Level 16, State 2, Line 2

    Logical file ' PrismData_FG2_0' is not part of database 'PrismDataBillingTest'. Use RESTORE FILELISTONLY to list the logical file names.

    Msg 3013, Level 16, State 1, Line 2

    RESTORE DATABASE is terminating abnormally.

    What do I need to do?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 1, 2016 at 6:08 am

    #1888067

    Welsh Corgi (7/1/2016)

    GilaMonster (7/1/2016)

    As John said, add the REPLACE option to your restore statement.

    See https://msdn.microsoft.com/en-us/library/ms186858.aspx for the options and syntax for a RESTORE

    ok, I'm tired could you please help me with the syntax please?

    I'm tired, and busy, and probably working all weekend, and the page I linked to has about 8 code examples as well as a detailed syntax diagram. Plus Thomas just explained exactly what needs to be added and where.

    Unless you've never seen a RESTORE statement before, that's more than enough information for you to make that single word addition yourself.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 16 total)

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