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

Answer 1

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

 

Answer 2

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

Answer 3

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

Answer 4

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

 

Answer 5

Hi,

Make use of copy database wizard, simple to use!

Answer 6

Hi,

 

Refer the below link

How to use Copy Database Wizard

Answer 7

Thanks Deepak,

My error was solved by the query,

Regards,

-Ketan