How can I copy the SQL Server Database Data File (mdf)

  • Hello,

    I have a database on the server MyDatabase.mdf . When I try to copy the database file it gives me an error cannot copy used by another person or program. But I am sure that No one is using that database as it is just in the testing phase.

    Also I want to copy this file (.mdf) to a CD and then I want to load the database from the CD to my computer at home.

    Kindly suggest.

    Thanks.

  • You can't copy it unless your server is shut down, if it is a production system, the easiest method is to copy the backup file and restore it on your computer

  • The file is constantly open by the sql server process, even if no-one is actually using it at that moment (Unless you have enabled auto-close which I would NOT normally do). So you could stop sql server and then copy the file, but if you do I would take a copy of the log at the same time.

    Then copy the log and data files to the new machine, use sp_attachdb on the destination machine.

    By far the safer option is to take a backup of the database, then restore the backup to the other machine.

    I would also check the legislation in your country with respect to who owns the data and privacy laws, you may not be legally able to copy this db to your home PC, depending on what it contains and who owns it.

    Mike John

  • Mike John (3/25/2009)


    I would also check the legislation in your country with respect to who owns the data and privacy laws, you may not be legally able to copy this db to your home PC, depending on what it contains and who owns it.

    Mike John

    That's a good point. I think I need to practice more of this kind 😀

  • The Problem is when I try to restore it from the backup file (.bak)

    From the Screen:

    Specify the source and location of backup sets to restore:

    I click on the radio button which says "From Device"

    Then I select the bak file from my local hard drive.

    Then from the next option:

    Select the backup sets to restore:

    It gives me the option of the Database on my production server and not my local server.

    How can I change that.

    So basically this is what i want:

    I want to have an identical copy of the database and all the stored procedures from the

    production server to the local server on my machine.

    I took a backup of the production server (.bak) file and copied it to my local machine Hard drive.

    I created a new database on my local machine and now I want to restore the data from this back up file and having all the issues I explained on the top.

    Thanks a lot.

  • I Hope you are trying to restore on your local server. You dont have to create database on your local server just specify it when you are restoring it.

    Try using TSQL which is the easiest way:

    Use master

    GO

    RESTORE DATABASE 'your database name'

    FROM DISK='path of your .bak file'

    GO

    You should run this on your computer.

  • When I run the Command I get the following Error:

    Use master

    GO

    RESTORE DATABASE MSR

    FROM DISK= 'C:\MSR.bak'

    GO

    The backup set holds a backup of a database other than the existing 'MSR' database.

  • RESTORE HEADERONLY FROM DISK='backup file path'

    and use the correct logical file name of the backup.

  • Are you trying to restore a copy of the MSR database OVER TOP of a copy on your local server?

    The general process:

    - Connect to production with SSMS

    - run backup from SSMS

    - copy backup file from production file system to your local file system

    - connect to local instance with SSMS

    - click restore, from device, pick the .bak file you copied.

    - If this replaces an existing copy of the database, you need to check the box that says overwrite the existing database.

  • Krishna (3/25/2009)


    You can't copy it unless your server is shut down, if it is a production system, the easiest method is to copy the backup file and restore it on your computer

    ?????

    If your server's shut down how can you copy ?

    Don't stop your server, or SQL. To copy, you detach the database, copy, then attach at your destination. You also need to re-attach the original.

    But the backup & restore as mentioned is a better idea usually, especially if the original needs to remain active.

  • homebrew01 (3/27/2009)


    Krishna (3/25/2009)


    You can't copy it unless your server is shut down, if it is a production system, the easiest method is to copy the backup file and restore it on your computer

    ?????

    If your server's shut down how can you copy ?

    Why not? How can you not copy?

    You can't copy the .mdf file if the server is online. If you want to copy it over you should make sure that your server is shut down and then copy it over.

  • Don't shut down the Windows host, shut down the SQL Server server instance. Use SSMS, right click, shut down SQL Server.

    Or Control Panel/Services, turn the service off.

  • Hi

    Points to get mdf file.

    1. select the database.

    2. Right click it and select Task

    3 select Detach

    Pls note that where the mdf file of the database is located.

    go to that location copy it and save it to the location you want.

    Note:

    Select the database right click it choose properties

    choose file and note the path where the .mdf and .ldf is location

    regards

    kkrchandru:-P

  • Hi

    Points to get mdf file.

    1. select the database.

    2. Right click it and select Task

    3 select Detach

    Pls note that where the mdf file of the database is located.

    go to that location copy it and save it to the location you want.

    Note:

    Select the database right click it choose properties

    choose file and note the path where the .mdf and .ldf is location

    regards

    kkrchandru:-P

  • Hi

    Points to get mdf file.

    1. select the database.

    2. Right click it and select Task

    3 select Detach

    Pls note that where the mdf file of the database is located.

    go to that location copy it and save it to the location you want.

    Note:

    Select the database right click it choose properties

    choose file and note the path where the .mdf and .ldf is location

    regards

    kkrchandru:-P

Viewing 15 posts - 1 through 15 (of 16 total)

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