MSSQL automatic backup and automatic purge log file server settings _mssql

1, daily automatic backup
It is strongly recommended that a conditional user do this!
1, open Enterprise Manager, in the console root directory in turn open Microsoft SQL Server--sql Server group--double hit Open your server
2, then click the tool in the menu above--Select database Maintenance Planner
3, next select the data to be automatically backed up-next to update the data optimization information, here generally do not have to choose-the next step to check the integrity of data, also generally do not choose
4, the next step to specify the database maintenance plan, the default is 1 weeks backup, click on the change to select a daily backup point to determine
5. Next, specify the backup disk directory, select the specified directory, such as you can create a new directory in D disk, such as: Ddatabak, and then choose to use this directory here, if your database is better choose to create subdirectories for each database, and then choose to delete earlier than how many days before the backup, generally set 4-7 days, This depends on your specific backup requirements, the backup file extension is generally bak with the default
6, next specify the transaction log backup plan, see your need to make a choice--the next report to be generated, generally do not choose--next to maintain the plan history, preferably with the default options-the next step to complete
7, this time the database plan has been successfully run, he will follow the settings above you to automatically back up.
Modification Plan:
Open Enterprise Manager, and then click the Microsoft SQL Server--sql Server group in the console root--double-click on your server--manage--database maintenance plan--When you open it, you can see the plan that you have set up to modify or delete it.

Automatically clean up database log files periodically
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 ...). ), and then 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, that is, the periodic cleanup cycle, is no longer described in detail. 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 here, 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.

Sometimes our server is not able to perform security settings, we recommend a backup software
Escort God. Good backup system (support for site file backup, sqlserver,mysql Backup)