asked on

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

Pavel Celba

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

Pavel Celba

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.

bfuchs

ASKER

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

SOLUTION

Vikas Garg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Abhimanyu Suri

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/

bfuchs

ASKER

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

Abhimanyu Suri

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.

bfuchs

ASKER

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

SOLUTION

Abhimanyu Suri

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

Thank you experts!