Database backup and recovery solution
Recommended tutorial: SQL tutorial
SQL SERVER data Backup plan
The backup methods of SQL SERVER database mainly include full backup, differential backup, transaction log backup, etc. Based on data security requirements, the recommended backup methods are a full backup once a week, a differential backup once a day, and a transaction log backup every half hour.
By default, BACKUP DATABASE and BACKUP LOG permissions are granted to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
Ownership and permission issues with the physical files of the backup device may hinder the backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service is running must have write permissions.
The backup file storage disk needs to be separated from the database file storage disk to avoid disk IO conflicts. The backup execution time is staggered with the database job execution time to prevent the backup from affecting the execution of the database job.
The SQL SERVER maintenance plan function can better realize automated backup. Start the SQL SERVER agent function on the database manager before using this function.
Full backup
1) Select Management-Maintenance Plan-Maintenance Plan Wizard
2) After opening the maintenance plan wizard, you can add a new maintenance plan and name the maintenance plan, as shown below:
3) Select the backup database (complete) maintenance task
4) Set up the database complete maintenance plan related Information, including backup database, backup set expiration time, backup file storage path
5) Select Next and then select Finish
6) Specify the execution cycle for the maintenance plan
As shown in the figure above, the plan type Select Repeat execution, select Weekly as the period, and execute the plan in the early morning of every Sunday.
7) After the backup plan is created, expired backup databases need to be automatically deleted to ensure efficient use of disk space. Create a cleanup maintenance task in the current maintenance plan to delete backup files whose retention time exceeds the set value.
As shown above, open the data backup plan you just created, drag the clear maintenance task from the maintenance plan task list into the box where the backup task is located; and drag The association arrow establishes the association between the backup task and the cleanup task. This association is used to specify the order of execution.
8) Set the folder and file retention period in the clear maintenance task
9 ) The above method is to complete the settings through the maintenance plan wizard, or through the new maintenance plan function. By creating a new maintenance plan, and then dragging the backup database task and clearing the maintenance tasks, the relevant settings are the same as above.
Differential backup
1) Select the maintenance plan-new maintenance plan
2) Set the maintenance plan name: differential backup
3) Open the differential backup plan and drag the backup database task and clear maintenance task to the current scheduled task box.
4) Set the backup type in the backup database task to differential, and set the backup type, back up the specified database, and back up the storage folder
5) Double-click to open the clear maintenance task, and set the folder and file retention period in the clear maintenance task
Transaction log backup
1) Select the maintenance plan - create a new maintenance plan
2) Set the maintenance plan name: transaction log backup
3) Open the differential backup plan and drag the backup Database tasks and clear maintenance tasks to the currently scheduled tasks box.
4) Set the backup type in the backup database task to transaction log, and set the backup type, back up the specified database, and back up the storage folder
5) Double-click to open the clear maintenance task, and set the folder and file retention period in the clear maintenance task
Database recovery Restore full backup
Restore full backup refers to restoring the complete backup file of the database and restoring the database to its complete state.
1) Select to restore the database
2) Under the general options, set the restore Target database name, source device (database complete file), etc.
3) Set the storage path of the restored database file, and choose to overwrite the existing database and roll back uncommitted transactions.
Restore full backup differential backup
This method is to restore the database to a differential backup status. After restoring the full backup, you can continue to restore the differential backup to the target database, which is used to update the data saved in the differential backup into the current database and restore the database to the state at the time of the differential backup.
1) Select the restore database
2) Under the general options, set the restore target database name, source device (database is complete files) etc.
3) Set the storage path of the restored database file, and choose to overwrite the existing database and not roll back uncommitted transactions on the database.
4) After the previous step is completed, the database is in the restoring state; you can continue to restore the database and select the differential backup file for the source device.
#5) Set the file storage path and database transaction log rollback method when restoring differential backup files.
Restore full backup differential backup transaction log backup
This method is to restore the database to the state when the transaction log was backed up. After restoring the full backup, you can continue to restore the differential backup to the target database and then continue to restore the transaction log backup. This is used to update the data saved in the differential backup and transaction log backup into the current database, so that the database can be restored to the state at the time of the transaction log backup.
1) Select the restore database
##2) Under the general options, set the restore target database name and source device (Database complete file) etc.
3) Set the storage path of the restored database file, and choose to overwrite the existing database and not roll back uncommitted transactions on the database.
4) After the previous step is completed, the database is in the restoring state; you can continue to restore the database and select the differential backup file for the source device.
5) Set the file storage path and database transaction log rollback method when restoring differential backup files.
6) After the differential backup and restore is completed, the database is in the restoring state; continue to select restore to restore the transaction log file to the current database.
Choose to restore the transaction log and select the general settings
Restore time point setting; by setting this time point, the database can be restored to any time point from the time of completion to the transaction log backup time period.
Set the option to roll back uncommitted transactions
After the execution is completed, the database returns to the available state and the data is updated to the state at the specified time. If no time is specified, it will be the state when the transaction log was backed up.
The above is the detailed content of Database backup and recovery solution. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1386
52
iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos
Jul 18, 2024 am 05:48 AM
Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps
Detailed tutorial on establishing a database connection using MySQLi in PHP
Jun 04, 2024 pm 01:42 PM
How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())
How to handle database connection errors in PHP
Jun 05, 2024 pm 02:16 PM
To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.
How to connect to remote database using Golang?
Jun 01, 2024 pm 08:31 PM
Through the Go standard library database/sql package, you can connect to remote databases such as MySQL, PostgreSQL or SQLite: create a connection string containing database connection information. Use the sql.Open() function to open a database connection. Perform database operations such as SQL queries and insert operations. Use defer to close the database connection to release resources.
How to use database callback functions in Golang?
Jun 03, 2024 pm 02:20 PM
Using the database callback function in Golang can achieve: executing custom code after the specified database operation is completed. Add custom behavior through separate functions without writing additional code. Callback functions are available for insert, update, delete, and query operations. You must use the sql.Exec, sql.QueryRow, or sql.Query function to use the callback function.
How to handle database connections and operations using C++?
Jun 01, 2024 pm 07:24 PM
Use the DataAccessObjects (DAO) library in C++ to connect and operate the database, including establishing database connections, executing SQL queries, inserting new records and updating existing records. The specific steps are: 1. Include necessary library statements; 2. Open the database file; 3. Create a Recordset object to execute SQL queries or manipulate data; 4. Traverse the results or update records according to specific needs.
PHP connections to different databases: MySQL, PostgreSQL, Oracle and more
Jun 01, 2024 pm 03:02 PM
PHP database connection guide: MySQL: Install the MySQLi extension and create a connection (servername, username, password, dbname). PostgreSQL: Install the PgSQL extension and create a connection (host, dbname, user, password). Oracle: Install the OracleOCI8 extension and create a connection (servername, username, password). Practical case: Obtain MySQL data, PostgreSQL query, OracleOCI8 update record.
How to save JSON data to database in Golang?
Jun 06, 2024 am 11:24 AM
JSON data can be saved into a MySQL database by using the gjson library or the json.Unmarshal function. The gjson library provides convenience methods to parse JSON fields, and the json.Unmarshal function requires a target type pointer to unmarshal JSON data. Both methods require preparing SQL statements and performing insert operations to persist the data into the database.
