Something that a friend on the server needs, because mssql data needs to be backed up frequently.
Something that a friend on the server needs, because mssql data needs to be backed up frequently.
1. Daily automatic backup
Open the Enterprise Manager, choose "manage"> "Database Maintenance Plan", right-click the window on the right, select "New Maintenance Plan", and start the "database maintenance plan wizard "; click "Next" to select the database to be maintained. When maintaining the feature database, select the last single region and select the name of the database to be maintained; "Next": select to update the data optimization information, "Next" to check the database integrity, "Next" to specify the database backup plan, "Next" to specify the backup storage location, and "Next" to specify the transaction log backup plan, "Next" specifies the report, "Next" specifies the maintenance of historical records, and finally sets the maintenance job name. Generally, if you only need to back up database files, you only need to specify the backup plan and storage location, and other projects will not be changed.
During the backup schedule, you need to change the schedule because daily backup is required. Click "change" to edit the scheduling. The occurrence frequency is set to daily. The start time of a job is set to the daily frequency. It is best to select the database access hour, which is usually the midnight time. The specific time can be determined based on the traffic diagram. The duration usually does not need to be changed, the start date is the editing date, and there is no end date.
After editing the preceding maintenance plan, you must check whether the SQL server proxy service is started, because the daily scheduled maintenance plan can be executed only after the service is started. If the service is not started, start it manually. You can view the newly added database maintenance plan in the subitem "job.
2. regularly clear Database Log Files
Database Log Files increase with time. If they are not cleared for a long time, the files become very large. Therefore, they need to be cleared regularly. However, daily files are an important basis for restoring the database, it is unwise not to use log files. Manual cleanup of a single database is good to say, but there are too many databases, or temporarily there is no time to clean up, the hard disk space may be full, affecting access. Therefore, it is more practical to set Automatic cleanup of database log files.
Manual cleaning method: Right-click the database to be cleaned up, select "properties", set the fault recovery model to simple on the "options" card, click OK, and close. Then, right-click the database, "All tasks"-"shrink Database". After confirmation, you can clear the log file. Remember to re-select "properties" and set the fault recovery model to complete.
Automatic cleanup method: the SQL server proxy service is used to execute automatic jobs.
Open the Enterprise Manager, choose "manage"> "SQL server proxy service"> "job", right-click the window on the right, and select "new job ". On the "General" tab, enter the job Name and description. It is recommended that you use sa or the default management account as the owner.
Go to the "Steps" tab, create a job step, enter the step name, type as script, and database as the database for which logs need to be cleared. In the following command, enter the following command:
Dump transaction database name WITH NO_LOG
Dbcc shrinkfile (Database Log File Name, 1)
Enter the name of the database to be maintained in the database name above, and enter the corresponding log file name in the Database Log File Name. Note that it is not the name with a Suffix in the resource manager, but in the Enterprise Manager, the name of the log in the Log tab in the database properties (usually only a suffix ......), Click OK to add a job step. If you need to maintain multiple databases, use the preceding method to repeat the operation steps, pay attention to the actions after each step succeeds or fails, and finally select the start step.
On the "scheduling" tab, enter the scheduling cycle, that is, the cycle of regular cleaning, similar to the backup maintenance plan. If necessary, you can set the notification item after the job is completed on the "Notification" tab at the end. You need to set the operator and set the corresponding service. This is not detailed here. Generally, you do not need ......
