none
How to restore database

    Question

  •  

    Hi,

    I ' ve dropped test database yesterday.

    before drop the database i' ve taken full

    database backup.but i couldnt restore

    my database

     

    use master

    restore database test from disk='ah1.bak'

     

    what could be the reason for this error?

     

    Msg 1834, Level 16, State 1, Line 2

    The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\samp.mdf' cannot be overwritten. It is being used by database 'ah'.

    Msg 3156, Level 16, State 4, Line 2

    File 'samp' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\samp.mdf'. Use WITH MOVE to identify a valid location for the file.

    Msg 1834, Level 16, State 1, Line 2

    The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\samp_log.ldf' cannot be overwritten. It is being used by database 'ah'.

    Msg 3156, Level 16, State 4, Line 2

    File 'samp_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\samp_log.ldf'. Use WITH MOVE to identify a valid location for the file.

    Msg 3119, Level 16, State 1, Line 2

    Problems were identified while planning for the RESTORE statement. Previous messages provide details.

    Msg 3013, Level 16, State 1, Line 2

    RESTORE DATABASE is terminating abnormally.

     

     

    Friday, June 06, 2008 4:51 AM

All replies

  • It looks like the database "ah" isn't deleted.

     

    As the files used by the database "ah" still exists in the filesystem - the restore command isn't able to restore "Test" Database. If you want to have both "ah" and the "test" database - Please refer to the restore database command with MOVE option.

     

    Cheers,

    Loonysan

    http://mystutter.blogspot.com

     

    Friday, June 06, 2008 5:46 AM
  • Hi Qoms,

    Please try with the below:

    To know the Logical_name, MDF and LDF file location:

    Use <<DB_NAME>>
    GO
    SP_Helpfile

    Resotore Command:

    USE [master]
    GO
    RESTORE DATABASE [DBNAME]
    FROM  DISK = N'BACKUP_FILE_LOCATION'
    WITH  FILE = 1, 
    MOVE N'LOGICALNAME' TO N'MDF_FILELOCATION', 
    MOVE N'LOGICALNAME' TO N'LDF_FILELOCATION', 
    NOUNLOAD
    REPLACE,  -- command will helps you to overwrite on the same files
    STATS = 10
    GO


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Thursday, May 31, 2012 9:14 AM
  • You can try restore the database with different name (using MOVE clause) and  make sure that everything is OK, just rename the database.

    RESTORE DATABASE dbData FROM DISK=N'\\server\f$\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\dbData.bak'
    WITH RECOVERY,
       MOVE 'dbData' TO 'e:\sql_data\dbData .mdf', 
       MOVE 'dbData_log ' TO 'e:\sql_data\dbData_1.ldf'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, May 31, 2012 9:22 AM
    Answerer