{"id":54563,"date":"2009-01-21T20:59:00","date_gmt":"2009-01-21T20:59:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2009\/01\/21\/hey-scripting-guy-how-can-i-back-up-a-databases-data-folder-while-the-database-is-running\/"},"modified":"2009-01-21T20:59:00","modified_gmt":"2009-01-21T20:59:00","slug":"hey-scripting-guy-how-can-i-back-up-a-databases-data-folder-while-the-database-is-running","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-back-up-a-databases-data-folder-while-the-database-is-running\/","title":{"rendered":"Hey, Scripting Guy! How Can I Back Up a Database&#8217;s Data Folder While the Database Is Running?"},"content":{"rendered":"<h2><img decoding=\"async\" class=\"nearGraphic\" title=\"Hey, Scripting Guy! Question\" height=\"34\" alt=\"Hey, Scripting Guy! Question\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" width=\"34\" align=\"left\" border=\"0\"> <\/h2>\n<p>Hey, Scripting Guy! I have a problem. I am trying to script the backup of a data folder used by our database application. The problem is that as long as the database service is running, the files that are used are locked, and I am unable to copy them. I have found the service that runs the database, and when I use WMI to stop the service, the script still fails to copy the database files. This is because the database service is too slow when stopping.<\/p>\n<p>I decided to include a <b>Start-Sleep<\/b> command, and this works sometimes. The problem here is that sometimes the database service will stop quickly, and other times it takes quite a few minutes. I guess it is performing lazy writes, and it all depends on how heavily the application was being used. I could do several trial runs, and then add 10 percent to the maximum length of time it takes for the service to shut down, but then that is a waste of time for those occasions when the service would shut down really fast. Is there something you can do, some piece of scripting magic you can give me? <\/p>\n<p>&#8211; CC<\/p>\n<p><img decoding=\"async\" height=\"5\" alt=\"Spacer\" src=\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\" width=\"5\" border=\"0\"><img decoding=\"async\" class=\"nearGraphic\" title=\"Hey, Scripting Guy! Answer\" height=\"34\" alt=\"Hey, Scripting Guy! Answer\" src=\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" width=\"34\" align=\"left\" border=\"0\"><\/p>\n<p>Hi CC,<\/p>\n<p>It seems as if you have a well thought-out plan. As you have seen, trying to time the shutdown of service can be problematic in even the best of conditions. It all depends on what the server is doing at the time: disk contention issues, processor utilization, and memory flushing. There are tons of things that go on when something is being shut down. It can seem hardly better than a controlled crash. In the past I used to just pause the script, and hope for the best. One day, I came up with the idea of using WMI eventing to help out with the problem.<\/p>\n<table class=\"dataTable\" id=\"E2C\" cellSpacing=\"0\" cellPadding=\"0\">\n<thead><\/thead>\n<tbody>\n<tr class=\"record\" vAlign=\"top\">\n<td class=\"\">\n<p class=\"lastInCell\">This week we will are talking about WMI eventing. For some VBScript examples of these kinds of scripts, you can refer to the eventing section of the <a href=\"http:\/\/www.microsoft.com\/technet\/scriptcenter\/resources\/qanda\/events.mspx\">Hey, Scripting Guy! archive<\/a>. Additional information can be obtained from <a href=\"http:\/\/www.microsoft.com\/technet\/scriptcenter\/guide\/sas_wmi_kzcp.mspx?mfr=true\">this Web page<\/a>. The Microsoft book, <a href=\"http:\/\/www.microsoft.com\/MSPress\/books\/authors\/auth8853.aspx\">Microsoft Windows Scripting with WMI: Self-Paced Learning Edition<\/a>, has an entire chapter on WMI eventing.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"dataTableBottomMargin\"><\/div>\n<p>The <b>StopServiceMonitor.ps1<\/b> script illustrates the use of WMI eventing to determine how long to wait before executing a command. The script stops a service, and then it creates a WMI event monitor to detect when the service is completely stopped. After the service is stopped, you can then add in your code to copy your database, or whatever you want to use the script for. Here is the&nbsp;script: <\/p>\n<pre class=\"codeSample\">Clear-Host\n$computer = \"localhost\"\n$dte = get-date\n$ServiceName = \"'AdobeActiveFileMonitor6.0'\"\n$Query = \"select * from __instanceModificationEvent within 5 where\n targetInstance isa 'win32_Service' AND targetInstance.name = $ServiceName\"\nWrite-Host \"Waiting for $ServiceName to be terminated ...\nYou will be notified within 5 seconds of  service  termination\n start time was $dte\"\n$WMI=Get-WmiObject -Class Win32_Service -computername $computer `\n      -filter \"name=$Servicename\"\nif($WMI.acceptstop)\n  {\n  $Eventwatcher = New-Object management.managementEventWatcher $Query\n  $rtn=$WMI.stopservice()\n  \"Stopping $ServiceName returned $($rtn.ReturnValue)\"\n  $Event = $Eventwatcher.waitForNextEvent()\n  $Event.targetInstance |\n  Format-List -property [a-z]*\n }\nELSE {\n      \"$ServiceName unable to accept stop command\"\n      exit\n     }\n<\/pre>\n<p>The first thing we do is use the <b>Clear-Host<\/b> function to clear off the Windows PowerShell console. To do this, we simply call the <b>Clear-Host<\/b> function as seen here: <\/p>\n<pre class=\"codeSample\">Clear-Host<\/pre>\n<p>Now we need to assign some values to a couple of variables. We use the <b>$computer<\/b> variable to hold the name of the computer we wish to monitor. We use the variable <b>$dte<\/b> to hold the current <b>date-time<\/b> stamp, and the <b>$ServiceName<\/b> variable to hold the name of the service we wish to monitor. This is shown here: <\/p>\n<pre class=\"codeSample\">$computer = \"localhost\"\n$dte = get-date\n$ServiceName = \"'AdobeActiveFileMonitor6.0'\"\n<\/pre>\n<p>Next we create our WMI event query. Today we are looking for an <b>__instanceModificationEvent<\/b>. This is an event that will be triggered when something is modified. Yesterday we used an <b>__instanceDeletionEvent<\/b> to monitor when a process went away, and on Monday we used an <b>__instanceCreationEvent<\/b> to monitor when a process was created. The difference here is that a service that is defined always exists. The thing that is changing is its state (in other words, is it running or is it stopped?). This means the service is being modified, not created and deleted as in the case of a process. It is a subtle distinction, but one that is vital if the script is going to work. <\/p>\n<p>The other thing that is new about our WMI event query is that we are using a compound query. We are, of course, looking for changes in a service state, but we are also interested in only one specific instance of the service. The one whose name we have identified in the <b>$ServiceName<\/b> variable. <b>TargetInstance<\/b> gets created when the event is triggered. It holds a copy of the object in question. This means we can use any property from the class we are monitoring.<\/p>\n<p>The WMI event query is seen&nbsp;here:<\/p>\n<pre class=\"codeSample\">$Query = \"select * from __instanceModificationEvent within 5 where\n targetInstance isa 'win32_Service' AND targetInstance.name = $ServiceName\"\n<\/pre>\n<p>When we have defined our WMI event query, we now want to print a message to the screen to let the user know that the script is doing something. To do this, we use the <b>Write-Host<\/b> cmdlet as illustrated&nbsp;here: <\/p>\n<pre class=\"codeSample\">Write-Host \"Waiting for $ServiceName to be terminated ...\nYou will be notified within 5 seconds of  service  termination\n start time was $dte\"\n<\/pre>\n<p>Then we do a plain old everyday WMI query. The reason to do this is we want to see if the service we are getting ready to stop will accept a <b>stop<\/b> command. This is one way of reducing errors. We use the <b>Get-WmiObject<\/b> cmdlet and specify the class we are querying is the <b>Win32_Service<\/b> WMI class. We also select the computer whose name is stored in the <b>$computer<\/b> variable. We use the <b>filter<\/b> parameter to limit the results to only services specified in the <b>$ServiceName<\/b> variable. This query is shown here:<\/p>\n<pre class=\"codeSample\">$WMI=Get-WmiObject -Class Win32_Service -computername $computer `\n      -filter \"name=$Servicename\"\n<\/pre>\n<p>We now want to stop the service if it will accept a <b>stop<\/b> command. For this example script, we are using a service that does not have any dependencies. If we were to choose a service with dependencies, and if the service accepted a <b>stop<\/b> command, we would receive error 3 in return, which means that dependent services are running. In our case we are not worrying about that because we know that our service has no dependencies. If it will accept a <b>stop<\/b> command, we enter the rest of the script. This statement is seen here:<\/p>\n<pre class=\"codeSample\">if($WMI.acceptstop)<\/pre>\n<p>When we make it past the <b>if<\/b> statement, we first create an instance of the <b>ManagementEventWatcher<\/b> .NET Framework class. This class resides in the <b>System.Management<\/b> .NET Framework namespace. To create the <b>ManagementEventWatcher<\/b> class, we give it the WMI event query that we stored in the <b>$query<\/b> variable. We store the resulting <b>ManagementEventWatcher<\/b> in the <b>$EventWatcher<\/b> variable as shown here: <\/p>\n<pre class=\"codeSample\">{\n  $Eventwatcher = New-Object management.managementEventWatcher $Query\n<\/pre>\n<p>After we have created the <b>ManagementEventWatcher<\/b>, we stop the service. To do this, we use the <b>stopService<\/b> method from the <b>managementObject<\/b> we stored as a result of our previous WMI query. When the <b>stopService<\/b> method is called, it returns a value, and we capture the return code in the <b>$rtn<\/b> variable. This is seen here: <\/p>\n<pre class=\"codeSample\">$rtn=$WMI.stopservice()<\/pre>\n<p>Next we display the return code from the <b>method<\/b> call. To do this, we need to use a subexpression, and we use the dollar sign and a set of parentheses to constrain the unraveling effect of the variable inside expanding quotation marks. This is shown here:<\/p>\n<pre class=\"codeSample\">\"Stopping $ServiceName returned $($rtn.ReturnValue)\"<\/pre>\n<p>Now we use the <b>ManagementEventWatcher<\/b> we stored in the <b>$EventWatcher<\/b> variable, and we wait for the next event. To do this, we use the <b>waitForNextEvent<\/b> method. When the <b>ManagementEventWatcher<\/b> detects that the service has changed, the <b>__instanceModificationEvent<\/b> is fired, and we store the returned <b>managementObject<\/b> in the <b>$event<\/b> variable. <\/p>\n<p>One thing to keep in mind: As written, this script works for services that take a bit of time to start and to stop. If you use this script on a fast service or on a fast computer, you will miss the event. But it is after all only a demo. In a real script with a real service, you would only be using this technique on a slow service anyway, so it is a moot point (which has nothing to do with cows by the way):<\/p>\n<pre class=\"codeSample\">$Event = $Eventwatcher.waitForNextEvent()<\/pre>\n<p>We can at this point in your script do whatever we need to do. We could start the service back up, or we could copy files that were locked by the service. In this demo script, we decide to simply print out all the properties of the modified instance of the service. To do this, we retrieve the <b>targetInstance<\/b> property from the <b>managementObject<\/b> and pipeline it to the <b>Format-List<\/b> cmdlet. We select only the properties whose name begins with the letters a through z and are followed by any other characters. This allows us to filter out the system properties (as they all begin with a double underscore)<\/p>\n<pre class=\"codeSample\">$Event.targetInstance |\n  Format-List -property [a-z]*\n }\n<\/pre>\n<p>When the script is run, you will see an output similar to this: <\/p>\n<p><img decoding=\"async\" height=\"357\" alt=\"Image of the output from running the script\" src=\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/hsg\/2009\/january\/hey0121\/Hsg_1_21_09-01.jpg\" width=\"500\" border=\"0\"><\/p>\n<p>&nbsp;<\/p>\n<p>If the service was unable to accept a <b>stop<\/b> command, we print out a message regarding that situation and exit the script as shown&nbsp;here:<\/p>\n<pre class=\"codeSample\">ELSE {\n      \"$ServiceName unable to accept stop command\"\n      exit\n     }\n<\/pre>\n<p>Well, CC, thank you for an interesting question. We hope you enjoyed our little excursion into compound WMI event queries and working with services. We will see you tomorrow, when we continue our discussions about WMI eventing. Until then, take care.<\/p>\n<p>&nbsp;<\/p>\n<p><b>Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hey, Scripting Guy! I have a problem. I am trying to script the backup of a data folder used by our database application. The problem is that as long as the database service is running, the files that are used are locked, and I am unable to copy them. I have found the service that [&hellip;]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,42,3,4,45,6],"class_list":["post-54563","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-events-and-monitoring","tag-scripting-guy","tag-scripting-techniques","tag-windows-powershell","tag-wmi"],"acf":[],"blog_post_summary":"<p>Hey, Scripting Guy! I have a problem. I am trying to script the backup of a data folder used by our database application. The problem is that as long as the database service is running, the files that are used are locked, and I am unable to copy them. I have found the service that [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54563","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=54563"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54563\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=54563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=54563"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=54563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}