locked
Backup MDF File Manually RRS feed

  • Question

  • Folks, I have card access software program using SQL database on a pc.  Version of sql is desktop 8 edition and pc is off the network.  To backup the database can I just manually copy the .mdf file to a USB stick once I have sql service stopped on the pc  and then reattched if I need to restore it?

    Thanks

    Derek.

    Wednesday, July 6, 2011 11:45 AM

Answers

  • Hi,

    Only copying SQL Server data file(s) such as .mdf file may be not enough, which may lead some data loss. This is because you may has some committed transactions in transaction log but not yet hardened in the data files, therefore I would recommend you copy the transaction log as well. Besides, if you have additional one or more secondary data files, copy that as well.

    As Uri mentioned, you may use BACKUP DATABASE statement to backup the database so that you can restore the database on the same or another SQL Server instance if needed. For more information, see http://technet.microsoft.com/en-us/library/cc966495.aspx.


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Monday, July 11, 2011 3:12 AM

All replies

  • Generaly yes, you can... But it is possible that not properly detached database may or may not be properly restored.. Cannot you issue BACKUP DATABASE ... command on USB stick or local disk and then copy to USB?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 6, 2011 11:50 AM
    Answerer
  • Hey, I have very little experience of sql, where do I enter the command from?  Its the desktop ediiton software.
    Wednesday, July 6, 2011 11:54 AM
  • Actually I found this little program which might do the trick, http://www.simplesql.net/?
    Wednesday, July 6, 2011 11:57 AM
  • See those links for SQL Express

     

    Automating Database maintenance in SQL 2005 Express Edition Part I

    http://www.sqldbatips.com/showarticle.asp?ID=27

     

    Automating Database maintenance in SQL 2005 Express Edition Part II

    http://www.sqldbatips.com/showarticle.asp?ID=29


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 6, 2011 12:04 PM
    Answerer
  • Hi Goalie

    You can download and install SQL Server Management Studio Express and then run BACKUP script from that

    Download links http://www.microsoft.com/download/en/details.aspx?id=22985

    Read the below article on how to backup databases using Management Studio

    http://msdn.microsoft.com/en-us/library/ms187510.aspx

     

     


    Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
    If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"
    Wednesday, July 6, 2011 1:37 PM
  • will sqlexpress maint work on desktop edition database?

     

    Wednesday, July 6, 2011 2:00 PM
  • There  is not Desktop Edition in SQL Server. can you explain what you meant?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 7, 2011 5:51 AM
    Answerer
  • There  is not Desktop Edition in SQL Server. can you explain what you meant?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 7, 2011 5:51 AM
    Answerer
  • Yes, You can copy the MDF file using offline the database and reattached to another machine or same server.

     

    There is no need to do extra excersise for this.


    Kuldeep
    Thursday, July 7, 2011 10:28 AM
  • Hi,

    Only copying SQL Server data file(s) such as .mdf file may be not enough, which may lead some data loss. This is because you may has some committed transactions in transaction log but not yet hardened in the data files, therefore I would recommend you copy the transaction log as well. Besides, if you have additional one or more secondary data files, copy that as well.

    As Uri mentioned, you may use BACKUP DATABASE statement to backup the database so that you can restore the database on the same or another SQL Server instance if needed. For more information, see http://technet.microsoft.com/en-us/library/cc966495.aspx.


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Monday, July 11, 2011 3:12 AM
  • Hi Derek, I guess you want to copy .mdf file because you don't want to trouble yourself with running BACKUP DATABASE command. I'd suggest you to use some simple free backup software like SqlBackupAndFtp (or similar) that will do backup for you. I think in your experience it will be the same as copying files to USB drive, but the result will be more reliable since such programs execute BACKUP DATABASE statement.
    Tuesday, September 23, 2014 12:51 PM