Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 4:46 PM
Points: 6, Visits: 26
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
Post #1509428
Posted Tuesday, October 29, 2013 9:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 132, Visits: 246
If you are trying to overwrite the existing dev database, you will need to add REPLACE to your with list.
Post #1509430
Posted Tuesday, October 29, 2013 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 4:46 PM
Points: 6, Visits: 26
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
Post #1509449
Posted Tuesday, October 29, 2013 11:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 132, Visits: 246
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.
Post #1509478
Posted Tuesday, October 29, 2013 11:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 4:46 PM
Points: 6, Visits: 26
The names no longer protect the innocent, but who really cares...Thanks

DEV-Dysel_Data c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV-Dysel_Data.mdf D PRIMARY
DEV-Dysel_1_Data c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV-Dysel_1_Data.ndf D Data Filegroup 1
DEV-Dysel_Log c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV-Dysel_Log.ldf L NULL

select name, physical_name from Dev_Dysel_Copy.sys.database_files =
DEV-Dysel_Data C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV_Dysel_Copy_Data.mdf
DEV-Dysel_Log C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV_Dysel_Copy_Log.ldf
DEV-Dysel_1_Data C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEV_Dysel_Copy_1_Data.ndf
Post #1509495
Posted Tuesday, October 29, 2013 1:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 4:46 PM
Points: 6, Visits: 26
Thanks you for the help grasshopper. I had the same logical files names for both databases.
Post #1509545
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse