none
Restoring Error 'Msg 3154, Level 16, State 4, Line 1'

    Question

  • Hi All,

     

        I have two sql instance 2005 what i want to do i want to copy one database from one instance and paste with second instance. sothat i created full back up from 1st instance and restoreing that .bak file with 2nd instance. following script i am running

     

    Msg 3154, Level 16, State 4, Line 1

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

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

     

    so what should be the code and how to restore database from .bak file

     

    give me good solution as soon as possible

     

    Thanks

    Friday, June 06, 2008 6:21 PM

Answers

  • If the database already exist in the server you can either use the With Replace option to overwrite it or else you can drop that database and restore it using the below command..

     (a) RESTORE FILELISTONLY FROM DISK='D:\YourBackupfilename.bak'
          GO
     (b) RESTORE DATABASE NewDatabaseName
          FROM DISK='D:\YourBackupfilename.bak'
          WITH MOVE 'LogicalName of the SourceDatafile' TO 'D:\NewDatabase.mdf',
          MOVE 'Logicalname of sourcelogfile' TO'D:\NewDatabase_log.ldf'

    Note : To get the logical file name  run the step (a) and copy past the data and log file name
    Change the script accordingly and run.

    - Deepak
    Sunday, June 08, 2008 9:26 AM

All replies

  • Use the option With Replace to overwrite an existing database as shown below,

    Code Snippet
    RESTORE DATABASE AdventureWorksDW FROM DISK = ‘C:\AdventureworksDW.bak’  WITH REPLACE

     

     

    - Deepak


     

    • Proposed as answer by Lima 83 Thursday, May 26, 2011 2:52 PM
    Friday, June 06, 2008 7:22 PM
  • when i am execute your statment. it gave me following err.

     

    Msg 3634, Level 16, State 1, Line 1

    The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf'.

    Msg 3156, Level 16, State 8, Line 1

    File 'AdventureWorksDW_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

    Msg 3119, Level 16, State 1, Line 1

    Problems were identified while planning for the RESTORE statement. Previous messages provide details.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

     

     

    what should be my syntax.please help

     

    Thanks

    Saturday, June 07, 2008 1:33 PM
  • If the database already exist in the server you can either use the With Replace option to overwrite it or else you can drop that database and restore it using the below command..

     (a) RESTORE FILELISTONLY FROM DISK='D:\YourBackupfilename.bak'
          GO
     (b) RESTORE DATABASE NewDatabaseName
          FROM DISK='D:\YourBackupfilename.bak'
          WITH MOVE 'LogicalName of the SourceDatafile' TO 'D:\NewDatabase.mdf',
          MOVE 'Logicalname of sourcelogfile' TO'D:\NewDatabase_log.ldf'

    Note : To get the logical file name  run the step (a) and copy past the data and log file name
    Change the script accordingly and run.

    - Deepak
    Sunday, June 08, 2008 9:26 AM
  • Looks like you've backed up the database from instance1 and are restoring on instance2. The restore on instance2 will try to use the same file locations as the database on instance1 - which will clearly fail. As Deepak says, you need to explicitly move the files to a different location.

     

    Thanks

     

    Monday, June 09, 2008 2:42 AM
  • Hi,

    Make use of copy database wizard, simple to use!
    Tuesday, June 10, 2008 1:22 PM
  • Hi,

     

    Refer the below link

    How to use Copy Database Wizard

    Wednesday, June 11, 2008 2:18 AM
  • Thanks Deepak,

    My error was solved by the query,

    Regards,

    -Ketan


    Saturday, July 05, 2008 12:58 AM