Backup database by copying the MDF and LDF files

Question

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;

score 3 · Accepted Answer · edited Nov 19 '15 at 11:33

Is there a reason for you to do this? Why don't you use:
```
backup database x to disk...?
```

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'

After this, set your database OFFLINE (this will stop the database):
```
alter database [Your_Database] set offline
```
Then, copy your files with ctrv+c, ArcServe or whatever.

Set it Online again:

alter database [Your_Database] set online

score 1 · Answer 2 · edited Nov 16 '15 at 13:12

1

Yes, it is possible for a normal database, if for any reason you do not want to use native backup. Just stop MS SQL Server Service before copy (and restore) files.

An example of when it will not work - if you use FILE-STREAM.

edited Nov 16 '15 at 13:12

Michael Green

25,255
13
54
100

answered Nov 16 '15 at 11:33

a_vlad

3,705
2
13
17

score 1 · Answer 3 · answered Nov 16 '15 at 13:33

If you are having a program like carbon copy or another automated file backup service running against your database directory (Directory where your .mdf etc files are stored) and you are wondering if that is enough as a fail safe, the answer is yes, you can do that.

Although if you are doing 'manual' backups you want to backup using SSMS or T-SQL like mentioned in the other answers. Just keep in mind that certain backup applications cannot backup OPEN SQL TABLES so the service will need to be shut down before 'manually' copying the DB. However, if your service does backup OPEN SQL tables, then you will be good to go without any interaction.

Hope this helps!

Backup database by copying the MDF and LDF files

3 Answers3

Linked