Restore

  • 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

  • If you are trying to overwrite the existing dev database, you will need to add REPLACE to your with list.

  • 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

  • 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.

  • 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

  • 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