When attempting to restore a SQL Server backup to a different database, you might encounter this frustrating error:

Msg 3156, Level 16, State 4, Line 1
The file 'C:\SQL Directory\DATA\A.mdf' cannot be overwritten. 
It is being used by database 'A'.

This occurs even when you're restoring to database B while database A is online. The root cause lies in how SQL Server handles physical file names during restoration.

The backup file contains metadata specifying the original database files' physical paths. During restoration, SQL Server will by default attempt to recreate these exact file paths. Even when restoring to a different database, it first tries to verify/overwrite the original files before applying the new database name.

Method 1: Use WITH MOVE Option

The most reliable solution is specifying new file locations during restore:

RESTORE DATABASE [B] 
FROM DISK = 'C:\Backups\A.bak'
WITH 
    MOVE 'A_Data' TO 'C:\SQL Directory\DATA\B.mdf',
    MOVE 'A_Log' TO 'C:\SQL Directory\LOG\B.ldf',
    REPLACE;

Key points:

• Use MOVE to specify new physical paths
• REPLACE overrides safety checks
• The logical names ('A_Data'/'A_Log') come from the original backup

Method 2: Script the Logical Names First

To find the exact logical names for use in MOVE commands:

RESTORE FILELISTONLY 
FROM DISK = 'C:\Backups\A.bak';

This returns columns including LogicalName, PhysicalName, and Type (D for data, L for log).

Method 3: Offline the Source Database Temporarily

If you have maintenance windows:

ALTER DATABASE [A] SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Perform restore here
ALTER DATABASE [A] SET ONLINE;

For automation needs, here's a PowerShell example that handles the entire process:

# Find logical names dynamically
$fileList = Invoke-Sqlcmd -Query "RESTORE FILELISTONLY FROM DISK = 'C:\Backups\A.bak'"

$mdf = $fileList | Where-Object {$_.Type -eq 'D'} | Select-Object -First 1
$ldf = $fileList | Where-Object {$_.Type -eq 'L'} | Select-Object -First 1

$restoreQuery = @"
RESTORE DATABASE [B] 
FROM DISK = 'C:\Backups\A.bak'
WITH 
    MOVE '$($mdf.LogicalName)' TO 'C:\SQL Directory\DATA\B.mdf',
    MOVE '$($ldf.LogicalName)' TO 'C:\SQL Directory\LOG\B.ldf',
    REPLACE;
"@

Invoke-Sqlcmd -Query $restoreQuery

• Always specify WITH MOVE when restoring to different databases
• Consider standardizing file naming conventions (e.g., using database GUIDs)
• Document original logical names as part of backup procedures
• For development environments, use contained databases when possible

-- This is what happens internally:
RESTORE DATABASE B 
FROM DISK = 'C:\backups\A.bak'
-- SQL Server automatically tries to use A.mdf/A.ldf paths

RESTORE DATABASE B 
FROM DISK = 'C:\backups\A.bak'
WITH 
    MOVE 'A_Data' TO 'C:\SQL Directory\DATA\B.mdf',
    MOVE 'A_Log' TO 'C:\SQL Directory\DATA\B.ldf',
    REPLACE;

DECLARE @cmd NVARCHAR(MAX);
SELECT @cmd = 
    'RESTORE DATABASE B FROM DISK = ''C:\backups\A.bak'' WITH ' +
    'MOVE ''A_Data'' TO ''C:\SQL Directory\DATA\B.mdf'', ' +
    'MOVE ''A_Log'' TO ''C:\SQL Directory\DATA\B.ldf'', REPLACE';
    
EXEC sp_executesql @cmd;