Full backup, differential backup operation in SQL Server 2000

In SQL Server 2000, assume that we have a database of: Test, which now requires it to be automatically backed up 19:00 every day, and in the event of a database error in the future, we can restore the database to any backup point in time with a backup file.

Backup steps:
1. In SQL Server Enterprise Manager, register the server where the database resides, note that you want to use the SA user name and password, or you will have insufficient permissions to perform the backup schedule at a later time, resulting in a failure to backup.
2. Ensure that the SQL Server Agent service for this server is turned on because all schedules are executed through the agent.
3. In SQL Server Enterprise Manager, select the test database, right-click to open the Backup Database window, specify a new file Test-daily.bak, and choose Full to make a full backup.
4. Open the Backup Database window again, this time using differential backup, the override option set to append to media, the destination file is still the Test-daily.bak specified in the previous step, and is set to 19:00 per day in the schedule option, so that the SQL The server appends the database to the Test-daily.bak file as an incremental backup at 19:00 per day for changes that have occurred since the last backup. (When testing, you can set the backup every 1 minutes per day so that you can see the backup results very quickly)

When a database recovery is required, you can follow the following restore steps:
1. Create a new database, such as the name back, right-click to open the Restore Database window, select "Restore From Device", and then in the "Select Device ..." In the Test-daily.bak file that is used to select the backup, return to the "Restore Database" window, "Backup number" The default is 1 (which corresponds to the initial full backup in step 3 of backup) and does not have to be changed. In the Options tab, select Force Restore, the most critical step is to select the 2nd or 3rd item in the recovery complete state, which is guaranteed to "restore other transaction logs", so that after the restore, the new database is back to the state of our first full backup, at this time, It is normal that the back database will be in the "loading" or "read-only" state, as we will then need to restore the database to a specified state through the transaction log.
2. Open the Restore Database window again, select Restore from Device, and then in the Select Device ... button, go back to the Restore Database window, and click the "View content ..." icon after "backup number" in the new window, Test-daily.bak. You can see the backup sets that were backed up every day around 19:00 (except the top one is our first full backup set, the other is the incremental backup set for each day), select a backup set that you want to restore, click OK to go back to the main window, and you can see "Restore backup Set" by default with "diff" selected. Click OK again so that the back database is restored to one of our selected backup sets.
The restore steps described above can be repeated until we find a backup set that is exactly needed.

In addition, the restored database name is back, if you want to rename it to Test, you can execute EXEC sp_renamedb ' back ', ' Test ', before renaming the database, you should ensure that no one is using the database, and the database is set to single-user mode.

Add: You need to select "Overwrite existing media" in the "Backup Database", "General" tab to set the "Backup set expiry time" in the "Options" tab, and find that after setting the "expiry time", even if "Overwrite existing media" is changed to "Append to Media", set " The expiration time is still valid, and in this way it should be possible to keep a backup of the last n days.