SQL Server database backup methods

A backup database refers to a copy of a database or transaction log. When the system, disk, or database file is damaged, you can use the backup file for restoration to prevent data loss.

SQL Server database backup supports four types, which are used in different scenarios. The following is a brief introduction.

(1) full backup

Full backup, that is, full database backup, can back up the entire database, including user tables, system tables, indexes, views, stored procedures, and other database objects. This is a common method for most people, but it requires more time and space. Therefore, we recommend that you perform a full backup once a week.

(2) transaction log backup

Transaction log backup is a separate file that records database changes. During backup, you only need to copy the changes made to the database since the last backup, supports quick recovery from databases, differential backup, or file backup, with less time and speed. We recommend that you back up transaction logs hourly or even more frequently.

(3) differential backup

Differential data backup is performed between full database backups, which is smaller than full backup because only the database changed since full backup is included, and the advantage is that the storage and recovery speed is fast. We recommend that you perform differential backup once a day.

(4) file and file group backup

A database generally consists of many files on a hard disk. If the database is very large and cannot be backed up in one night, you can use files and file groups to back up part of the database every night. Generally, the database is not large enough to use multiple file storage, so this backup is not commonly used.

This instance uses the SQLDMO. backup object to back up the entire system database. This allows you to reconstruct the system in case of system or database faults (such as hard disk faults.

The syntax for backing up the entire database is as follows:

Backup database {database_name | @ database_name_var}
TO <backup_device> [,... n]
[
[BLOCKSIZE = {blocksize | @ blocksize_variable}]
[[,] DESCRIPTION = {'text' | @ text_variable}]
[[,] DIFFERENTIAL]
[[,] EXPIREDATE = {date | @ date_var}
| RETAINDAYS = {days | @ days_var}]
[[,] PASSWORD = {password | @ password_variable}]
[[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] Mediadeiterator = {'text' | @ text_variable}]
[[,] MEDIANAME = {media_name | @ media_name_variable}]
[[,] MEDIAPASSWORD = {mediapassword | @ mediapassword_variable}]
[[,] NAME = {backup_set_name | @ backup_set_name_var}]
[[,] {NOSKIP | SKIP}]
[[,] {NOREWIND | REWIND}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] RESTART]
[[,] STATS [= percentage]
]

Backup database parameters and descriptions are as follows:
First, right-click "enterprise manager" and choose "all tasks/backup database" from the shortcut menu"

Then, keep other options unchanged, and click "add" in the "purpose" field ":

In the pop-up menu, select save backup by file, and enter the path and file name you want to save after "file name:

Click "OK" to return to the initial pop-up interface and click "OK" to complete the backup.

Backup files can be migrated anywhere, including other servers. On the new server, you can create a new database by entering the same name as the original database. Do not change any other settings. After the database is created, you do not have to run SQL scripts to generate the database, do not import data from the mdb Database either:

 

After the database is created, right-click the newly created database and select "all tasks/restore Database"

In the displayed window, select "slave device" for the restore type, and click "select device ":

By default, the system is "restored from disk". Click "add ":

In the displayed dialog box that appears, enter (or select) your backup file after "file name:

Click "OK" twice to return to the original window for restoring the database. Select "option" here, select "force restore on the existing database", and click "OK.

OK. Now the success is:

This method can be used to back up and restore databases, or migrate SQL databases to other servers.