MSSQL automatic backup automatically clears log every day

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

1, daily automatic backup

Open Enterprise Manager, go to Administration-database maintenance plan, right click on the right side of the window, select "New Maintenance Plan", start the Database Maintenance Plan Wizard; Click "Next" to select the database to be maintained, to maintain the feature database, select the Last radio box and check the database name to be maintained; " Next, select Update Data optimization information, next check Database integrity, next to specify the database backup plan, next to specify the backup location, next to specify the transaction log backup plan, next to specify the report, "Next" To specify history maintenance, and finally set the maintenance job name; In general, if you only need to back up the database files, you only need to specify the backup plan and the storage location, and other items will not be changed.

When you specify a backup schedule, you need to change the schedule because daily backups are required. Click "Change" to edit the schedule. Frequency of occurrence of a day; daily frequency Select job start time, it is best to select the database traffic hours, mostly in the middle of the night, according to the flow chart to determine the specific time; the duration is usually not changed, the start date is the edit date, no end date.

Once you have edited the maintenance plan above, be aware that the SQL Server Agent service is started because the daily scheduled maintenance plan is to start the service before it can be executed. If the service does not start, you need to start it manually, which is the database maintenance plan that you have just added in the child job.

2. Periodically automatically clean up database log files

Database log files are growing over time, and if you do not clean up for a long time, the files will become particularly large and therefore need to be emptied periodically, but the day to the file is an important basis for restoring the database, and it is unwise not to log files. Manual removal of a single database fortunately, but the database is more, or temporarily did not have time to clean up, may be hard disk space will be full, affect access. Therefore, it is more practical to set up automatic cleanup of database log files.

Manual Cleanup Method: Right-click the database you want to clean up. Select Properties, on the Options tab, set the failover model to simple, OK, and then right click on the database, all tasks-shrink database, clear the log file after confirmation, and finally remember to select Properties again, Set the failover model to full.

Automatic Cleanup method: The same is done by using the SQL Server Agent service to perform automatic jobs.

Open Enterprise Manager, go to administration-SQL Server Agent service-jobs, right-click in the right window, and select New job. General tab, fill in the job name, and note that the owner should preferably use SA or the default administrative account.

Go to the Steps tab, create a new job step, fill in the step name, type as a script, database as the database that needs to be cleaned up, and complete the following command in the bottom command:

DUMP TRANSACTION database name with NO_LOG

DBCC shrinkfile (Database log file name, 1)

The database name above fills in the name of the database to be maintained. Database log file name fill in its corresponding log file name, note, not the Explorer to see the name with the suffix, but Enterprise Manager, database properties in the Log tab in the log name (usually just a suffix name ...), After you have determined, add a job step. If you need to maintain more than one database, repeat the job step with the above method, pay attention to each step after the success or failure of the action, and finally select the first step.

In the Schedule tab, a backup-like maintenance plan that fills out the scheduling cycle, the periodic cleanup cycle, is no longer detailed. If you want, you can set up a notification entry after the job completes on the last Notification tab, you need to set up the operator, and set up the appropriate service, which is not specified, usually not ...

The original is the operation of the above manual, more trouble, find on the Internet with their own practice, summed up the experience of the above, the implementation of a period of time, indeed more worry than before. Archive one. Description This is also my recent server hang a few small station www.ilovemh.cn I love comics, fear of data loss and do strategy www.yulin110.cn rainforest literature