BACKUP DATABASE AdventureWorks TO AW_TSQL_BackupDevice in .NET

Generating Quick Response Code in .NET BACKUP DATABASE AdventureWorks TO AW_TSQL_BackupDevice

5 After the backup completes successfully, enter the following command to view the header:

Notice that you can tell a lot from this information Look at the BackupStartDate and BackupFinishDate columns By subtracting one from the other, you can identify exactly how many seconds (or minutes, or hours) a backup takes On my system it took about ten seconds 6 Enter the following script to make a change to the database, and view the change:

USE AdventureWorks UPDATE PersonContact Set LastName = 'He-who-can''t-be-named' WHERE LastName = 'Voldemort' GO SELECT * FROM PersonContact WHERE FirstName = 'Lord'

7 Enter the following script to create a differential backup of the database:

BACKUP DATABASE AdventureWorks TO AW_TSQL_BackupDevice WITH DIFFERENTIAL GO RESTORE HEADERONLY FROM AW_TSQL_BackupDevice

Notice that the differential backup is much quicker Using the BackupStartDate and BackupFinishDate columns, I see that it finished in the same second it started meaning the differential backup took less than a second Only one extent (all of 64KB) changed, so that s all that needed to be backed up 8 Enter the following script to make another change to the database, and then view the change:

USE AdventureWorks UPDATE PersonContact Set FirstName = ' ' WHERE FirstName = 'Lord' GO SELECT * FROM PersonContact WHERE LastName = 'He-who-can''t-be-named'

9 Enter the following script to create a transaction log backup of the database:

Notice that another line has been added to the header It now shows that we have three backups on this device the full backup, the differential backup, and the transaction log backup You may have noticed that backups are listed by type in the RESTORE HEADERONLY display Unfortunately, the type is displayed as an integer Here s what some of the integers represent:

It s possible to specify CHECKSUM when doing a backup CHECKSUM requests that the backup operation verify each page and verify the backup as a whole When CHECKSUM is enabled and an error is encountered, the backup fails If you want to allow the backup to continue even if an error is encountered, you can specify the CONTINUE_AFTER_ERROR option TIP If checksum is specified in the backup but you want to ensure that scheduled backups still complete, you must modify the jobs by adding the CONTINUE_AFTER_ERROR option if running the jobs via T-SQL statements If you re using the Backup wizard within SSMS, these options are available in the Reliability section of the Backup options By default, the STOP_ON_ERROR is specified The CONTINUE_AFTER_ERROR option is not available when using the GUI within SSMS to do backups and restores; the only way it can be chosen is by using T-SQL statements In this exercise, we ll create a full backup of the current database using the SSMS Backup wizard Assuming that AdventureWorks was in the state we wanted it to be, we could easily use this backup to restore the AdventureWorks database somewhere else (on another server for example) This demonstrates one way that a database can be moved Exercise 104: Create Backups with the SSMS Backup Wizard

1 If not open, launch SSMS 2 Open the Databases container and right-click AdventureWorks Select Tasks | Backup 3 Ensure the AdventureWorks database is selected For the Backup Set Name, change the name to AdventureWorksToMove

4 In the Destination section, if a destination path exists, click Remove 5 Click Add In the Select Backup Destination dialog box, click the ellipsis button and browse to the root of the C: drive Enter the name of AdventureWorksMoving Click OK, and then click OK again 6 On the Select A Page pane, click Options 7 In the Reliability section, click the check box next to Verify Backup When Finished Click the Perform Checksum Before Writing To Media option and the Continue On Error option 8 Click OK to start the backup 9 Once finished, you can copy the backup file to another server and restore it