Capitulo IV: Resultados
Apéndice 5: Especificaciones del trabajo de campo
In this section we discuss how to perform SQL Server backups using Data Protection for SQL Server CLI tool. In the next examples, it is expected that you already have installed and configured Data Protection for SQL.
The next sections will show how to perform similar backups to those performed in 4.6,
“Legacy CLI backups” on page 131 using Data Protection for SQL CLI tool.
4.6.1 Full database backups
Example 4-1 shows an example of full database backup and its output. The next example backs up all databases, and produces the same result as the GUI example shown in 4.2.1,
“Full database backups” on page 114.
Example 4-1 CLI legacy full database backup C:\>tdpsqlc backup * full
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Note: For the next examples we use the default configuration parameters stored in the tdpsql.cfg file. The parameter BACKUPMethod is configured as Legacy.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Connecting to TSM Server as node 'COPPER_SQL'...
Beginning full backup for database master, 1 of 6.
Full: 0 Read: 3230464 Written: 3230464 Rate: 278.47 Kb/Sec Backup of master completed successfully.
Beginning full backup for database model, 2 of 6.
Full: 0 Read: 2184960 Written: 2184960 Rate: 534.51 Kb/Sec Backup of model completed successfully.
Beginning full backup for database msdb, 3 of 6.
Full: 0 Read: 6377216 Written: 6377216 Rate: 1,262.72 Kb/Sec Backup of msdb completed successfully.
Beginning full backup for database ReportServer, 4 of 6.
Full: 0 Read: 3231488 Written: 3231488 Rate: 646.80 Kb/Sec Backup of ReportServer completed successfully.
Beginning full backup for database ReportServerTempDB, 5 of 6.
Full: 0 Read: 2182912 Written: 2182912 Rate: 426.69 Kb/Sec Backup of ReportServerTempDB completed successfully.
Beginning full backup for database SalesDB, 6 of 6.
Full: 0 Read: 2192000 Written: 2192000 Rate: 433.15 Kb/Sec Backup of SalesDB completed successfully.
As discussed in 4.5.1, “Data Protection for SQL Server 5.5 CLI new features” on page 130, it is possible to exclude a database from a backup operation using the /excludedb parameter, as shown in Example 4-2. In this example, we exclude the databases ReportServer and ReportServerTempDB from the backup operation.
Example 4-2 Exclusion of a database in a full database backup operation
C:\>tdpsqlc backup * full /excludedb=ReportServer,ReportServerTempDB IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Starting SQL database backup...
Connecting to TSM Server as node 'COPPER_SQL'...
Beginning full backup for database master, 1 of 4.
Full: 0 Read: 3231488 Written: 3231488 Rate: 353.31 Kb/Sec Backup of master completed successfully.
Beginning full backup for database model, 2 of 4.
Full: 0 Read: 2187008 Written: 2187008 Rate: 398.16 Kb/Sec Backup of model completed successfully.
Beginning full backup for database msdb, 3 of 4.
Full: 0 Read: 7425792 Written: 7425792 Rate: 1,335.74 Kb/Sec Backup of msdb completed successfully.
Beginning full backup for database SalesDB, 4 of 4.
Full: 0 Read: 2192000 Written: 2192000 Rate: 468.92 Kb/Sec Backup of SalesDB completed successfully.
Total SQL backups selected: 6 Total SQL backups attempted: 4 Total SQL backups completed: 4 Total SQL backups excluded: 2 Total SQL backups inactivated: 0
Throughput rate: 604.05 Kb/Sec Total bytes transferred: 15,036,288 Elapsed processing time: 24.31 Secs
4.6.2 Transaction log backups
Example 4-3 shows an example of transaction log backup and its output. We back up the SalesDB transaction log, similar to the GUI example shown in 4.2.2, “Transaction log backups” on page 116.
Example 4-3 CLI legacy transaction log backup C:\>tdpsqlc backup SalesDB log
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Connecting to TSM Server as node 'COPPER_SQL'...
Beginning log backup for database SalesDB, 1 of 1.
Full: 0 Read: 26240 Written: 26240 Rate: 37.08 Kb/Sec
A new Data Protection for SQL Server feature is very useful when performing transaction log backups: the ability to automatically exclude databases using the simple recovery model (or truncate log on checkpoint option) from log backups. With this new feature, you can simply ask Data Protection to perform transaction log backups for all databases from a SQL Server, and it will detect the databases using simple recovery model and automatically exclude them from the backup operation.
Example 4-4 shows an example of this new feature.
Example 4-4 Automatic exclusion of simple recovery model databases from transaction log backup C:\>tdpsqlc backup * log
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Connecting to TSM Server as node 'COPPER_SQL'...
Beginning log backup for database model, 1 of 2.
Full: 0 Read: 1535744 Written: 1535744 Rate: 1,315.57 Kb/Sec Backup of model completed successfully.
Beginning log backup for database SalesDB, 2 of 2.
Full: 0 Read: 223872 Written: 223872 Rate: 193.99 Kb/Sec Backup of SalesDB completed successfully.
4.6.3 Differential backups
Example 4-5 shows an example of differential backup and its output. We back up the SalesDB database, similar to the GUI example shown in 4.2.3, “Differential backups” on page 117.
Example 4-5 CLI legacy differential backup
C:\>tdpsqlc backup SalesDB diff
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Connecting to TSM Server as node 'COPPER_SQL'...
Beginning difffull backup for database SalesDB, 1 of 1.
Full: 0 Read: 1143424 Written: 1143424 Rate: 270.37 Kb/Sec Backup of SalesDB completed successfully.
A new Data Protection for SQL Server feature is very useful when performing differential backups: the ability to automatically exclude the master database from differential backups.
With this new feature, you can simply ask Data Protection to perform differential backup for all databases from a SQL Server, and it automatically will exclude the master database from the backup operation.
Example 4-6 shows an example of automatic exclusion of master database in differential backups.
Example 4-6 Automatic exclusion of master database from differential backups
C:\>tdpsqlc backup * diff
IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Connecting to TSM Server as node 'COPPER_SQL'...
Beginning difffull backup for database model, 1 of 5.
Full: 0 Read: 1138432 Written: 1138432 Rate: 285.94 Kb/Sec Backup of model completed successfully.
Beginning difffull backup for database msdb, 2 of 5.
Full: 0 Read: 2182912 Written: 2182912 Rate: 403.21 Kb/Sec Backup of msdb completed successfully.
Beginning difffull backup for database ReportServer, 3 of 5.
Full: 0 Read: 1134336 Written: 1134336 Rate: 243.19 Kb/Sec Backup of ReportServer completed successfully.
Beginning difffull backup for database ReportServerTempDB, 4 of 5.
Full: 0 Read: 1134336 Written: 1134336 Rate: 246.77 Kb/Sec Backup of ReportServerTempDB completed successfully.
Beginning difffull backup for database SalesDB, 5 of 5.
Full: 0 Read: 1143424 Written: 1143424 Rate: 245.36 Kb/Sec Backup of SalesDB completed successfully.
Total SQL backups selected: 6 Total SQL backups attempted: 5 Total SQL backups completed: 5 Total SQL backups excluded: 1
Throughput rate: 288.53 Kb/Sec Total bytes transferred: 6,733,440 Elapsed processing time: 22.79 Secs
4.6.4 Group backups
Example 4-7 shows an example of group backup and its output. We back up the SalesDB_LA filegroup of SalesDB database, similar to the GUI example shown in 4.2.4, “Group backups”
on page 118.
Example 4-7 Cli legacy group backup
C:\>tdpsqlc backup SalesDB group=SalesDB_LA IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Beginning group backup for database SalesDB, 1 of 1.
Full: 0 Read: 1139968 Written: 1139968 Rate: 266.33 Kb/Sec Backup of SalesDB completed successfully.
Total SQL backups selected: 1 Total SQL backups attempted: 1 Total SQL backups completed: 1 Total SQL backups excluded: 0
Throughput rate: 265.95 Kb/Sec Total bytes transferred: 1,139,968 Elapsed processing time: 4.19 Secs
4.6.5 File backups
Example 4-8 shows an example of file backup and its output. In this example only the file SalesDB_LA_1 is backed up. All other files from the SalesDB_LA filegroup are not included in this backup. It produces the same result as the GUI example shown in 4.2.5, “File backups”
on page 119.
Example 4-8 CLI legacy file backup
C:\>tdpsqlc backup SalesDB file=SalesDB_LA_1 IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Connecting to TSM Server as node 'COPPER_SQL'...
Beginning file backup for database SalesDB, 1 of 1.
Full: 0 Read: 1138304 Written: 1138304 Rate: 264.55 Kb/Sec Backup of SalesDB completed successfully.
Total SQL backups selected: 1 Total SQL backups attempted: 1 Total SQL backups completed: 1 Total SQL backups excluded: 0
Throughput rate: 264.17 Kb/Sec Total bytes transferred: 1,138,304
Note: We recommend you to follow group backups with transaction log backups for all SQL databases you back up.
Elapsed processing time: 4.21 Secs
4.6.6 Set backups
Example 4-9 hows an example of file backup and its output. In this example the filegroup SalesDB_EMEA and the file SALESDB_LA_2 from the filegroup SalesDB_LA are backed up.
It produces the same result as the GUI example shown in 4.2.6, “Set backups” on page 120.
Example 4-9 CLI legacy set backups
C:\>tdpsqlc backup SalesDB set /group=SalesDB_EMEA /file=SalesDB_LA_2 IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Connecting to TSM Server as node 'COPPER_SQL'...
Beginning set backup for database SalesDB, 1 of 1.
Full: 0 Read: 1139456 Written: 1139456 Rate: 281.71 Kb/Sec Backup of SalesDB completed successfully.
Total SQL backups selected: 1 Total SQL backups attempted: 1 Total SQL backups completed: 1 Total SQL backups excluded: 0
Throughput rate: 281.28 Kb/Sec Total bytes transferred: 1,139,456 Elapsed processing time: 3.96 Secs
4.6.7 Tail-log backups
Example 4-10 shows an example of transaction log backup and its output. We back up the SalesDB transaction tail log, similar to the GUI example shown in 4.2.7, “Tail-log backups” on
Note: We recommend you to follow file backups with transaction log backups for all SQL databases you back up.
Note: We recommend you to follow set backups with transaction log backups for all SQL databases you back up.
Example 4-10 CLI legacy tail-log backup
C:\>tdpsqlc backup SalesDB log /trunc=no IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server Version 5, Release 5, Level 0.0
(C) Copyright IBM Corporation 1997, 2007. All rights reserved.
Connecting to SQL Server, please wait...
Starting SQL database backup...
Connecting to TSM Server as node 'COPPER_SQL'...
Beginning log backup for database SalesDB, 1 of 1.
Full: 0 Read: 223872 Written: 223872 Rate: 171.74 Kb/Sec Backup of SalesDB completed successfully.
Total SQL backups selected: 1 Total SQL backups attempted: 1 Total SQL backups completed: 1 Total SQL backups excluded: 0
Throughput rate: 170.93 Kb/Sec Total bytes transferred: 223,872 Elapsed processing time: 1.28 Secs