1 / 6

4. SQL Backup tasks

4. SQL Backup tasks. Objectives Secure the databases and the database server Contents Understanding Backup Terms, Backing Up Databases, Files, Filegroups, and Transaction Logs Practicals Backup your databases Summary. Defining backup Terms.

yates
Download Presentation

4. SQL Backup tasks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 4. SQL Backup tasks • Objectives • Secure the databases and the database server • Contents • Understanding Backup Terms, • Backing Up Databases, Files, Filegroups, and Transaction Logs • Practicals • Backup your databases • Summary

  2. Defining backup Terms • Backup A full or partial copy of a database, transaction log, file, or filegroup forming a backup set. • Backup device The physical file. • Backup file A file that stores a backup set. • Backup media The actual physical media. • Backup set The backup from a single backup operation that resides on backup media. • Media family All media (physical files or tapes) in a media set written by a single backup device for a single backup set. • Media header Provides information about the contents of the backup media • Media set All media involved in a backup operation.

  3. Create a backup device • Comfortable in automated backup setup • Allows you to refer to them using only a logical name • Created using SQL Server Enterprise Manager • expand the Management container, • right-click Backup, • click New Backup Device. • Created using Transact-SQL • Gurka is the logical name to backup device Sp_addumpdevice ’disk’ , ’gurka’ , ’C:\SQLBackups\Full.bak’

  4. Backing Up Databases to backup device • Perform Backups Using SQL Server Enterprise Manager • Good, Quick and dirty • Using the Create Database Backup Wizard • Best for making scheduled backups • Perform Backups Using Transact-SQL • Best performance and most flexible • Backup types: • Perform full database backups • Perform differential database backups • Perform transaction log backups • Perform file or filegroup backups

  5. Using SQL Server Enterprise Manager Directly To back up the Northwind database directly using SQL Server Enterprise Manager 1. In the SQL Server Enterprise Manager console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance, and then expand Databases. 2. Right-click NorthWind, point to All Tasks, and then click Backup Database. The SQL Server Backup - NorthWind dialog box appears with the General tab selected. 3. In the General tab, in the Name text box, type Full NorthWind Database Backup #1. 4. In the Description text box, type Backup Set #1. 5. Click the Add button. The Select Backup Destination dialog box appears. 6. Click the Backup Device option button. 7. Select NorthWindFullBackup from the Backup Device drop-down list, and then click OK. 8. Click the Overwrite Existing Media option button and then click the Options tab. 9. Select the Verify Backup Upon Completion check box. 10. Select the Initialize And Label Media check box. 11. In the Media Set Name text box, type NorthWindBackups. 12. In the Media Set Description text box, type Media for NorthWind Database Backups and then click OK. The Backup Progress dialog box appears displaying the progress of the backup of the NorthWind database. Next, the Verify Progress dialog box appears displaying the progress of the verification of the NorthWind database backup. When the database backup is complete, a SQL Server Enterprise Manager message box appiars. 13. Click OK to close the SQL Server Enterprise Manager message box. 14. Do not close SQL Server Enterprise Manager.

  6. Perform Backups Using Transact-SQL • Performing a Full Database Backup • Performing a Differential Database Backup • Performing a File or Filegroup Backup • Performing a Transaction Log Backup • The name XXgurka is the created backup device. BACKUP DATABASE Northwind TO DISK = ’C:\SQLBackups\Temp.bak’ BACKUP DATABASE Northwind TO Dgurka WITH DIFFERENTIAL BACKUP DATABASE Northwind FILEGROUP = ’SECOND_FG’ TO FGgurka BACKUP LOG Northwind TO TLgurka

More Related