Post reply

Backing up raw mdf/ldf files Online

  • October 10, 2006 at 9:41 am

    #60877

    Hi All

    I wanted to know if it was possible to backup database files whilst the database is online.

    Eg I have a database with 5 filegroups/files, I need to know if I can backup the files whilst the database is online. Does the database also need to be in full recovery mode ?

    If this is possible, what software supports this option.

     

    Thanks in advance.

     

     

    Kindest Regards,

    John Burchel (Trainee Developer)

  • October 10, 2006 at 10:09 am

    #665046

    I don't think you can. You'll get an error that the file is in use. You would have to detach, backup, then reattach, but that takes the DB out of service.  Why would you want to ?

  • October 10, 2006 at 10:51 am

    #665053

    The native SQL Backup can be run while the database is in use.  Most third party backup software can also backup SQL databases while they are online as well.  Take a few minutes to read BOL for more information.

  • October 10, 2006 at 11:01 am

    #665055

    Look also at products like Double Take

  • October 10, 2006 at 11:05 am

    #665056

    But within a very large database, I want to have the flexibility of backing up individual filegroups.

    Does anyone currently use filegroup backup and what has been your experience with it ? Any advice/ information you can provide ?

     

    Thanks

    Kindest Regards,

    John Burchel (Trainee Developer)

  • October 10, 2006 at 11:57 am

    #665062

    Using the native SQL backup you can backup and restore individual files/filegroups.  Again, please read BOL.  It does have the information you need to get started.  Our databases are not large enough to require it, and we don't have a tight window for completing backups so it is not necessary.

  • October 10, 2006 at 1:13 pm

    #665075

    • You certainly can perform file/filegroup backups using native SQL Server backup. 
    • I don't think that any 3rd party general purpose backup (like Brightstor or Veritas) with their SQL Server agent will do file/filegroup backups. 
    • I don't know if any 3rd party SQL backups will do file/filegroup backup and restores.  (But you may find you don't need to have file/filegroup backups with smaller faster backups that these tools provide.)

    Hope this helps.



    Mark

  • October 10, 2006 at 1:25 pm

    #665078

    We use idera's SQLSafe for our big production backup. It's gone from 1 hour down to 25 minutes, and the backup file from 220 Gig to 40 Gig.

  • October 10, 2006 at 1:45 pm

    #665081

    You cannot backup the .mdf/.ndf/.ldf files while the database is online. They will not be restorable.

    You have two options:

    1. put the database offline and backup the files.

    2. use SQL Server backup commands (or third-party backup tool with a SQL Server agent) and backup the database while it is online.

    -SQLBill

  • October 10, 2006 at 1:53 pm

    #665083

    True, you can't backup the physical .mdf/.ndf/.ldf files if the database is open.  If you read between the lines, however, you can see that the question was actually about backing up files and filegroups, which can be done online using the native SQL backup routine.

  • October 11, 2006 at 7:31 am

    #665208

    IBM's Tivoli Storage Management product has the option to do file/filegroup backups

  • October 11, 2006 at 1:39 pm

    #665295

    You certainly can back up mdf,ndf,ldf files directly.  It is somewhat complicated, however.

    Contemplate the fact that SQL server will accept this backup command:

    backup database model

    to disk = 'c:\model.bak'

    with snapshot

    Now, "snapshot' is not listed as a WITH option in BOL, but it actually is.  Included in the code samples that come with SQL 2000 are a few pieces of broken code that show how to implement a VDI device that will consume the snapshot metadata that the backup statment creates.

    First, you call the Backup WITH SNAPSHOT, specifying the VDI device that you have created in code (Instead of 'to disk =').

    SQL server quiesces all writes to the physical files for the duration of the backup, queueing any that are pending.

    Then make a shadow copy of the database files (Split-mirror or copy-on-write). 

    Then cause the VDI device to return success somehow in code.

    bada-boom, normal operation resumes and you have (supposedly) cloned mdf, ldf, ndf file(s) that can be attached if needed.

    Check out the code samples on a rainy day.  They are very interesting.

    jg

     

     

     

     

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply