Restoring an existing database on the same server

Restoring an older version of a database to the same server is an occasional duty for DBAs. Backup and Recovery expert Greg Robidoux takes you through the process in T-SQL and Enterprise Manager.

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.

One task sometimes needing to be accomplished is that of restoring an older version of an existing database to the same server. It may be necessary for auditing purposes, restoring or recovering older data or even for reporting. In most cases you wouldn't use your production server, but it could easily be the case for test or development servers.

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

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

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.

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.

Northwind2.mdf Northwind2_log.ldf 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 and 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

  • Troubleshooting SQL Server backup and restore dilemmas
  • Restore basics: How to restore SQL Server using Enterprise Manager
  • Testing SQL Server restores


  • This was first published in August 2006
    This Content Component encountered an error

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close