October 29, 2013 at 9:35 am
I'm trying to restore a backup from a 'dev' database to copy 'test' database & I'm getting:getting:
'DEV_Database_Test.mdf' cannot be overwritten. It is being used by database 'DEV_Database_Test'
&
File 'DEV_Data' cannot be restored to 'C:\MSSQL\DATA\DEV_Data_Test.mdf'. Use WITH MOVE to identify a valid location for the file.
File 'DEV_Data_2' cannot be restored to 'C:\MSSQL\DATA\DEV_Data_Test_2.ndf'. Use WITH MOVE to identify a valid location for the file.
File 'DEV_Data_Log' cannot be restored to 'C:\MSSQL\DATA\DEV_Data_Test_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Even when I'm setting the database offline & using MOVE...
Here's my syntax:
USE [master]
ALTER DATABASE Dev_Database_Test
SET OFFLINE WITH ROLLBACK IMMEDIATE
GORESTORE DATABASE Dev_Database_Test
FROM DISK='C:\MSSQL\Backup\DEV-database'+ (select CONVERT(VARCHAR(8), getdate(), 112))+'.bak'
WITH
MOVE 'DEV-Database_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test.mdf',
MOVE 'DEV-Database_1_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test_1.ndf',
MOVE 'DEV-Database_Log' TO 'C:\MSSQL\DATA\Dev_Database_Test.ldf'
ALTER DATABASE Dev_Database_Test SET ONLINE
GO
ALTER DATABASE Dev_Database_Test SET MULTI_USER
GO
Any help would be greatly appreciated
October 29, 2013 at 9:39 am
If you are trying to overwrite the existing dev database, you will need to add REPLACE to your with list.
October 29, 2013 at 10:08 am
Even when I'm setting the database offline, WITH REPLACE & MOVE... I'm getting the same error...
Here's my syntax:
USE [master]
ALTER DATABASE Dev_Database_Test
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE Dev_Database_Test
FROM DISK='C:\MSSQL\Backup\DEV-database'+ (select CONVERT(VARCHAR(8), getdate(), 112))+'.bak'
WITH REPLACE,
MOVE 'DEV-Database_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test.mdf',
MOVE 'DEV-Database_1_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test_1.ndf',
MOVE 'DEV-Database_Log' TO 'C:\MSSQL\DATA\Dev_Database_Test.ldf'
ALTER DATABASE Dev_Database_Test SET ONLINE
GO
ALTER DATABASE Dev_Database_Test SET MULTI_USER
GO
October 29, 2013 at 11:06 am
OK, how about we have a look at what is really going on with the files in both places. Run these, and post the results:
restore filelistonly from disk ='C:\MSSQL\Backup\DEV-database'+ (select CONVERT(VARCHAR(8), getdate(), 112))+'.bak'
select name, physical_name
from Dev_Database_Test.sys.database_files
You may want to run the second query on the source database, as well, just to see if someone added a fourth file on you.
October 29, 2013 at 11:36 am
The names no longer protect the innocent, but who really cares...Thanks
DEV-Dysel_Datac:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV-Dysel_Data.mdfD PRIMARY
DEV-Dysel_1_Datac:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV-Dysel_1_Data.ndfD Data Filegroup 1
DEV-Dysel_Logc:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV-Dysel_Log.ldfL NULL
select name, physical_name from Dev_Dysel_Copy.sys.database_files =
DEV-Dysel_DataC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV_Dysel_Copy_Data.mdf
DEV-Dysel_LogC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV_Dysel_Copy_Log.ldf
DEV-Dysel_1_DataC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV_Dysel_Copy_1_Data.ndf
October 29, 2013 at 1:28 pm
Thanks you for the help grasshopper. I had the same logical files names for both databases.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply