#1
  1. Loser
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    415
    Rep Power
    11

    Copy mdf files without stopping sql server


    hi.

    how can I copy any mdf file to another folder without stopping sql server?


  2. #2
  3. #3
  4. Loser
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    415
    Rep Power
    11
    detaching isn't what I want

    because when you try to detach a db you get a warning

    "database cannot be detached while there are connections using this database"

    what I what is that

    copying the mdf file mustnt hinder working of the db...

    I think it is not possible
    Last edited by pette.n; January 29th, 2006 at 11:51 AM.
  5. #4
  6. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    538
  7. #5
  8. Loser
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    415
    Rep Power
    11
    yes on-line backup..

    while sql server are running, I want to get mdf file to my local sql server..
  9. #6
  10. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    538
    Please, take note that a database is different from it's datafile(s) and that taking a backup of it and restoring on a different machine is very different to copying the mdf file on another machine.
    See this excellent whitepaper from HP or the MS backup guide
  11. #7
  12. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    Smile


    Originally Posted by pette.n
    detaching isn't what I want

    because when you try to detach a db you get a warning

    "database cannot be detached while there are connections using this database"

    what I what is that

    copying the mdf file mustnt hinder working of the db...

    I think it is not possible

    true
  13. #8
  14. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by pette.n
    hi.

    how can I copy any mdf file to another folder without stopping sql server?



    Steps to follow
    alter database <databassename> set offline

    copy mdf files to another folder

    alter database <databasename> set online

    done
  15. #9
  16. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    101
    Rep Power
    9
    that solution will give down time and it is no difference from detach solution. I would run a backup database, copy backup file to new location and restore database from backup.
    All what you may loose is security settings, but you can script them and run script on new server after restore database

IMN logo majestic logo threadwatch logo seochat tools logo