SQL Server 2005 log compression and database backup

1. Log compression

The command is simple.

Dump transaction [Database name] WITH NO_LOG
Backup log [Database name] WITH NO_LOG
Dbcc shrinkdatabase ([Database name])

Example 1

The code is as follows:	Copy code
# Sql2005 log compression "sqcrm2009" for the database "sql2005 management maintenance New t-SQL" Use sqcrm2009; Go Dump transaction sqcrm2009 with no_log Backup log sqcrm2009 with truncate_only; Dbcc shrinkfile (sqcrm2009_log, 1 ); Go

II. Database backup

# Sql2005 data backup "SaasCrm" for database "SQL server proxy New Job"

The code is as follows:	Copy code
Declare @ strPath nvarchar (200) Set @ strPath = replace (convert (nvarchar (19), getdate (), 120 ),':','.') Set @ strPath = 'd:/CRM2_Bak/SaasCRM _ '+ @ strPath +'. Bak' Backup database [SaasCrm] to disk = @ strPath with noinit, nounload, noskip, stats = 10, noformat

3. Create an automatic backup maintenance plan

(1) start SQL server Management Studio and select Manage> maintenance plan in the object Resource Manager window.

 

2) right-click maintenance plan and choose maintenance plan wizard from the shortcut menu.

In the displayed dialog box, click next.

3) the select target server dialog box is displayed, as shown in the figure. In the name text box, enter the name of the maintenance plan. In the description text box, enter the description of the maintenance plan; in the server text box, enter the name of the server you want to use. Finally, select the correct ID card and click next.

(4) The Select maintenance task dialog box is displayed, as shown in the following figure.

You can select a variety of maintenance tasks: Check database integrity, shrink the database, regenerate or organize indexes, update statistics, clear historical records, and execute SQL

Server proxy job, backup database, etc. In this example, select the backup database (complete) check box. The settings for other maintenance tasks are similar.

(5) click Next. The select maintenance task sequence dialog box is displayed, as shown in the following figure. If multiple maintenance tasks exist, you can click the move up and move down buttons to set the order of maintenance tasks. After the settings are complete, click next.

(6) The define task dialog box is displayed, as shown in the following figure. Under Database

Select the name of the database to be backed up from the drop-down list box. In the backup components area, select backup database or backup database file; in the [target] area, you can add backup files and devices, and set whether to append backup data to the backup files. Click next after setting.

(7) The Select plan attribute dialog box is displayed, as shown in the following figure. Click change.

8) the create job plan dialog box is displayed, as shown in the figure. In this dialog box, you can set the time and frequency of backing up the database. After the setting is complete, click OK to return to the following dialog box, click next.

(9) the "select report option" dialog box is displayed, as shown in figure 18.24. In this dialog box, you can choose to manage the maintenance plan report, which can be written to a file, you can also email the database administrator. Click next after setting.

10) The Complete Wizard dialog box is displayed, as shown in the following figure. Click finish to create the maintenance plan.

11) after creating the maintenance plan, check whether the SQL server proxy is started. In the object Resource Manager window, right-click SQL

Server proxy. In the pop-up shortcut menu, select start.