Home » Blog » SQL Server » How to Restore SQL Database With Different Name

How to Restore SQL Database With Different Name

Neeraj Kumar
Published By Neeraj Kumar
Debasish Pramanik
Approved By Debasish Pramanik
Published On
Last Updated

SQL Server restores a backup under a new name when you type that name in the restore dialog or in the RESTORE DATABASE statement. File paths still point to the original files unless you set new ones. Three methods restore SQL database with different name next to the live one. Free methods use the dialog or T-SQL and need a readable backup. Recovery software moves MDF file data to a new database when no backup exists.

You need a test copy of KitchenDB next to the live database. Your backup is ready but the live files occupy the original paths.

Search results show how to restore SQL database with different name in the Destination box and stop there. Few explain why the file names matter or how to rename a database that already exists.

This guide explains how to create a test copy without touching production, including the rename statements that fix the file names.

Microsoft states in the rename a database reference that renaming does not alter the physical or logical names of the database files.

Which Items Follow the Backup and Not the Name?

Three items follow the backup and not the name you type:

  • File paths. Microsoft states that every file returns to its original location by default.
  • Logical names. The backup stores them and MOVE clauses refer to them.
  • Tail log option. Per Microsoft a ticked box sets the live database to the restoring state when you restore a copy.

Find your situation in the table below.

SituationMethod
Readable backup and a screen#1
Readable backup and a script#2
Only an MDF file or a damaged backup#3
Database already restored under the old nameRename section below

3 Ways to Restore SQL Database With Different Name

Backups power the first two methods and the MDF file powers the third.

FeatureSSMS DialogT-SQL ScriptRecovery Software
CostNo feeNo feeFree scan and a licensed export
Best forOne time copiesRepeat test restoresMDF files and damaged backups
NeedsBackup file and folder rightsBackup file and logical namesWindows PC and an MDF file duplicate
Main limitTyping errors in the gridWrong logical namesTrial export limit

Method #1: Rename the Copy in SSMS (Free, Needs a Healthy Backup)

Microsoft shows every page in the restore a database backup with SSMS steps. Seven actions restore KitchenDB as KitchenDB_Test:

  1. Right click Databases under your server and choose Restore Database.
  2. Select Device as the source and add KitchenDB.bak with the browse button.
  3. Replace the name in Database under Destination with KitchenDB_Test.
  4. Open the Files page and edit the paths in the Restore the database files as grid. Microsoft names that grid for a new location of every file.
  5. Give the data file and the log file new names like KitchenDB_Test.mdf and KitchenDB_Test_log.ldf.
  6. Clear Take tail-log backup before restore on the Options page.
  7. Click OK and wait for the confirmation.

    Avoid the Overwrite the existing database (WITH REPLACE) box for a copy. Microsoft states that REPLACE also permits a restore to overwrite an existing file that it cannot verify as part of the restored database.

Method #2: Restore With a Script and MOVE (Free, Needs Logical Names)

Scripts repeat the same restore in seconds. Logical names come from RESTORE FILELISTONLY as shown in the guide to restore a database from a BAK file.

RESTORE DATABASE KitchenDB_Test
FROM DISK = N'D:\Backups\KitchenDB.bak'
WITH MOVE N'KitchenDB' TO N'E:\SQLData\KitchenDB_Test.mdf',
     MOVE N'KitchenDB_log' TO N'E:\SQLData\KitchenDB_Test_log.ldf',
     RECOVERY;

As per Microsoft's RESTORE arguments, MOVE places a file at the path you give and avoids collisions with files that exist. Find the new database name in the first line.

REPLACE removes any existing database of that name per Microsoft. New names need no REPLACE option.

Method #3: Export the MDF File to a New Database With EmailDoctor (Free Preview, Licensed Export)

Some servers have no backup at all. EmailDoctor SQL Database Recovery processes the duplicate of your MDF file and sends its objects to a database with the name you type.

Place an MDF file duplicate in a safe folder first. Trial exports stop at five extra objects and 25 rows of one table.

Scan the Duplicate and Preview Objects

  1. Open the recovery software on the Windows PC. Recovery software home screen
  2. Press Open and select KitchenDB.mdf from the duplicate folder. Open button Select MDF file window
  3. Choose Quick Scan for a healthy file or Advance Scan for a damaged one. Tick Recover Deleted Objects only when you need deleted items. Scan mode options
  4. Select the SQL Server release yourself or accept Auto detect. SQL Server version list
  5. Review the scan report and open a table from the tree to preview its rows. Scan report Preview of the MDF file objects

Export Under the New Name

  1. Click Export at the top. Export button
  2. Select SQL Server Database in Export Options. Export options
  3. Choose Server Name and the Authentication mode. Windows Authentication option
  4. Select Create New Database and set Database Name to KitchenDB_Test. Create New Database option Database name box
  5. Mark the tables and other objects you want to export. Database components list
  6. Select With Schema & Data and press Export. Export deleted objects option
  7. Confirm Export Completed and refresh Databases in SSMS. Export Completed window Recovered database in SQL Server

How to Rename the Database and Its Files After a Restore?

Databases restored under the old name need a rename. Microsoft requires ALTER permission on the database and states that other users cannot access it during the rename. Use single user mode with a rollback to disconnect them.

USE master;
GO
ALTER DATABASE KitchenDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE KitchenDB MODIFY NAME = KitchenDB_Test;
ALTER DATABASE KitchenDB_Test SET MULTI_USER;
GO

File names remain as they were. Microsoft documents NEWNAME for the logical name of a file in the ALTER DATABASE file options.

ALTER DATABASE KitchenDB_Test
MODIFY FILE (NAME = KitchenDB, NEWNAME = KitchenDB_Test);

Microsoft recommends a backup of the master database after a rename on SQL Server. Logins that used the old name as their default database show error 4064 until you set the new name.

ALTER LOGIN [report_user] WITH DEFAULT_DATABASE = [KitchenDB_Test];

Which Problems Stop You When You Restore SQL Database With Different Name?

Five problems stop most copy restores:

ProblemReasonFix
Restore reports files in useThe original paths belong to the live databaseEdit the paths in the Files page grid or add MOVE clauses
Source database enters the restoring stateTail log option was selected for the copyClear it on the Options page
Files of the live database get overwrittenREPLACE was ticked for a copyClear the overwrite option
Rename fails with users connectedOther sessions access the databaseUse SINGLE_USER with ROLLBACK IMMEDIATE
Error 4064 after the renameA login still points to the old nameSet the new default database with ALTER LOGIN

Try Method #3 on a duplicate when no backup is readable.

Frequently Asked Questions

Can I Restore SQL Database With Different Name From One Backup?

Yes. Type the new name in Destination or in the first line of RESTORE DATABASE. Edit the file paths too.

Does a Rename Alter the File Names?

No. Microsoft states that renaming a database does not alter the physical or logical names of its files.

Do I Need WITH REPLACE for a Copy?

No. REPLACE removes an existing database of the same name per Microsoft. Select it only when that is your goal.

Can I Rename a System Database?

No. Per Microsoft renaming is not possible for system databases.

Which Permission Renames a Database?

Microsoft requires ALTER permission on the database.

Can I Rename a Database in Use?

No. Per Microsoft the name cannot be altered while other users access the database. Set single user mode with a rollback first.

Do I Need a Master Backup After a Rename?

Yes. Microsoft recommends a backup of the master database after a rename on SQL Server.

Can I Rename the Logical File Name?

Yes. Use MODIFY FILE with NAME and NEWNAME. Microsoft documents the clause in the ALTER DATABASE file options.

Can Recovery Software Use a New Database Name?

Yes. Select Create New Database in the export window and type the name you want.

Does the Trial Version Limit the Export?

Yes. Trial exports are limited to five extra objects and 25 rows of one table. Buying a license ends it.

Author’s Verdict

Typing a new name in the restore dialog sets only the database name. File paths and file names need their own attention.

Use MOVE for repeat restores and the Files page for one copy. Recovery software handles the cases with no backup.

Check the files of the copy before you retire the old database.

  • Restore SQL database with different name and set new file paths because restores retain the original ones.
  • MOVE and the Files page place the files in new folders.
  • REPLACE removes an existing database of that name and a copy never needs it.
  • Renames do not alter logical or physical file names.
  • Recovery software exports an MDF file under a new database name when no backup exists.
  • Try every step on a test server first.
  Neeraj Kumar

By Neeraj Kumar

Neeraj Kumar is a Principal Technical Architect at EmailDoctor with 19+ years of experience in email migration, cloud migration, data recovery and digital forensics. He guides enterprise teams through Exchange, Office 365 and Google Workspace projects and shares what works as an EmailDoctor writer. Every method in his posts is tested on Windows and Mac before it goes live.