Post reply

How to read .mdf file of database currently in use

  • October 5, 2009 at 11:31 am

    #215101

    I do not have permission to perform back up on a production database, although I have full access to the file system.

    Is there anyway I can access the .mdf file without detaching the database? For example, to Zip the .mdf file. Since my access is read-only, I would imagine this is possible without affecting the operations of the database.

    Best regards,

    Hanxue

  • October 5, 2009 at 11:45 am

    #1061902

    Is there some particular reason to not have SQL back up the database? I'm sure there are ways to back up the file directly, but they're all going to be less reliable, and none of them are going to ensure transactional consistency.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • October 5, 2009 at 11:50 am

    #1061906

    The MDF file is the database. You wouldn't zip up this file, you'd zip up the backup if you want to move it to another system. A backup is the way to copy the system without interrupting operation.

    If you really want to copy this, you need to detach the database. That will require downtime.

  • October 5, 2009 at 11:55 am

    #1061910

    Thank you, GSquared and Steve Jones. I will go ahead and request for a back-up job to be created.

    Cheers,

    Hanxue

  • October 5, 2009 at 11:57 am

    #1061912

    hanxue.lee (10/5/2009)

    Since my access is read-only, I would imagine this is possible without affecting the operations of the database.

    Nope. SQL takes exclusive 'locks' on the files. Can't be read or written to while the SQL process is running. Also, if you could just backup the mdf, you would very likely have a suspect database when you tried to reattach it.

    Ask for permission to run a database backup, ask someone else to run a database backup or, if you insist on copying the files, take the DB offline and then take copies of both the mdf and the ldf files. Bring the DB back online once you're done. Note that the DB will be inaccessible while offline.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 21, 2009 at 3:13 pm

    #1069148

    A backup is the way to go!



    My blog

Viewing 6 posts - 1 through 6 (of 6 total)

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