Database Fundamentals (Backup, restore, detach, attach, and statements)

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 Server provides backup and recovery methods for a variety of databases, such as detach/attach database, backup/restore database, copy database, and so on. This article introduces a commonly used "detach/Attach" method in learning, similar to the familiar "file copy" method, that is, the database file (. MDF) and the corresponding log file (. LDF) to the other 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. However, because of the particularity of database management system, it is necessary to use the tools provided by SQL Server to accomplish the above work, and the direct file copy is not feasible.

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

1. Detaching a database is the removal of a database (such as Student_mis) from the list of SQL Server databases so that it is no longer managed and used by SQL Server, but the database file (. MDF) and the corresponding log file (. LDF) are intact. Once the separation is successful, we can put the database file (. MDF) and the corresponding log file (. LDF) Copy to the other disk as a backup save.

2, the additional database is a backup disk of the database files (. MDF) and the corresponding log file (. LDF) to the required computer and add it to a SQL Server database server, which is used by the server to administer 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. Select the Options object in the area below the Select page on the left side of the Database Properties window, and then find the status item in the other options list in the right area, click the Restrict Access text box, and choose Single_user in its drop-down list.

3. After clicking the "OK" button a message box will appear informing us that this operation will close all connections to this database, and after clicking the "Yes" button, the database name is added after the "individual user" is displayed. Right-click the database name, and in the shortcut menu, select the two-level menu item "Detach" for "task".

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 to open the window that locates the database file, where you select the database file (suffix) 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

Database Fundamentals (Backup, restore, detach, attach, and statements)