Simple implementation of standby SQL Server database

Why should I back up the database?

In the real it world, we use the server hardware may be because of the use of too long time, and failure;

Windows family servers may be blue-screen or infected, and SQL Server databases may stop running because of misoperation or bugs.

How to effectively back up the SQL Server database to avoid prolonged downtime when the fault really occurs is a task that every system administrator must face.

Second, the simple implementation of standby SQL Server database principle

I'm here to introduce a simple configuration and usage of standby SQL Server that doesn't require much hardware input (just a dedicated or concurrent backup server).

Database full backup and log backup files are copied from the workspace to the backup environment via the MSDOS command (much simpler than setting up the log transfer method in SQL Server), The backup environment then completes the automatic recovery operation based on Xcopy's backup file Setup job (executing some stored procedures).

If this happens, the backup system, of course, needs to be artificially intervened and restored (such as changing the backup machine's IP address and hostname or changing the application's connection database parameters, etc.), and it is inevitable that some data will be lost.

Here is my test environment standby SQL Server backup system diagram:

Iii. Backup and Recovery case introduction

First, we need to know what the maximum downtime the system can afford (if it's 1 hours), what the most data loss can be (if it's 30 minutes), and use it to set the goals for backup and recovery:

A SQL Server database in a working environment (if it is db_test) must be set to a full failover mode;

Then in the database maintenance plan to do a full database backup four o'clock in the morning every day (starting from 0:00 every day, every 20 minutes to do a backup of the database log files, until the 23:59);

The backup directory retains only the full backup and log backup files in the last day, and this directory is shared.