How to create multiple copies on a server

SQL Server Administration
1

Hi,
I have been researching this for days on the web and I find f=things that are close but not exactly, I am hoping that someone can help me, please.

Basically, we have a database (we will call) Base1, and I need from this a copy to do development and call it base2_Dev. so at this point, it is easy I would just restore from Base1 and call it base2_Dev. I have done this many times. However, now I need the same thing for another one, we can call it base3. this it will not let me do.

So in my research, I found things like this below, in different variations, but none of them work. it complains about the file names or ti says the database is in use by the system, depending on what I do here.

RESTORE DATABASE Base1 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Bak1.bak'
With MOVE 'Base' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Namg.mdf',
MOVE 'base_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Namg.ldf'

  Any ideas on getting this to work or anything else would be great.

Thank you

2

seems to me it's in the name of the files. If you are trying to restore to base3 and using the same MDF/LDF file names, you will get that error. If you use the GUI, then it will probably change the names for you, but you are probably using a script, so you would need to change the MDF/LDF names on your own

3

I like to be in control of where my ldf and mdf files reside. I use c:\DATA and c:\LOGS
For the following to work, you need C:\DATA and C:\LOGS folders created.

--Step 1 find details about your backup
RESTORE FILELISTONLY 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Bak1.bak'   

--Step 2
RESTORE DATABASE Base1 FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Bak1.bak'
WITH
--LogicalName for .mdf from Step1
MOVE 'use_name_from_LogicalName_Column' 
TO 'C:\DATA\HRTest.mdf',		
--LogicalName for .ldf from Step1	
MOVE 'use_name_from_LogicalName_Column' 
TO 'C:\LOGS\HRTest_log.ldf'
4

Hi,
I have done this and put in the names that they gave and I get " cannot be overwritten. It is being used by database"

5

that means you already have an existing mdf and ldf files with that same exact name, you need to change the name so it does not conflict though, it is a filesystem so it will say that.

you cant have the same Namg.mdf and Namg.ldf files, those belong to each database.
name it DarthVader.mdf and DarthVader.ldf instead

6

First thanks for your help here.
I did try giving them, where you have 'use_name_from_LogicalName_Column' , many different names and that did not work. so you must mean doing something else?
Can you please tell me what and how to change it.
Thanks

7 
RESTORE DATABASE Base3 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Bak1.bak'
With MOVE 'Base' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Base3_data.mdf',
MOVE 'base_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Base3_log.ldf'

change the file name to something unique for that database. I use the database name - and append _data or _log to the name.

If the database you are restoring to already exists - and you want to overwrite the existing database then you need to specify that option.