Da Notes - 70-028 - B

Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender.

Passing Score: 680, Questions: 52,  Time: 90 Minutes

Da Notes Index 

[ 70-028 - B ] [ 70-028 - C ] [ 70-028 - D ] [ 70-028 - F ] [ 70-028 - H ] [ 70-028 - I ] [ 70-028 - L ] [ 70-028 - M ] [ 70-028 - N ] [ 70-028 - P ] [ 70-028 - R ] [ 70-028 - S ] [ 70-028 - U ] [ 70-028 - W ]

Backing Up Databases

Backup contains :  Schema and file structure, Data , Portions of transactions log file 

Who performs backups :  sysadmin fixed server role, db_owner fixed database role, db_backupoperator fixed database role 

Where to store backups :  Disk file , Tape drive (must be attached locally to SQL Server) , Named Pipes (Third-party software packages) 

When you have to backup :

After modifying master database (e.g. create database, alter database or drop database) 

After modifying msdb database (e.g. sp_logdevice which alters transaction log) 

After modifying model database (e.g. sp_addserver, sp_dropserver and sp_addlinkedserver) 

After creating databases 

After creating indexes 

After clearing transaction log (e.g. BACKUP LOG WITH NO LOG or BACKUP LOG WITH TRUNCATE ONLY) 

After performing nonlogged operations (e.g. bcp, SELECT ... INTO, WRITETEXT, UPDATETEXT) 

The BACKUP statement cannot be performed at the same time as these operations: 

Creating or modifying databases, creating indexes, performing non-logged operations

DBCC CHECKALLOC 

DBCC SHRINKDATABASE 

bcp 

SELECT INTO 

File manipulation 

Creating backup :
Backup to a device

Create the backup device for the full MyNwind backup.

USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_2', 'c:\mssql7\backup\MyNwind_2.dat'
-- Back up the full MyNwind database. To a backup device
BACKUP DATABASE MyNwind TO MyNwind_2

Backup to a temp file 
BACKUP DATABASE MyNwind TO DISK = "c:\temp\mynwind_dump.bak"

MMC
SQL Server -> Database -> Right Click -> All tasks -> Backup Database

BACKUP options :
INIT / NOINIT NOINIT (default) appends backups to file, INIT overwrites any existing data but retains header information

UNLOAD (default) Rewinds and unloads tape (tape drive must locally to SQL Server)

NOUNLOAD Does not rewind and unload tape

BLOCKSIZE Changes physical block size

FORMAT Writes new header to tape (use MEDIANAME and MEDIADESCRIPTION)

SKIP Ignores ANSI tape label

NOSKIP SQL Server is reading ANSI tape label

RESTART Restarts backup from the point of interruption

Types of backup methods :
Full backup Backups Files, Objects, date and portions of transaction logBACKUP DATABASE MyNwind TO MyNwind_1

Differential Backup Saves time in backup and restoreBacks up parts of the database since last full DB backupContains only the last changes to a specific row ==> MMC : SQL Server -> Database -> Right Click -> All tasks -> Backup Log==> TSQL: BACKUP DATABASE MyNwind TO MyNwind_1WITH DIFFERENTIAL

Transaction Log Backup Backs up transaction log from last successfully executed BACKUP LOG Requires a full database backup for restoring

Truncates transaction log ==> MMC : SQL Server -> Database -> Right Click -> All tasks -> Truncate Log ==> TSQL :USE masterEXEC sp_addumpdevice 'disk', 'MyNwindLog1', 'c:\mssql7\backup\MyNwindLog1.dat'BACKUP LOG MyNwind TO MyNwindLog1

Backup with NO_TRUNCATE or NO_LOG before a FULL backup cannot recover changes and does not get recorded.

Clearing Transaction Log TRUNCATE_ONLY removes the inactive portion of transaction log.