Fundamentals of database Development-Lesson Plans-Understanding database backups and transaction log backups

Understanding database backups and transaction log backups

Database backup and log backup are the daily tasks of database maintenance, and the purpose of backup is to restore the loss to the lowest point based on the backup database and transaction log files when the database fails or is compromised.

Database Backup

Database backups can be manually backed up and statement backed up

I. Manually backing up the database

1. Right-click on the database you want to back up-tasks-backup

On the General Options page you can choose whether the backup type is a full database backup or a differential database backup

2. Click the Add option to select the storage path for the database file.

Note the filename is appended with the suffix. bak to facilitate recovery when searching

Database Restore

Right-click Database-Restore Database-Add the database file path that needs to be restored

In the Restore source option you can select ' Source database ', ' source device '. 1. Selecting the Source database tool automatically displays some of the backups that were before the database, and then directly selects the database backup set that needs to be restored.

2. Select the source device click on the following ... to add the database files that need to be restored

2. Click Confirm to restore the database

Separation and addition of databases

I. Overview

SQL Serverprovides a"Separation/additional"database,"Backup/Restore"Database , replication database, and many other database backup and recovery methods. Here is an introduction to a common learning"Separation/additional"methods, similar to those familiar to us"file Copy"method, which is to put the database file (. MDF) and the corresponding log file (. LDFcopy to a different disk for backup, and then copy the two files to any system that needs the database. For example, in the course of experimental teaching, students often want to move the database they created in the school lab computer to their own computer and do not want to recreate the database, you can use this simple method. But because of the particularity of database management system, we need to useSQL Serverprovided tools to do the above, and a direct copy of the file will not work.

This approach involves both the SQL Server Detach Database and the additional database interoperability tools.

1. Detaching a database is to remove a database (such as Student_mis) from the SQL Server Database list so that it is no longer sql The Server manages and uses the database, but the files (. MDF) and the corresponding log file (. LDF) intact. Once the separation is successful, we can put the database file (. MDF) and the corresponding log file (. LDF) is copied to another disk as a backup save.

2, the additional database is a backup disk of the database files (. MDF) and the corresponding log file (. LDF) is copied to the desired computer and added to a SQL server database server, which is used by the server to manage and use the database.

ii. separation of databases

1. After you start SSMS and connect to the database server, expand the server node in Object Explorer. Under Database objects, find the name of the database that needs to be detached, as an example of the student_mis database. Right-click the student_mis database and select Properties from the popup shortcut menu.

2.in the"Database Properties"Window left"Select Page"selected in the following area"Options"object, and then the right area of the"Other Options"list, find"Status"Item, click"Restrict Access"text box, in its drop-down list, select"Single_user".

3. Click OK " " After the database name is added, the display " single user " Task Span style= "Font-family:verdana;" > " Level Two menu item " "

4. The Detach Database window lists the names of the databases we want to detach, and when the parameters are set, click the OK button at the bottom to complete the detach operation for the selected database.

Third, additional database

1. Copy the database files and log files that you want to attach to an already created folder.

2. Right-click the database object and select the attach command from the shortcut menu to open the attach database " window.

3. in the attach database window, click the add button in the middle of the page , Open the window that locates the database file, and in this window select the database file (suffix) you want to attach . MDF)

4. Click " OK " button to complete the installation of additional database files, click the OK button to complete the database additional tasks.

Statement:

1. Change the name of the database

2. Add columns, delete columns if there is data in the table

3. examples of the use of various query statements in SQL Server

4. The statement of the fuzzy query

5. sort statements, sort and check the top three statements

Fundamentals of database Development-Lesson Plans-Understanding database backups and transaction log backups