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 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vikas GargAssociate Principal EngineerCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abhimanyu SuriDatabase 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 SuriDatabase 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 SuriDatabase EngineerCommented:
HI Ben,

Please use "WITH REPLACE" option, since DB is already created
0
bfuchsAuthor Commented:
Thank you experts!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.