CODEWITHSUNDEEP
sqlsql-serverdatabasebackuprestore
KimberJosh
KimberJosh

Reputation: 45

How to take a restore of a database in SQL server with a different name

I have a backup of a PRODUCTION DB and I want to create an exact replica of it on the same server for testing purposes. So whenever I try to restore it with a different name I get an error as 'Exclusive Access could not be obtained because the database is in use'. The source DB cannot be taken offline or be disconnected due to organizational policies.

Upvotes: 2

Views: 1962

Answers (2)

Anusha Subashini
Anusha Subashini

Reputation: 397

These are the steps

  1. Get the backup of the database

  2. Make sure there are no active connections

  3. Expand the Management folder

  4. Double click the Activity Monitor

Backup the existing database lets say ABC_test.bak database

Right click database
    Select Tasks
    Select Back Up
    Verify Database says ABC_test
    Verify Backup Type says full
    Verify Backup component says Database
    Verify Name says ABC_test-Full Database Backup
    Select Backup to Disk
    Select Add
    Select Backup Device
    Select ABC_test
    Under Options…Select the Overwrite all existing backup sets

Step 2: Create new database. lets say Sample.

CREATE DATABASE [Sample] ON  PRIMARY 
( NAME = N'Sample', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.ANUSHADB\MSSQL\DATA\Sample.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Sample_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.ANUSHADB\MSSQL\DATA\Sample_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Restore the Sample database with the ABC_test backup Right click database Select Tasks Select Restore Select Database

Verify the To database says ABC_test
    Select to the From device option
    Select …
    Set Backup media to Backup device
    Select Add
    Select ABC_test
    Check Restore
    Under Options…Select Overwrite the existing database
    Rename the physical names of the log and data files to Sample\Sample.MDF and .LDF

Upvotes: 0

anon
anon

Reputation:

In order to restore a copy of a database on the same instance but with a different name and without disrupting the original in any way, you need to make sure you restore to a different database name and you will need to move all of the files associated with the copy to a different location.

This will create for you the list of logical files (let's call this "A"):

RESTORE FILELISTONLY FROM DISK = 'C:\temp\PRODUCTION_DB.bak';

This will tell you where your current files are (let's call this "B"):

EXEC [PRODUCTION DB].sys.sp_helpfile;

Then you can create your RESTORE command manually based on those outputs:

RESTORE DATABASE PRODUCTION_DB_COPY
  FROM DISK = 'C:\temp\PRODUCTION_DB.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'LogicalName1_fromA' TO 'path_from_filename1_fromB\PhysicalName1_fromA_copy.mdf',
  MOVE 'LogicalName2_fromA' TO 'path_from_filename2_fromB\PhysicalName2_fromA_copy.mdf',
  ...;

I honestly would recommend against trying to do this through the UI in Management Studio. It tries to help, but falls flat. Even if you specified a new database name, it is obviously trying to restore over your existing database, and pointing and clicking the wrong way here can be disastrous.

Upvotes: 1

Related Questions