Answered by:
Backup MDF File Manually

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.- Marked as answer by Alex Feng (SQL) Thursday, July 14, 2011 2:38 PM
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 AMAnswerer -
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 PMAnswerer -
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 AMAnswerer -
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 AMAnswerer -
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.
KuldeepThursday, 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.- Marked as answer by Alex Feng (SQL) Thursday, July 14, 2011 2:38 PM
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