Before we get to the backup and restore syntax, its worth noting that the Parallel Data Warehouse (PDW) appliance architecture offers an environment that greatly enhances backup times (due to dedicated storage and network interfaces, see the following post for more information - http://saldeloera.wordpress.com/2012/07/09/lesson-1-of-parallel-data-warehouse-basic-architecture-overview/). Summarized below, the observable backup times for test databases was ~60 Gb/min. Note, this backup test was performed in a low volume test environment; however, initial observations in a production environment revealed a linear average backup rate, or in other words, the 60 Gb/min backup rate remained constant over increasing volumes. Additionally, the web-based administration console was utilized to observe backup times as seen below and can be found here - https://yourPDWAdminHost/LoaderBackup/BackupRestores
.
.
The information below was sourced from the MS helpfile for AU3.
BACKUP DATABASE
Creates a backup of a SQL Server Parallel Data Warehouse (PDW) database.The backup is stored in the G:\Backups directory on the appliance Backup node. Use this statement with RESTORE DATABASE (SQL Server PDW) for disaster recovery, to copy a database from one appliance to another, or to redistribute a database after adding a data rack to your appliance.
A full database backup is a backup of an entire SQL Server PDW database. A differential database backup only includes changes made since the last full backup. A backup of a user database includes database users, and database roles. A backup of the master database includes logins.
Syntax
Create a full backup of a user database or the master database.
BACKUP DATABASE database_name
TO backup_name
[ WITH [ ( ] DESCRIPTION = 'text' [ ) ] ]
[;]
BACKUP DATABASE TESTDB TO TESTDB_20121004;
BACKUP DATABASE TESTDB TO "20121004 TESTDB Full";
The following statement creates a full backup of the master database. This includes appliance logins:
BACKUP DATABASE master TO master;
Create a differential backup of a user database.
BACKUP DATABASE database_name
TO backup_name
WITH [ ( ] DIFFERENTIAL
[, DESCRIPTION = 'text'] [ ) ]
[;]
BACKUP DATABASE TESTDB TO TESTDB_20121004 WITH DIFFERENTIAL;
BACKUP DATABASE TESTDB TO TESTDB_20121004 WITH DIFFERENTIAL,
DESCRIPTION = '20121004 TESTDB Differential';Arguments
- database_name
- The name of the database to be backed up. This can be a user database or the master database.
- backup_name
- The name of the backup. The backup name can be different from the database name. The backup results are stored in the directory G:\Backups\backup_name on the Backup node.backup_name cannot include a path. The statement will fail if G:\Backups\backup_name already exists.If backup_name contains one or more spaces, the name must be enclosed with quotation marks. The quotation mark must be a double quotation mark (“), not a single quotation mark (‘). For more details on permitted backup names, see Object Naming Rules (SQL Server PDW).
- DIFFERENTIAL
- Perform a differential backup of a user database. If omitted, the default is a full database backup. The name of the differential backup does not need to match the name of the full backup. For keeping track of the differential and its corresponding full backup, consider using the same name with full and diff extensions.For example:BACKUP DATABASE Customer to CustomerFull;BACKUP DATABASE Customer to CustomerDiff WITH DIFFERENTIAL;
- text
- Description of the backup. The string can have a maximum of 255 characters. The description is returned with the RESTORE HEADERONLY statement.
General Remarks
A differential backup usually takes less time than a full backup and can be performed more frequently. When multiple differential backups are based on the same full backup, each differential includes all of the changes in the previous differential backup.
If you cancel a BACKUP command, SQL Server PDW will remove the target directory and any files created for the backup.
If a user database has a full backup and a differential backup, each backup has a distinct name. Naming conventions are not enforced for specifying that a full backup and differential backup belong together. You can track this through your own naming conventions. Alternatively, you can track this by adding a description with the WITH DESCRIPTION option, and then using the RESTORE HEADERONLY statement to retrieve the description.
SQL Server PDW uses SQL Server backup technology to backup and restore databases. SQL Server backup options are preconfigured to use backup compression. You cannot set backup options such as compression, checksum, block size, and buffer count.
Backup files are stored on a disk configuration that optimizes backup and restore performance. Therefore, moving a backup set off the Backup node can negatively impact restore performance. This can occur when files are moved to a different logical disk configuration.
Limitations and Restrictions
You cannot perform a differential backup of the master database. Only full backups of the master database are supported.
The BACKUP DATABASE statement requires a Backup node in the SQL Server PDW appliance.
The backup files are stored in a format suitable only for restoring the backup to a SQL Server PDW appliance using the RESTORE DATABASE (SQL Server PDW) statement.
Backup files created with the BACKUP DATABASE statement cannot be used to transfer data or user information to SMP SQL Server databases. For that functionality, you can use the remote table copy feature. For more information, see Remote Table Copy (SQL Server PDW).
Only one database backup or restore can run on the appliance at any given time. SQL Server PDW will queue backup or restore commands until the current backup or restore command has completed.
You can copy a database to a different appliance by copying the backup files to the G:\Backups directory on the Backup node of the target appliance and then performing a restore. However, copying backup files to a different location can negatively impact restore performance. This can occur when the logical disk storage for the backup files varies from the original backup directory.
The target appliance for restoring the backup must have at least as many Compute nodes as the source appliance. The target can have more Compute nodes than the source appliance, but cannot have fewer Compute nodes.
A differential backup is only allowed if the last full backup completed successfully. For example, suppose that on Monday you create a full backup of database Sales and the backup finishes successfully. Then on Tuesday you create a full backup of database Sales and it fails. You cannot then create a differential backup based on Monday’s full backup. You must first create a successful full backup before creating a differential backup.
Locking Behavior
BACKUP DATABASE requires an ExclusiveUpdate lock on the user database. The Control node puts the backup command in a waiting queue until it has obtained all the necessary locks on the Compute nodes. While the user database is being backed up, logins can read from the database, but cannot write to the database. For example, a login can run a SELECT statement, but cannot run an INSERT, UPDATE, or DELETE statement.
Metadata
These dynamic management views contain information about all backup, restore, and load operations completed after the last system restart.
Use the following DMV to view a list of the database backups that are currently available on the Backup node. - sys.dm_pdw_online_backups
Security
The customer is responsible for managing the security of the backup data. This includes managing data security when it resides on the Backup node, and when it is copied or moved to other storage locations. The Backup node administrator can use the Windows operating system user and file permissions to manage the backup file permissions.
RESTORE DATABASE
Restores a SQL Server Parallel Data Warehouse (PDW) user database backup from the Backup node to the SQL Server PDW appliance. Use this statement with BACKUP DATABASE (SQL Server PDW) for disaster recovery or to copy a database from one appliance to another.
To restore the master database, use the Restore Master page in the Configuration Manager Tool. For more information, see Restore the Master Database (SQL Server PDW).
Restoring a database restores a full backup and then optionally restores a differential backup to the appliance. A restore of a user database includes database users, and database roles.
Restoring with the HEADERONLY option returns the header information for a backup. The header includes the text description of the backup. To view the header information, you can also use the sys.dm_pdw_online_backups system view.
Restore a full database backup. RESTORE DATABASE database_name FROM 'full _backup_name' [;] RESTORE DATABASE TESTDB FROM TESTDB_20121004; RESTORE DATABASE TESTDB FROM "TESTDB_20121004 Full"; Restore a full database backup and then a differential backup. RESTORE DATABASE database_name FROM 'differential_backup_name' WITH [ ( ] BASE = 'full_backup_name' [ ) ] [;] RESTORE DATABASE TESTDB FROM TESTDB_20121004; RESTORE DATABASE TESTDB FROM "TESTDB_20121004 Differential"; Restore header information for a database backup. RESTORE HEADERONLY FROM 'backup_name' [;] RESTORE HEADERONLY FROM TESTDB_20121004 RESTORE HEADERONLY FROM "TESTDB_20121004" '; |
General Remarks
If the backup name contains one or more spaces, enclose the name with double quotation marks. Example: “20090702 Invoices Database Full”.
RESTORE HEADERONLY returns header information about one backup. The results are patterned after the SQL Server RESTORE HEADERONLY results. The row has over 50 columns, which are not all used by SQL Server PDW. For a description of the columns in the RESTORE HEADERONLY results, see sys.dm_pdw_online_backups
Limitations and Restrictions
For these limitations and restrictions, the source appliance is where the backup was created and the destination appliance is where the backup will be restored.
Restoring a database does not automatically rebuild statistics.
The RESTORE DATABASE statement requires a Backup node in the destination SQL Server PDW appliance.
Only one backup or restore statement can be running on the appliance at any given time. If multiple backup and restore statements are submitted concurrently, the appliance will put them into a queue and process them one at a time.
You can copy a database to a different appliance by moving the backup files to the Backup node of the destination appliance, and then performing a restore. However, moving the backup files can negatively impact restore performance if files are moved to a different logical disk configuration.
The backup set can only be restored to a SQL Server PDW destination appliance that has the same number or more Compute nodes than the source appliance. It cannot have fewer Compute nodes than the source appliance.
Locking Behavior
Initiates an Exclusive lock on the name of the database.
Metadata
To view the header information, you can use the sys.dm_pdw_online_backups system view.
