[sql-server-2008] 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?

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'

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.

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.

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.

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

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 :)