Sql2000 backup and log cleanup

Document directory

• 1. Database Backup
• 2 log cleanup

 

1. Database Backup

Go to the Enterprise Manager:Choose Start> program> Microsoft SQL Server> Enterprise Manager to go to the management screen of sqlserver2000, this Enterprise Manager contains almost all the functions for database management.

Manual database backup:For example, expand to the "Database" node, and all installed databases are displayed under the database node. For example, select the database to be backed up "lis2002" and Right-click the lis2002 pop-up menu, select "all tasks" à "backup database" to enter the database backup window.

Backup screen:For example, if you select "Full backup" as the backup mode and "Overwrite existing media" as the rewrite mode, you can click "add" to add a file as the database backup file for the backup target file.

Select backup file:Click "add" to bring up the "Add backup file" window (for example). You can manually enter a file name as the database backup file or select an existing file, after completion, confirm to return to the previous screen.

Start backup:After adding (or selecting) a backup file, you can see that the file we just added is in the "back up to:" list, click "OK" to start backup.

Automatic Backup:If Automatic Backup is required, select the "Schedule" check box on the screen. For example, the system automatically backs up the database at every Sunday by default.

Edit scheduling:To change the automatic backup time and cycle interval, click "…" in the scheduling line. Button to open the scheduling editing window (for example). If we select "appear repeatedly", this automatic backup is performed repeatedly at a certain interval. Modify the backup time, click "change" in.

Change scheduling time:For example, you can choose whether automatic backup is performed every week or every day. For example, we choose to perform automatic backup at on the evening of the first, third, fifth, and fourth day of each week, after completion, press "OK" to return to the previous screen, and then press "OK" to continue to return until the "Enterprise Manager" screen is back, the automatic backup task is completed.

View the task created by the system:If you need to modify or delete a created task in the future, locate the task in the following locations, for example, find the "manage" node in Enterprise Manager, "SQL Server proxy" à "job" in sequence. All the system jobs are displayed on the right of the screen. You can double-click a job to edit it, or right-click the job to delete it.

Services that ensure automatic operation of jobs:We can see that the automatic backup job depends on a service named "SQL Server proxy" of slqserver. If this service is not started, even if an automatic backup job is created, the job is not automatically executed.

How to open the "SQL Server proxy" service. For example, there is an "SQL Server Service Manager" in the lower-right corner of the screen. You can double-click the icon and the Service Manager window will pop up.

Service Manager:For example, you can choose to start, pause, or stop a service, or set whether a service automatically starts when the operating system (OS) is started. Generally, ensure that the "SQL Server" service is automatically started. If you need to automatically execute the backup task, the "SQL Server Agent" service also needs to be automatically started ..

2 log cleanup

Database Composition: The sqlserver database consists of two files, one being a data file and one log file. The data file stores all the data of the system and the log file stores all the modification details of the database; generally, log files are growing much faster than data files. After a while, we need to clear the log files. Otherwise, once the log files exceed the available disk space, this will cause the database service to fail, and all operations on the database will fail.

View the location of the two files in the database:(Such as the picture on the right), go to the Enterprise Manager, find the database you want to view, right-click the pop-up menu, and select "properties ".

Data Files and log files: For example, the data file location, size, and growth rule are displayed. Similarly, you can switch to the transaction log page to view the log file information.

Clear logs:As shown in the figure on the right, select data "lis2002", right-click the menu, and select "all tasks"> "detach Database ".

Separate database:If other programs are currently connected to the database, click "clear" to clear all connections, and then press "OK" to complete database separation.

Delete Database Log Files: After the database is detached, you can directly Delete the log files of the database.

Additional database: For example, select "Database"> "all tasks"> "Attach Database". The additional database window is displayed.

Select the data file and additional Database NameFor example, click OK to add the database that has just been detached to the current sqlserver server.

After all the logs are complete, the system re-creates the log file. The size of the newly created log file is 1 kb. Wait until it reaches a very large size and then execute the above log clearing process.

3
Set the database to not record logs

You can set the following settings so that the database does not record log files, so that the log files are automatically cleared during daily automatic backup. The specific steps are as follows:

1. Select "tool" à "SQL query analyzer" in the right figure to open the query analyzer window, as shown in.

2. In the query window, enter the command line: backup log lis2002 with no_log, and press F5 to run the command to set the lis2002 database to a database without log records (for example ).