1

is it possible to restore a MSSQL server by copying its directory/data files? I have a damaged hard disk and was able to salvage the C:\Program Files (x86)\SQL Server 2008\MSSQL\MSSQL10.MYDATABASE directory. I copied that to my new installation and tried to start the service, but it failed. Is it possible so retore the db somehow?

I am using Windows 7 64 bit and MS SQL Server 2008 R2.

migrated from superuser.com Dec 24 '13 at 22:08

This question came from our site for computer enthusiasts and power users.

2

To restore a database from the files you need the data file - MDF, sometimes NDF exist as well, and the log file - LDF.

  1. Start SQL Server Management Studio
  2. Right-click the Databases node
  3. Select Attach
  4. Click Add
  5. Select the MDF file you saved
  6. The LDF file should be automatically added. If not, the 'Not found' message will be shown and you must add the file manually by clicking the ellipsis button

enter image description here

If this fails, try to attach only the MDF file, using the sp_attach_single_file_db function

EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2012Restored' ,
@physname = N'D:\ApexSQL\backups\CS2.mdf';

If this fails, the MDF file might be corrupted. You can try a third party tool that will read the MDF file and recover your data

ApexSQL Recover is a tool that can read a corrupted MDF file, re-create all objects and recover table records. It can create T-SQL script or create a copy of the database

Disclaimer: I work for ApexSQL as a support engineer

4
  1. You shouldn't have copied the entire directory. You should have copied only the database and transaction log files for the database in question.

  2. Uninstall and reinstall SQL Server on the target host.

  3. Copy the database and transaction log files for the database in question to the target host.

  4. Attach the database.

  • What joeqwerty said. Generally, you want to be cautious about copying the system databases over each other. More specifically: Master contains the server's information about the databases themselves, including the location of the files. MSDB contains any jobs you had. – Katherine Villyard Dec 26 '13 at 19:50
0

Exactly what @joeqwerty said, just few tips for the future.

It is best to keep database files .MDF on separate drive from log files .LDF. It is also recommended to keep all databases off the C: drive. TEMPDB performs best when it is on a drive by it self.

Here is an article how to move system dbs Tech Net

  • 1
    Just to clarify: SQL Server transaction logs do not have the .LOG file extension, they have the .LDF file extension. – joeqwerty Dec 24 '13 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.