"Restoring" a user DB using this method, as long as its going back to the same server in the same file locations should be fine, but is not equivalent to a true SQL Server backup. I use this method as an insurance policy for my system databases - the system db's are small and this is a simple way to get the box up if master is corrupted etc. The server would have properly shut down the database while stopping the service, so no concerns about an improper shutdown causing problems. Shutting down the SQL Server service and copying the mdf and ldf of the database will work if you just want to get the database back to the state it was in when you shutdown the service and you are putting it back in the same place. It takes application aware backup and from the image you can restore SQL data alone.
#Youtube using sql backup master how to#
For example, I have a Windows scheduled tasks that tells my MSSQL database to do a full backup. MSSQL knows how to do a stable export of its database, so I let it handle that. Now, having answered your question, I want to point out that if your goal is to get a restorable backup of your database, there are other methods that might accomplish that. I'd want to verify that on a live test system first, however. So, clearly, at some point, the files can be stabilized (quiesced) and used to restart a consistent SQL experience.īased on your question, I'm going to assume you want to create a task that stops the SQL service, copies files, and then restarts the service. If you shut down the server (stopping all services) and reboot it, the SQL database will return to operation. Logically, if you take your question to the extreme, it will work. Im not a SQL expert so wanted to know the best method.ĬheersIt's funny that you've asked this question four times and no one has yet answered. I backup every day the whole of the SQl server but wanted to know if just stopping the SQL service and copying mdf, ldf files could be used in a restore.