Shulou(Shulou.com)06/01 Report--

After data migration, use SQL Server 2012 to back up the database, and automatically compress the database into rar files after the backup is completed. Statements that have no problems before can only be used for backup, but cannot automatically compress and delete backups, indicating that there is a problem with compression after backup. Copy the statement to the query analyzer to execute the error message, indicating that "RAR.EXE is not an internal or external command, nor is it a runnable program or batch file."

The SQL statement is as follows:

/ * automatically compress the file and delete the original file after the compression is completed * /

Declare @ sqlPathB varchar (150)-data path

Set @ sqlPathB='RAR.EXE a-dw-ep E:\ EFBackup\ EFNETSYS\ EFNETSYS'+rtrim (convert (varchar (20), getdate ()) + .rar'--create a compressed file and storage path.-df or-dw means that the original file is deleted immediately after compression, and there is no path in the compressed package of-ep table.

+'E:\ EFBackup\ EFNETSYS\ EFNETSYS'+rtrim (convert (varchar (20), getdate (), 112)) + '0000.bak'-fetch the compressed file and path

Exec xp_cmdshell @ sqlPathB

First, check the environment variables.

Automatic compression needs to call WinRar's Rar.exe, and my WinRar is installed on D disk. When the above SQL statement is executed, you need to use Rar.exe under the WinRar installation directory. Therefore, the WinRra installation path must be added to the environment variables. After inspection, it was found that the environment variable was forgotten to be added because of the database migration. Add in the following order (take Win server 2008 as an example):

1. Right-click "computer" on the desktop, find "Properties", click "Advanced system Settings", bring up the "system Properties" dialog box, and find "Environment variables".

2. Click "Environment variable", in "system variable", locate and select the "Path" variable, and click "Edit" to open the "Edit system variable" dialog box.

3. In the open dialog box, put the installation path of WinRar at the end of "variable value". Here is D:\ Program Files\ WinRAR. Note that the newly placed path should be separated from other paths by half-width semicolons.

4. After you have added it, make sure to return it all the way.

5. Test whether it is normal or not. In CMD, execute "rar.exe", enter, and the window lists the command usage, indicating that the setting is successful.

After the setting is completed, it should be able to use normally, who knows, after the execution of the SQL statement, it still can not be compressed normally, and the problem is still not solved. Since rar.exe can be executed normally in the DOS command window, it shows that the problem has nothing to do with the compression command. Continue to find the reason.

Second, check the SQL Server settings.

Because you only call the xp_cmdshell component in SQL, now that rar.exe works in the DOS command window, you have to start with SQL Server to find out why. Because database compression backups are performed automatically using the SQL Server agent, let's start here:

1. Open the SQL Server configuration Manager, find the SQL Server service, and view the SQL Server proxy service

2. Suddenly found that the login identity of the SQL Server agent is LocalService.

It is thought that once in the past, there was a problem with the use of SQL due to login identity, and then changed to LocalSyste can be used normally. It is suspected that it is due to insufficient permissions for login identity, resulting in the inability to call rar.exe normally.

3. Modify the login identity of the SQL Server agent to LocalSystem.

Right-click on the SQL proxy service, select Properties, open the SQL Agent Properties dialog box, and in the login identity of the built-in account under the menu, make Local Service more Local System, and then click OK.

The system prompts you to restart the SQL proxy service due to account changes. Just click to restart the service.

After the setup is complete, execute the SQL statement again and find that the compression is successful.

Summary:

When executing the cmd command, xp_cmdshell should not only correctly configure the necessary environment variables, but also give the necessary permissions involved in the execution of the command.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.