CODEWITHSUNDEEP
sql-server-2008database-restore
Ahsan Raza
Ahsan Raza

Reputation: 779

Restoring database from .mdf and .ldf files of SQL Server 2008

For some reason I have to uninstall SQL Server 2008 R2 but before that I copied two files (.mdf and .ldf) of my database from

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA

Now, the question is, is it possible for me to recover the database from these files in my new installed SQL Server 2008 R2.

If yes: then how can I do this?

Upvotes: 67

Views: 210445

Answers (8)

Jaydeep Gausvami
Jaydeep Gausvami

Reputation: 51

Ensure that you have the .mdf and .ldf files of the database you want to restore. These files contain the data and log information of the database, respectively. Copy them to a location accessible by your SQL Server instance.

Below is the way we can create database using SQLCMD (Command Line):

DECLARE @MDFFilePath NVARCHAR(255) = 'C:\Path\To\Your\Database.mdf';
DECLARE @LDFFilePath NVARCHAR(255) = 'C:\Path\To\Your\Database.ldf';

CREATE DATABASE [YourDatabaseName]
ON (FILENAME = @MDFFilePath)
LOG ON (FILENAME = @LDFFilePath)
FOR ATTACH;

Make sure to replace [YourDatabaseName] with the desired database name and provide the correct file paths for your .mdf and .ldf files.

Another way of doing this is via SSMS.

In SSMS:

  • In Object Explorer, right-click on the "Databases" node.
  • Choose "Attach..."
  • In the "Attach Databases" dialog, click the "Add" button.
  • Browse to the location where you copied the .mdf file.
  • Select the .mdf file and click "OK."
  • You should now see the database listed in the "Databases" folder.

Upvotes: 3

Ishara Samintha
Ishara Samintha

Reputation: 530

CREATE DATABASE MyDatabase 
ON (FILENAME = 'C:\SQLServer\MyDatabase_Data.mdf'), 
(FILENAME = 'C:\SQLServer\ MyDatabase _Log.ldf') 
FOR ATTACH;

Upvotes: -1

Alen.Toma
Alen.Toma

Reputation: 4870

this is what i did

first execute create database x. x is the name of your old database eg the name of the mdf.

Then open sql sever configration and stop the sql sever.

There after browse to the location of your new created database it should be under program file, in my case is 

C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQL\MSSQL\DATA

and repleace the new created mdf and Idf with the old files/database.

then simply restart the sql server and walla :)

Upvotes: 4

Adam John
Adam John

Reputation: 99

I have an answer for you Yes, It is possible.

Go to

SQL Server Management Studio > select Database > click on attach

Then select and add .mdf and .ldf file. Click on OK.

Upvotes: 9

jenson-button-event
jenson-button-event

Reputation: 18941

From a script (one that works):

CREATE DATABASE Northwind
ON ( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Northwind.mdf' )
LOG ON ( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Northwind_log.ldf')
GO

obviously update the path:

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA

To where your .mdf and .ldf reside.

Upvotes: 14

TheGameiswar
TheGameiswar

Reputation: 28890

First google search yielded me this answer. So I thought of updating this with newer version of attach, detach. 

Create database dbname 
On 
(   
Filename= 'path where you copied files',   
Filename ='path where you copied log'
)
For attach;

Further,if your database is cleanly shutdown(there are no active transactions while database was shutdown) and you dont have log file,you can use below method,SQL server will create a new transaction log file..

Create database dbname 
    On 
    (   
    Filename= 'path where you copied files'   
    )
    For attach;

if you don't specify transaction log file,SQL will try to look in the default path and will try to use it irrespective of whether database was cleanly shutdown or not..

Here is what MSDN has to say about this..

If a read-write database has a single log file and you do not specify a new location for the log file, the attach operation looks in the old location for the file. If it is found, the old log file is used, regardless of whether the database was shut down cleanly. However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database.

There are some restrictions with this approach and some side affects too..

1.attach-and-detach operations both disable cross-database ownership chaining for the database
2.Database trustworthy is set to off
3.Detaching a read-only database loses information about the differential bases of differential backups.

Most importantly..you can't attach a database with recent versions to an earlier version

References:
https://msdn.microsoft.com/en-in/library/ms190794.aspx

Upvotes: 12

sridhar
sridhar

Reputation: 1279

Yes, it is possible. The steps are:

  1. First Put the .mdf and .ldf file in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ folder

  2. Then go to sql software , Right-click “Databases” and click the “Attach” option to open the Attach Databases dialog box

  3. Click the “Add” button to open and Locate Database Files From C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\folder

  4. Click the "OK" button. SQL Server Management Studio loads the database from the .MDF file.

Upvotes: 127

Sarfaraz Kazi
Sarfaraz Kazi

Reputation: 1

use test
go
alter proc restore_mdf_ldf_main (@database varchar(100), @mdf varchar(100),@ldf varchar(100),@filename varchar(200))
as
begin 
begin try
RESTORE DATABASE @database FROM DISK = @FileName
with norecovery,
MOVE @mdf TO 'D:\sql samples\sample.mdf',
MOVE @ldf TO 'D:\sql samples\sample.ldf'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage;
print 'Restoring of the database ' + @database + ' failed';
end catch
end

exec restore_mdf_ldf_main product,product,product_log,'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\product.bak'

Upvotes: 0

Related Questions