Restoring an existing database on the same server

The process for restoring a database works the same as any other restore process, with one exception. Problems are unlikely during the restore, but the values of the files' physical locations are embedded in the backup copy and will need to be changed.

To investigate this we can run the following query in the master database. This will give us the results of all our databases and the location of the physical files.

SELECT sd.name,
saf.name,
saf.filename
FROM master.dbo.sysaltfiles saf
INNER JOIN master.dbo.sysdatabases sd ON sd.dbid = saf.dbid

Listing 1

Database	Logical File	Physical File
master	master	C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
master	mastlog	C:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf
tempdb	tempdev	C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf
tempdb	templog	C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf
model	modeldev	C:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf
model	modellog	C:\Program Files\Microsoft SQL Server\MSSQL\data\modellog.ldf
msdb	MSDBData	C:\Program Files\Microsoft SQL Server\MSSQL\data\msdbdata.mdf
msdb	MSDBLog	C:\Program Files\Microsoft SQL Server\MSSQL\data\msdblog.ldf
pubs	pubs	C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf
pubs	pubs_log	C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf
Northwind	Northwind	C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind.mdf
Northwind	Northwind_log	C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.ldf

To discover the contents of our backup file, we can run the following command. This will show us the Logical and Physical names embedded in the backup file.

RESTORE FILELISTONLY
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind2.BAK'

Listing 2

LogicalName	PhysicalName	Type	File Group Name	Size	MaxSize
Northwind	C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind.mdf	D	PRIMARY	3801088	35184372080640
Northwind_log	C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.ldf	L	NULL	4325376	35184372080640

If you look at the values of the PhysicalName from the first list and the second list for the Northwind database, you can see the values are the same for the MDF files and the LDF files. The listing below better illustrates this.

Type	Listing	Physical File Name
MDF	Listing 1	C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind.mdf
MDF	Listing 2	C:\Program Files\Microsoft SQL Server\MSSQL\data\Northwnd.mdf
LDF	Listing 1	C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.ldf
LDF	Listing 2	C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.ldf

Since you can not have two files with the same name in the same directory you will need to do one of two things. Either rename one of these files when the restore is run or use a different directory. This can be done with Enterprise Manager or by using T-SQL scripts.

Enterprise Manager

To do the restore using Enterprise Manager, right click on databases in the navigation tree and select All Tasks -> Restore Database. The following window will pop up. Enter the new name of the database and find the backup file or files you want to restore.

At this point if we simply select OK we will only get the following error message, because we can not use already existing files.

To get around this error you will need to select the Options tab and change the name and or location of the physical files. As you can see below when I click on the Options tab SQL Server changes the name of the logical files and includes the "2" automatically. So the new names are Northwind2.mdf and Northwind2_log.ldf and we can just hit OK to proceed with the restore.

T-SQL

When restoring using T-SQL the process is pretty much the same thing. If we issue a straight restore command such as the following:

RESTORE DATABASE Northwind2
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.BAK'

We get the following error message:

Server: Msg 1834, Level 16, State 1, Line 1

The file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind.mdf' cannot be overwritten.
It is being used by database 'Northwind'.
Server: Msg 3156, Level 16, State 1, Line 1 File 'Northwind' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind.mdf'.
Use WITH MOVE to identify a valid location for the file.
Server: Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.ldf' cannot be overwritten.
It is being used by database 'Northwind'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Northwind_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.ldf'.
Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

To get around this we need to use the WITH MOVE option such as the following.

RESTORE DATABASE Northwind2
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.BAK'
WITH MOVE 'Northwind'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind2.mdf',
MOVE 'Northwind_log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind2_log.mdf'

This command uses the same RESTORE command as the first attempt, except we are using the WITH MOVE for both Logical files in the backup. The logical file names that are being used are the names that were pulled from the query results in listing 2.

Summary

As you can see there is not much to restoring another copy of an existing database on the same server. The only thing you really need to do is make sure you rename the physical files. With Enterprise Manager the renaming of the files is automatically done when you click on the Options tab, but with T-SQL you need to use the WITH MOVE option to specify the new file names.

About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.

More information on SearchSQLServer.com