Post reply

problem restoring db

  • April 25, 2008 at 3:02 pm

    #186968

    I am trying to restore a db to my local install of sql server 2005. When doing so I get this message:

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Restore failed for Server 'SAM-WKHNCD94333'. (Microsoft.SqlServer.Smo)

    ------------------------------

    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: File 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\si_training_db.mdf' is claimed by 'si_production_db_pri_2'(4) and 'si_production_db_Data'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

    I tried changing the MOVE portion of the script to a new filename, but it didn't work:

    (original)

    MOVE N'si_production_db_Data' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\si_training_db.mdf'

    (changed)

    MOVE N'si_production_db_Data' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\si_training_dbNEW.mdf'

  • April 25, 2008 at 4:28 pm

    #807435

    Use MOVE to change location, not name.

    You probably have different drive mappings.

  • April 25, 2008 at 4:43 pm

    #807438

    I don't understand the difference. Sorry... Could you explain more?

  • April 25, 2008 at 5:06 pm

    #807442

    Check how many files make up your database and the original locations.

    RESTORE FILELISTONLY FROM DISK = 'C:\myBackup.bak'

    Then check to see if any of those files are in use by another database.

    SELECT DB_NAME(database_id), physical_name FROM sys.master_files

  • April 25, 2008 at 5:26 pm

    #807444

    A database consists of multiple files whose name and location are stored in the master database. They don't necessarily have anything to do with the database name, although they default to similar names when you create the database.

    The Restore command allows you to "move" these restored files to new paths and/or filenames. The error you're getting is a naming collision with the restore trying to overwrite existing database files during the restore.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply