SQL Server database backup methods

Source: Internet
Author: User
Tags mdb database

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.