Search code examples
sql-serverrestore

How to restore my backup to a different database?


I have a backup and I want it to restore it to a new Database Wss_Content3 The backfile is a backup of the Wss_Content database.

When I run the following code:

USE Master;
GO

RESTORE DATABASE Wss_Content3 FROM DISK = 'f:\Fill_Wss_Content.bak'
  WITH 
  MOVE 'Wss_Content3' TO 'E:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\Wss_Content3.mdf',
---------------------------------------^^^^^
  MOVE 'Wss_Content3_log' TO 'F:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\Wss_Content3_log.ldf';

I get:

Msg 1834, Level 16, State 1, Line 3 The file 'E:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\WSS_Content.mdf' cannot be overwritten. It is being used by database 'WSS_Content'. Msg 3156, Level 16, State 4, Line 3 File 'WSS_Content' cannot be restored to 'E:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\WSS_Content.mdf'. Use WITH MOVE to identify a valid location for the file. Msg 1834, Level 16, State 1, Line 3 The file 'F:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\WSS_Content_log.ldf' cannot be overwritten. It is being used by database 'WSS_Content'. Msg 3156, Level 16, State 4, Line 3 File 'WSS_Content_log' cannot be restored to 'F:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\WSS_Content_log.ldf'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 3 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.

It's like to restore on the database Wss_Content but I want to do it on a new database Wss_Content3.

How can I do it?

EDIT: With:

USE Master;
GO

RESTORE DATABASE Wss_Content3 FROM DISK = 'f:\Fill_Wss_Content.bak'
  WITH 
  MOVE 'Wss_Content' TO 'E:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\Wss_Content3.mdf',
---------------------------------------^^^^^
  MOVE 'Wss_Content_log' TO 'F:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\Wss_Content3_log.ldf';

I get:

Msg 3154, Level 16, State 4, Line 2
The backup set holds a backup of a database other than the existing 'Wss_Content3' database.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

EDIT2:

USE Master;
GO
RESTORE  FILELISTONLY FROM DISK = 'f:\Fill_Wss_Content.bak'

WSS_Content E:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\WSS_Content.mdf D   PRIMARY 163158360064    35184372080640  1   0   0   140B6DD4-DCA0-4FD8-BA65-59390D5FC3B5    0   0   162484191232    512 1   NULL    133075000000938400215   F50C4B9C-B575-4158-B5D6-F44B92FE0380    0   1   NULL
WSS_Content_log F:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\WSS_Content_log.ldf L   NULL    22918791168 2199023255552   2   0   0   C83B63C2-B68E-4101-A52B-07EFC08446D7    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1   NULL

EDIT3: I've tried this (with Wss_Content5.mdf and Wss_Content5_log.ldf ) non existing:

RESTORE   DATABASE Wss_Content3 FROM DISK = 'f:\Fill_Wss_Content.bak'
  WITH 
  MOVE 'Wss_Content' TO 'E:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\Wss_Content5.mdf',
---------------------------------------^^^^^
  MOVE 'Wss_Content_log' TO 'F:\MSSQL\MSSQL11.SHP2013PROD\MSSQL\Data\Wss_Content5_log.ldf

Unfortunately I get this error:

Msg 3154, Level 16, State 4, Line 5
The backup set holds a backup of a database other than the existing 'Wss_Content3' database.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Solution

  • You need to use the following option:

    WITH REPLACE
    

    If you're using the wizard from SQL Management Studio to restore the database rather than the command line, there is a corresponding option that needs to be checked: "Overwrite the existing database (WITH REPLACE)"