PARTIAL Backup in SQL Server Step by Step guide

SQL Server

A Partial backup is a user-initiated backup that backs up only part of the database. Partial backups can be used in scenarios where it is not required to have a full backup of the entire database, but where having an up-to-date partial backup is still important. For example, you might take a partial backup before performing batch operations that modify a large number of rows in several tables.

SQL Server provides two ways to create partial backups:

You can use the BACKUP DATABASE statement to back up selected files or file groups. 

You can use the COPY_ONLY option with this statement to create a COPY_ONLY backup, which does not affect the sequence of SQL Server backups.

You can use the BACKUP LOG statement with the COPY_ONLY option to back up the log file(s) without truncating the log. 

COPY_ONLY backups are not transitionally consistent. For more information, see Copy-only Backups (SQL Server).

The following sections describe how to:

  • Create a partial backup by using the BACKUP DATABASE statement
  • Create a copy-only partial backup by using the BACKUP DATABASE statement with the COPY_ONLY option
  • Work with partial backups in SQL Server Management Studio
  • Creating a Partial Backup by Using the BACKUP DATABASE Statement
  • The BACKUP DATABASE statement can be used to create a partial backup of the database. To create a partial backup, you must specify the name of the file or file group to be backed up. For example, the following statement creates a backup of the primary file group:
  • BACKUP DATABASE AdventureWorks2012
  • TO DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak’
  • WITH FORMAT; 
  • You can also use the BACKUP DATABASE statement to back up multiple files or file groups in a single operation. For example, the following statement backs up both the primary and secondary file groups:
  • BACKUP DATABASE AdventureWorks2012
  • TO DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak’
  • WITH FORMAT,
  • FILEGROUP = ‘PRIMARY’, 
  • FILEGROUP = ‘SECONDARY’;
  • You can also use the BACKUP DATABASE statement to back up all the files in the database:
  • BACKUP DATABASE AdventureWorks2012
  • TO DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak’
  • WITH FORMAT; 
  • Creating a Copy-Only Partial Backup by Using the BACKUP DATABASE Statement with the COPY_ONLY Option
  • The COPY_ONLY option can be used with the BACKUP DATABASE statement to create a copy-only backup. A copy-only backup is a backup that is independent of the sequence of conventional SQL Server backups. For example, you might take a copy-only backup before running a batch operation that makes large changes to the data in the database. 
See also  Unveiling the Advantages Galore of Truck Magnetic Signs for Your Business

The COPY_ONLY option has the following effects:

  • It creates a new differential base for subsequent differential backups.
  • It does not truncate the log.
  • For more information about how to use the COPY_ONLY option, see Copy-Only Backups (SQL Server).
  • Working with Partial Backups in SQL Server Management Studio
  • To work with partial backups in SQL Server Management Studio:
  • In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  • Expand Databases, and then right-click the database that you want to back up.
  • Click Tasks, and then click Back Up.
  • In the Backup type list, select Partial. If you want to create a copy-only backup, also select Copy-only in the Copy-only list.
  • Under the Backup component, select the check boxes for the files or file groups that you want to back up. 

FAQs:

1. What is a partial backup?

A partial backup is a backup of selected files or file groups in a database. Partial backups can be useful when you want to back up only a portion of the database, such as before performing batch operations that modify a large number of rows in several tables.

2. How do I create a partial backup?

There are two ways to create partial backups: using the BACKUP DATABASE statement or using the COPY_ONLY option with this statement. 

Conclusion:

Partial backups are useful when you want to back up only a portion of the database, such as before performing batch operations that modify a large number of rows in several tables. You can create partial backups using the BACKUP DATABASE statement or using the COPY_ONLY option with this statement. Copy-only backups are independent backups that do not affect the sequence of conventional SQL Server backups. You can create copy-only backups by using the COPY_ONLY option with the BACKUP DATABASE statement. 

See also  A Review of MetaTrader 4
By Staccy Minnityy

Leave a Reply

Your email address will not be published.