2

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;
  • I want to have more options on copying database simple as that . I am already using backups scheduled in the maintenance plan . But i want also to use ROBOCPY – Java Main Nov 16 '15 at 11:51
  • 1
    If you try to attach the copies to the same instance as the source there's likely to be a DB name conflict. – Michael Green Nov 16 '15 at 13:22
  • 1
    The OS will see the files as opened by SQL Server. Copy tools may not be able to read files held open by other programs. Hence the admonishment to detact / close / stop the DB / instance / services. Should you succeed without doing so you may have partial writes & pending transactions to deal with. – Michael Green Nov 16 '15 at 13:30
  • 1
    Why are you copying the mdf/ldf files anywhere instead of the backup files you already have? Please read this: blogs.sqlsentry.com/aaronbertrand/bad-habits-file-backups – Aaron Bertrand Nov 16 '15 at 14:00
2
  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
    
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.

1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.