Backing up databases

How to perform a full backup of a database

BACKUP DATABASE AdventureWorks
TO DISK = ‘L:\SQLBackups\AW.bak’
WITH INIT;

INIT tells SQL Server to create the file if it doesn’t exist, and to overwrite the existing file if it does
NOINIT tells SQL Server to create the file if it doesn’t exist, and to append the existing file if it does
NOINIT is the default.

or

1. Right-click database, select Tasks and Backup
2. In General pane ensure Recovery model is set to FULL and check Destination
3. Ensure Backup type is set to Full
4. In Options ensure Continue on error is checked
5. Click OK

How to perform a differential backup of a database

BACKUP DATABASE AdventureWorks
TO DISK = ‘L:\SQLBackups\AW.bak’
WITH DIFFERENTIAL, INIT;

or

1. Right-click database, select Tasks and Backup
2. In General pane ensure Recovery model is set to FULL and check Destination
3. Ensure Backup type is set to Differential
4. In Options ensure Continue on error is checked
5. Click OK

How to perform a transaction log backup

BACKUP LOG AdventureWorks
TO DISK = ‘L:\SQLBackups\AW.bak’
WITH NOINIT;

or

1. Right-click database, select Tasks and Backup
2. In General pane ensure Recovery model is set to FULL and check Destination
3. Ensure Backup type is set to Transaction Log
4. In Options ensure Continue on error is checked
5. In Options under Transaction log select either Truncate the transaction log  or Backup the tail of the log, and leave the database in the restoring state
6. Click OK