Start/stop the SQL Server service using a batch processing script

Cause

 

Write this article firstArticleThe reason is described in two simple cases.

Case 1: In a Windows operating system, 10 SQL Server instances are installed, and each instance has about 120 databases. Now you need to start the services of 10 instances (SQL Server & SQL Server Agent & SQL Server Fulltext search) at the same time ). Through SQL Server Configuration Manager, you need an instance and one service to start one by one. How tedious and clumsy the operation is.

Case 2: in Case 1, all instances are started. Soon, I discoveredProgramIt runs slowly, even if you query data in Microsoft SQL Server Management Studio (msms) manager, it is like crawling a tree. In this case, you need to disable services for SQL Server instances that are not currently used. And then start it when necessary. In some cases, you may encounter services that frequently shut down the instance and start services of another instance to solve the resource shortage problem in windows.

 

Solution

 

To solve the complex operation problems in the above case, you can use the net command to start or stop various sqlserver services, such:

Net start SQLAgent $ sql2005de1 /* Start the SQLAgent service in instance sql2005de1 */ Net stop SQLAgent $ sql2005de1 /* Stop the SQLAgent service in instance sql2005de1 */

 

According to the net command, you can write a batch processing script to start various services of each instance. CopyCodeTo store the "Start & stopsqlserver. Bat" batch file with the suffix bat ":

View code @ Echo off
:
Echo local instance list:
Echo ---------------------------
Echo 1 pc143 \ sql2005de1
Echo 2 pc143 \ sql2005de2
Echo 3 pc143 \ sql2005de3
Echo 4 pc143 \ sql2005de4
Echo 5 pc143 \ sql2005de5
Echo 6 pc143 \ sql2005de6
Echo 7 pc143 \ sql2005de7
Echo 8 pc143 \ sql2005de8
Echo 9 pc143 \ sql2005de9
Echo 10 pc143 \ sql2005de10
Echo ---------------------------
Echo operation:
Echo 1 start the service
Echo 0 stops Service
Echo ---------------------------
Echo.
Set/P var2 = enter the operation Action: [1/0]
Set/P var1 = enter the instance number: [1/2/3/4/5/6/7/8/9/10]
If % var1 % = 1 If % var2 % = 1 goto S1
If % var1 % = 2 if % var2 % = 1 goto S2
If % var1 % = 3 if % var2 % = 1 goto S3
If % var1 % = 4 If % var2 % = 1 goto S4
If % var1 % = 5 if % var2 % = 1 goto S5
If % var1 % = 6 if % var2 % = 1 goto S6
If % var1 % = 7 if % var2 % = 1 goto S7
If % var1 % = 8 If % var2 % = 1 goto S8
If % var1 % = 9 If % var2 % = 1 goto S9
If % var1 % = 10 if % var2 % = 1 goto S10
If % var1 % = 1 If % var2 % = 0 goto T1
If % var1 % = 2 if % var2 % = 0 goto T2
If % var1 % = 3 if % var2 % = 0 goto T3
If % var1 % = 4 If % var2 % = 0 goto T4
If % var1 % = 5 if % var2 % = 0 goto T5
If % var1 % = 6 if % var2 % = 0 goto T6
If % var1 % = 7 if % var2 % = 0 goto T7
If % var1 % = 8 If % var2 % = 0 goto T8
If % var1 % = 9 If % var2 % = 0 goto T9
If % var1 % = 10 if % var2 % = 0 goto T10
Echo.
CLS
Goto:
Echo.
: S1
Net start SQLAgent $ sql2005de1/y
Net start msftesql $ sql2005de1/y
Goto endapp
Echo.
: S2
Net start SQLAgent $ sql2005de2/y
Net start msftesql $ sql2005de2/y
Goto endapp
Echo.
: S3
Net start SQLAgent $ sql2005de3/y
Net start msftesql $ sql2005de3/y
Goto endapp
Echo.
: S4
Net start SQLAgent $ sql2005de4/y
Net start msftesql $ sql2005de4/y
Goto endapp
Echo.
: S5
Net start SQLAgent $ sql2005de5/y
Net start msftesql $ sql2005de5/y
Goto endapp
Echo.
: S6
Net start SQLAgent $ sql2005de6/y
Net start msftesql $ sql2005de6/y
Goto endapp
Echo.
: S7
Net start SQLAgent $ sql2005de7/y
Net start msftesql $ sql2005de7/y
Goto endapp
Echo.
: S8
Net start SQLAgent $ sql2005de8/y
Net start msftesql $ sql2005de8/y
Goto endapp
Echo.
: S9
Net start SQLAgent $ sql2005de9/y
Net start msftesql $ sql2005de9/y
Goto endapp
Echo.
: S10
Net start SQLAgent $ sql2005de10/y
Net start msftesql $ sql2005de10/y
Goto endapp
Echo.
: T1
Net stop MSSQL $ sql2005de1/y
Net stop msftesql $ sql2005de1/y
Goto endapp
Echo.
: T2
Net stop MSSQL $ sql2005de2/y
Net stop msftesql $ sql2005de2/y
Goto endapp
Echo.
: T3
Net stop MSSQL $ sql2005de3/y
Net stop msftesql $ sql2005de3/y
Goto endapp
Echo.
: T4
Net stop MSSQL $ sql2005de4/y
Net stop msftesql $ sql2005de4/y
Goto endapp
Echo.
: T5
Net stop MSSQL $ sql2005de5/y
Net stop msftesql $ sql2005de5/y
Goto endapp
: T6
Net stop MSSQL $ sql2005de6/y
Net stop msftesql $ sql2005de6/y
Goto endapp
: T7
Net stop MSSQL $ sql2005de7/y
Net stop msftesql $ sql2005de7/y
Goto endapp
: T8
Net stop MSSQL $ sql2005de8/y
Net stop msftesql $ sql2005de8/y
Goto endapp
: T9
Net stop MSSQL $ sql2005de9/y
Net stop msftesql $ sql2005de9/y
Goto endapp
: T10
Net stop MSSQL $ sql2005de10/y
Net stop msftesql $ sql2005de10/y
Goto endapp
: Endapp
Set/P var3 = continue operation: [Y/n]
If % var3 % = y goto:

 

 

The following shows the batch processing scripts for starting and stopping 10 SQL Server instances on pc143. Run the script below to start one of the Instance services in pc143, pc143 \ sql2005de4:

 

Disabling the instance service is similar to starting the instance service, for example:

 

 

 

Summary

 

The above batch processing script, although the Code looks very long, in fact, the format is very simple, familiar with Windows batch processing script friends, will be able to simplify it. You can use other background scripting languages, such as Windows powershell or Python, to start or stop an instance. No matter which one is used, the ultimate goal is to solve the two cases at the beginning of the article. Writing a batch processing script allows us to press the keyboard a few times and mouse a few times. Through simple script code implementation, we can replace some tedious and clumsy operations. Over time, we will find that they can save us a lot of time and improve work efficiency.