SQL Server Backup – How to Backup Database by Copying MDF and LDF Files

backupsql servertransaction-log

I want to backup a SQL Server database by copying its files manually. Is copying the database.MDF and database.LDF files enough or should I also add more files?

It might be not the proper way for backing up a database, but in certain cases it might help for testing purposes for example.

Suppose you are left with a corrupted OS with no any DB backups ,but you can still plug it as HDD and get those files containing the data available in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA and run the following commands 

 Create database dbname. 

 On. 

 ( 
 Filename= 'mymdf.MDF', 
 Filename ='mylog.LDF'. ). 

 For attach;

Best Answer

  1. Is there a reason for you to do this? Why don't you use:

    backup database x to disk...?

  2. Try at least to list all files of your database:

    select 
   db_name(database_id) as Database_Name,
   physical_name,
   name, 
   state_desc
from sys.master_files
where 
   db_name(database_id) ='Your_Database'

  3. After this, set your database OFFLINE (this will stop the database):

    alter database [Your_Database] set offline
  4. Then, copy your files with ctrv+c, ArcServe or whatever.

  5. Set it Online again:

    alter database [Your_Database] set online
Related Question