SQL-Server: Cannot be overwritten. It is being used by database

Question

RESTORE DATABASE Tes
FROM DISK = '{7522204E-0256-47B3-9864-137D6D1FD449}6' 
WITH MOVE 'Test' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Tes.mdf',
MOVE 'Test_log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Tes.ldf'

The above restore command is working fine for new dbs, But few old databases it showing a errors like

The file 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\db1.mdf' cannot be overwritten. It is being used by database 'windb'. Msg 3156, Level 16, State 4, Line 1 File 'windb' cannot be restored to 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\db1.mdf'. Use WITH MOVE to identify a valid location for the file

I found that only dbs having more than one ndf files having this issues ..

Any solutions this issue ?

Answer 1

Try to restore the database from the Restore Database Wizard. For that, in the Object Explorer, right-click on Databases -> select Restore Databases.... In the window that appears, set the necessary details for the Source and the Destination. And in the left pane, either select Options or Files, depending on the SSMS version you have.

In the table that appears, find the column Restore As. In each row, give a new file name, instead of the name that shows. For example, in your case it might be showing the file names as something similar to <Folder Path>\DATA\Tes.mdf and <Folder Path>\DATA\Tes.ldf. Give a different name other than Tes. (A name that does not already exist in the DATA folder).

Note: The file names do not have anything to do with the database name, but it will easy referencing later on to give a meaningful name, in case you have to backup or look for the files.

Answer 2

If you are trying to move your database to a new location, you should better detach the source database or take it offline first

Answer 3

I faced this issue when working on an MS SQL Server 2012.

I was trying to restore a database using the script below

USE master;
GO

ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

    
RESTORE DATABASE my_db.dev
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\com.mydb.dev.bak'
    WITH REPLACE,
         STATS = 10,
         RESTART,
    MOVE 'com.mydb.dev' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.mydb.dev.mdf',
    MOVE 'com.mydb.dev_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.mydb.dev_log.ldf'
GO
    
ALTER DATABASE com.mydb.dev SET MULTI_USER;
GO

And then this was throwing the error below when I tried executing the restore task:

The file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.mydb.dev.mdf' cannot be overwritten.  It is being used by database restdb

The file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.mydb.dev_log.ldf' cannot be overwritten.  It is being used by database restdb

Here's how I fixed it:

The issue was that the database backup file that I was using for this database restore operation had been previously used to restore data to a different database on the same server.

All I had to do was to change the Physical names of the Logical files to that of the database that I was to restore to. That is from com.mydb.dev.mdf to my_db.mdf and from com.mydb.dev_log.ldf to my_db_log.ldf:

USE master;
GO

ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

    
RESTORE DATABASE my_db
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\com.mydb.dev.bak'
    WITH REPLACE,
         STATS = 10,
         RESTART,
    MOVE 'com.my_db.dev' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\my_db.mdf',
    MOVE 'com.mydb.dev_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\my_db_log.ldf'
GO
    
ALTER DATABASE my_db SET MULTI_USER;
GO

And this time, the database restore task ran successfully.

That's all.

I hope this helps