The only reason for backupThe only reason for the backup is that we can restore when I first became a SQL Server database administrator, as long as the backup work can run successfully, I feel that everything is fine. I'll look at the SQL Server Agent to make sure those jobs are running, and that's it.I think as long as the disaster occurred, I just need to do a recovery. How hard can this be? Theoretically, we useKedella's 5 simple questions to test our backup strategy, and we want to keep in mind the 9 considerations that will lead to the DBA being dismissed. in practice, there are some small problems that will haunt us. When there is a problem, do we want to restore the entire database-that is, only a few tables or a complete database. Some people take the production environment database as the development environment to accidentally delete the database, the next thing is of course everyone is crazy. Thinking ahead of our backup plan can make it easier to deal with the crisis.
to do recovery there? Do not revert to the production server when you restore code (stored procedures, views, triggers, and so on) or tables. I don't like to move the production database, and when we face these accidents-you've had a bad day. That's why you do the restoration, remember? Let's do different things in different environments (development or testing), away from the production environment. I will also write how to restore in development, test, and production environments. When we recover a database safely, we can easily copy the data to other servers. You can access the database on a read-only recovery server in a production environment by connecting the server securely and simply. Also, the data can be directed to the recovery database from the production database through the connection server. Then, if you want to recover more than 10G of data, you may have to recover on the production database to speed up. We have to be very careful and make sure that your scripts and database names are correct-we don't want to overwrite the databases that are working. This may require additional space on the production server, and in an emergency I have to reduce the tempdb and log files to 1M to free up free space. Tempdb is on a very fast disk, and there are no other problems such as a power outage during the recovery process, so this recovery is perfectly done. We're not always good luck, but it helps us come up with more of these situations. A warning: If there is a dependency relationship, such as a table and other tables that have dependencies, and you restore the table without restoring the table that the table depends on, then this can cause damage. We can't talk about all the scenes-really, every situation is different.
Perform recoveryUsing the no recovery option to restore the most recent full backup-this is very important. This causes the database to be in a recovery state, and other backups can be restored. If you forget these two points, then you may need to recover from the beginning again, so be sure to check carefully before you recover. If my recovered code or configuration table has not changed since the last backup, then I will not restore the differential and transaction log backups, and our goal is to complete the restore as soon as possible. Next, restore the transaction log backup after the differential backup is restored (if you don't have a differential backup after the last full backup), again, don't forget the no recovery. It's really bad to do all this with a graphical interface. The more backups you make, the longer you restore and the more error-prone. Instead, you need a script to manage the backup folder, find the corresponding file, and make an automatic reply. In the following training we will discuss the auto-restore module. To learn backup and restore knowledge, we recommend these articles:
- Grant fritchey ' s SQL Server Backup and Restore for the accidental DBA
- Brent ' s DBA nightmare:sql down, No plans
- Jes ' s 3 things need to Start Doing to Your Database Server
Thank! We'll see you next week!?
