Restoring a database with same name as an existing

Hi Experts,

I have a database thats called Placement, both the logical name and the database name.
Now I want to restore to the same server a database with same logical name Placement (from a backup), however the database has different name.
But I want make sure it will not replace the existing DB.
Can you give me exact steps how to accomplish that.
Using SSMS 2008 express edition.

Thanks in advance
LVL 5
bfuchsAsked:
Who is Participating?
 
pcelbaCommented:
You may do it all in SSMS:

1) Open SSMS
2) Right click on the Databases and select Restore database
3) Select "From device" option and select the backup file
4) Enter NEW database name in "To database" field
5) Look at the Options page and update/fix physical file names and paths
6) Click OK

The database name is important and you may select whatever you need except the existing db name in this case.
There is nothing like logical database name on SQL Server. Each database has just ONE name which must be unique on the SQL Server engine.

Each database consists of (at least) two physical files (mdf and one or more ldf). These files do have assigned logical file names.
Logical file names are irrelevant as they are not used obviously. Physical file names are important and you may select whatever name you want except the existing name and path.
0
 
pcelbaCommented:
If you need to be 100% sure then do following:
1) Rename the existing database
2) Create a backup of the renamed database (just to be sure)
3) Restore the database you've intended to restore. Select any DB name of your choice. Don't forget to change the target filenames if they are in collision with any existing DB filenames.
4) Logical names are not so relevant
0
 
pcelbaCommented:
Just to clarify:
If you issue following command
SELECT * FROM sys.database_files
or better
SELECT * FROM sys.master_files
then you may see both logical file name and physical file name. The logical file name (in column named "name") does not need to be unique at the server, it should be unique in one database only so you may see many equal names if you issue above command for several databases.

The database name itself is listed when you issue
select * from sys.databases
Database names must be unique at the server.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
bfuchsAuthor Commented:
Hi,

1) Rename the existing database
I cannot do that as the existing is a live db and most likely in use all the time.

Just to clarify.
Current db is placement.
Trying to import Home care db from a different server.
However the logical name of home care db is placement.
bottom line, I would like to have 2 db's in one server, placement & home care.

Thanks,
Ben
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

You can do this by doing the following steps



USE master
GO

/* Step I - Take BAckup of Main Database */

BACKUP DATABASE Placement TO DISK = N'\\e:\SQL Backups\Placement.bak'
WITH COMPRESSION
GO

Open in new window






/* Step II - Restore the Backup of Main Database as copy with different DB and File Name */

RESTORE DATABASE Placement_Copy FROM DISK= N'\\e:\SQL Backups\Placement.bak'
WITH
MOVE 'Placement' TO N'E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Placement_Copy.mdf',
MOVE 'Placement_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Placement_Copy_Log.ldf'
GO

Open in new window


Here you need to use existing file name with move command.

After restore you can rename the logical file if required.
0
 
Abhimanyu SuriSr Database EngineerCommented:
Please refer below link, in my opinion it exactly matches your requirement

https://www.mssqltips.com/sqlservertip/4407/rename-logical-database-file-name-for-a-sql-server-database/
0
 
bfuchsAuthor Commented:
Hi Experts,

I'm getting the attached error while trying to restore from a backup file.
FYI- both servers are SQL 2008 version.

Thanks,
Ben
Untitled.png
0
 
Abhimanyu SuriSr Database EngineerCommented:
Please provide the command used to restore the backup.
Also, please confirm if you have a "Home care" database on the instance where "placement"is located.
0
 
bfuchsAuthor Commented:
Hi,

They are in two servers.

I first went to SSMS in original server and right click on db selected backup.
then copied that file to other server.
and then created db named homecare.
then right click on that new home care database and selected restore/from device
and selected that backup file, then this error popped up.

Thanks,
Ben
0
 
Abhimanyu SuriSr Database EngineerCommented:
HI Ben,

Please use "WITH REPLACE" option, since DB is already created
0
 
bfuchsAuthor Commented:
Thank you experts!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.