Restoring database from mdf and ldf files of SQL Server 2008

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Yes  it is possible  The steps are    First Put the  mdf and  ldf file in  C  Program Files Microsoft SQL Server MSSQL 1 MSSQL DATA  folder Then go to sql software   Right-click    Databases    and click the    Attach    option to open the Attach Databases dialog box Click the    Add    button to open and Locate Database Files  From  C  Program Files Microsoft SQL Server MSSQL 1 MSSQL DATA folder Click the  OK  button  SQL Server Management Studio loads the database from the  MDF file

User · Answer

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

User · Answer

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

[sql-server-2008] Restoring database from .mdf and .ldf files of SQL Server 2008

Examples related to sql-server-2008

Examples related to database-restore