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.

Improve this question

3 Answers 3

Reset to default
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

Improve this answer
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.

Improve this answer
1
  • 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. Commented Dec 26, 2013 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

Improve this answer
1
  • 1
    Just to clarify: SQL Server transaction logs do not have the .LOG file extension, they have the .LDF file extension. Commented Dec 24, 2013 at 19:33

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.