Backup, restore, and detach of SQL Server 2005, additional basic operations

1. Backup

With the database engine connected, I have 2 instances locally, SQL Server, Sqltest, and (1-1).

(Fig. 1-1)

To back up SQL Server, for example, right-click a task--backup, (1-2).

(Fig. 1-2)

The (1-3) interface appears:

"Source"-the database, which defaults to the selected DB instance in figure (1-2).

The "target" default backup file is named "DB instance name. bak".

(Fig. 1-3)

You can modify the backup file name by adding features, click "Add", and the (1-4) interface appears.

(Fig. 1-4)

After clicking on the Select Directory operation, the interface appears (Figure 1-5). Rename the file and add the suffix (otherwise the backed-up file does not have a suffix), and then OK.

(Fig. 1-5)

The file path that appears just renamed, (1-6), is finalized.

(Fig. 1-6)

Click OK, the interface appears (Figure 1-7), the new add to the file path, we generally delete the original default file path (otherwise the backed up database will be divided into these two files), and finally OK.

(Fig. 1-7)

The Backup Success dialog box pops up, (1-8).

(Fig. 1-8)

2. Restore

Refer to (Figure 1-2), if you want to restore the original database, click the name of the database instance to restore, do the following: Right-click the task--------restore database, which appears (Figure 2-1).

Description

"Restored target"-the target database, by default, is the selected DB instance just clicked.

Restored source, the source database is selected by default, which is the most recent database backup file.

If we need to back up to another machine, or the newly installed database, to restore the previous database, select "Source Device", specifically

(Fig. 2-1)

Click OK to appear (Figure 2-2), error message: The database has not been backed up "..." Tail of the log ...

(Fig. 2-2)

Workaround: In (Figure 2-1), do the following: "Select Page"-"Options", check "Overwrite existing database", as (Figure 2-3)

(Fig. 2-3)

Click OK to appear (Figure 2-4)

(Fig. 2-4)

"Source Device" is restored by clicking on the button to the right of the line, which appears (Figure 2-5).

(Fig. 2-5)

Click "Add", appear (Figure 2-6), select the backup data file to restore, click OK.

(Fig. 2-6)

Appear (Fig. 2-7)

(Fig. 2-7)

Once determined, a check appears (Figure 2-8) for the item that appears in the "Select backup set for Restore", and the "Restore" column in front of it indicates that the backup set is restored.

(Fig. 2-8)

Click OK, the same error message appears (Figure 2-2): The database has not been backed up "..., the solution is the same as above, see (Figure 2-3), after the final confirmation, prompt for restore success.

Transition

If you want to restore SQL Server on the DB instance sqltest, the operation is still on the database instance name sqltest.

See Figure Step: (Figure 2-1), if SQL Server is selected in the drop-down list in the source database row (Figure 2-9). 1-2

Restore target-The target database, automatically replaced by SQL Server, select SQL Server in the drop-down list, and then OK.

(Fig. 2-9)

appear (Figure 2-10).

(Fig. 2-10)

At this point (Figure 2-1) (Figure 2-3), click OK, appear (Figure 2-11).

(Fig. 2-11)

Similarly, in (figure 2-9) "restored source", select "Source Device" (Figure 2-5)--(Figure 2-6)--(Fig. 2-7) and so on (figure 2-8), the results are also (Figure 2-11).

Because SQL Server instance is still in use, "Sqlserver.mdf" is being used, resulting in the inability to generate a "sqlserver.mdf" to overwrite itself, but it is also possible to use your own "sqlserver.mdf" file for a SQL Server instance. , this should be a mechanism for SQL Server.

3. Separation

Now solve the problem above, because SQL Server and sqltest two instances are in the same state as the database, and we stop the use of the SQL Server instance at this point.

For SQL Server instances, right-click-to-Task detach, appears (Figure 3-1), and the Delete connection column is optional.

(Fig. 3-1)

When you click OK, it appears (Figure 3-2), at which time the SQL Server instance disappears from the database.

(Fig. 3-2)

If you restore the above operation to Sqltest, there will be no problem. After the restore succeeds, the original "sqltest.mdf" database file will be replaced by "sqlserver.mdf", which means "sqltest.mdf" is lost, and now the "Sqlserver.mdf" instance name is changed to Sqltest, How bored and ridiculous I am ...

4. Additional

Attaching is the addition of a database source file (. mdf) to the data instance, which is quite handy for a machine changer operation.

Attach to "database" right-click, as shown (Figure 4-1)

(Fig. 4-1)

In the interface that appears, select the "sqlserver.mdf" database file that is added in step 3 and then appear (Figure 4-2).

(Fig. 4-2)

When you click OK, the error hint, such as (Figure 4-3), this time, oneself can not cover oneself again, I really dish ah, do not understand very much ...

(Fig. 4-3)

Of course this time you attach other database files that can be successful.

For example, we can detach the instance sqltest at this time, and then attach "sqlserver.mdf" without a problem.

In (Figure 4-1), right-clicking on "Database" also has the "RESTORE Database" option, its operation is to restore the backup database file, we can find that separation and restore can sometimes be common, but the target file is not the same.

Backup, restore, and detach of SQL Server 2005, additional basic operations