SQL Server Backup & Recovery

Backup

Full Backup: With this backup you are backing up the entire database and will be able to restore the entire database from just this backup. This backup will match the state of the database at the time the backup completes (except for uncommitted transactions which on restore of the backup will be rolled back). Generally this backup takes up the most time and uses up the most space out of all of the different types of backups.

Differential Backup: A differential backup will record all of the data that has changed since the last database backup (differential is the cumulative of all changes since the last full backup) you must have a database backup in place to use a starting point for your differential backup. Like database backups, differential backups will match the state of the database at the time that the backup is completed. Differential backups aren't like transaction log backups because they aren't incremental. When a differential backup occurs, it does not pay any regard to any differential backups that occurred before, it looks back only to the last database backup and records any data changed since that database backup.

Transaction log (Incremental) Backup: Transaction log backups record all transactions that have been recorded against the database since the last transaction log backup. You must have either a differential database backup or a database backup to use as a starting point for your transaction log backups. Unlike database and differential backups, transaction log backups will match the state of the database at the time that the backup is started. These backups use less space and time than database backups, and should be taken frequently to avoid loss of data. Your transaction logs should also be located on a separate disk if possible from your data files.

File Backup: File backups involve backing up individual files within a database. This can be the quickest way to restore, but it also has a lot of overhead associated with it. You must keep track of your file backups as well as use these file backups in conjunction with transaction log backups. Transaction log backups must be performed after a file backup is completed. You will restore your file backups first, and then all transaction log backups that occurred.

Both the differential and transaction log backups requires the Full backup to be restored first before they can be restored.

Permissions Required for Backup and Restore: Any logon that requires permissions to perform backup or restore operations should be provided membership in the following SQL Server roles.
Permissions required for performing backup-
Server Role : sysadmin
DB role : db_backupoperator, dbo_owner
Permissions required for performing restore -
Server role : sysadmin, dbcreator
DB role : db_owner

Creating a Backup Device using T-SQL:
Exec sp_addumpdevice 'device_type', 'logical_name', 'physical_name_or_location'
Exec sp_addumpdevie 'disk', 'Test Backup', 'D:\Backups\Test_Backup.bak'

To Check Transaction Log File being used:
DBCC SQLPERF(LOGSPACE)

Backup Database:
BACKUP DATABASE northwind
TO DISK = 'd:\backups\northwind\nwind.bak'

Full Backup:
BACKUP DATABASE [Inventory]
TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]
WITH

FORMAT,
MEDIANAME = N'InventoryStripeSet',
MEDIADESCRIPTION = N'3 Devices = InventoryBackup1-3',
MEDIAPASSWORD = N'InventoryStripeSetPassword',
RETAINDAYS = 7,
NAME = N'InventoryBackup',
DESCRIPTION = N'Full Database Backup of Inventory',
PASSWORD = N'InventoryBackupFullDBPassword',
NOREWIND, STATS = 10

MediaName, MediaDescription- Every media should have name and description
MediaPassword- If a password is used on the backup, a media set password will need to be supplied for every restore.
Password-This password is for the individual backup, not the media set.
Name, Description-Every backup should have a name and a description.
INIT initializes backup devices only when they are in the correct structure.
RETAINDAYS set the backup retention period. The retention period is set as the number of days that must pass before the backup can be overwritten with INIT.
EXPIREDATE is the date when a backup can use INIT- to the same device- without error
All expiration options are discarded if FORMAT is used or if SKIP is used. If you use FORMAT, it implies the use of SKIP.

Differential Backup:
BACKUP DATABASE MyNwind
TO MyNwind_1 WITH DIFFERENTIAL
GO

BACKUP DATABASE [Inventory]
TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]
WITH

DIFFERENTIAL, NOINIT,
MEDIAPASSWORD = N'InventoryStripeSetPassword',
NAME = N'InventoryDiffBackup',
DESCRIPTION = N'Differential Database Backup of Inventory',
PASSWORD = N'InventoryBackupDiffPassword',
UNLOAD, STATS = 10

Transaction Log/Incremental Backup:
BACKUP LOG MyNwind
TO MyNwind_log1
GO

BACKUP LOG [Inventory]
TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

WITH

NOINIT,
MEDIAPASSWORD = N'InventoryStripeSetPassword',
NAME = N'InventoryTLogBackup',
DESCRIPTION = N'Transaction Log Backup of Inventory',
PASSWORD = N'InventoryBackupTlogPassword',
NOREWIND, STATS = 10

File Backup:
BACKUP DATABASE MyNwind
FILE = 'MyNwind_data_1',
FILEGROUP = 'new_customers',
FILE = 'MyNwind_data_2',
FILEGROUP = 'first_qtr_sales'
TO MyNwind_1
GO

Truncating Log: Transaction Log truncation occurs at-
1) The completion of any BACKUP LOG statement.
2) Every time a checkpoint is processed, provided the database is using the simple recovery model.
Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file.

DBCC SHRINKFILE: DBCC SHRINKFILE allows you to shrink files in the current database. When target_size is specified, DBCC SHRINKFILE attempts to shrink the specified file to the specified size inmegabytes. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, for a 15-MB data file, a DBCC SHRINKFILE with a target_size of 12 causes all used pages in the last 3 MB of the file to be reallocated into any free slots in the first 12 MB of the file. DBCC SHRINKFILE doesn't shrink a file past the size needed to store the data.
For example, if 70 percent of the pages in a 10-MB data file are used, a DBCC SHRINKFILE statement with a target_size of 5 shrinks the file to only 7 MB, not 5 MB.

DBCC SHRINKFILE ( {file_name | file_id }
[, target_size][, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY} ] )

DBCC SHRINKDATABASE: The DBCC SHRINKDATABASE command shrinks all files in a database. The database can't be made smaller than the model database. In addition, the DBCC SHRINKDATABASE command does not allow any file to be shrunk to a size smaller than its minimum/initial size. For example, if you've used 60 MB of a 100-MB database file, you can specify a shrink percentage of 25 percent. SQL Server will then shrink the file to a size of 80 MB, and you'll have 20 MB of free space in addition to the original 60 MB of data.

DBCC SHRINKDATABASE (database_name [, target_percent]
[, {NOTRUNCATE | TRUNCATEONLY} ] )

NOTRUNCATE option causes all the freed file space to be retained in the database files. SQL Server only compacts the data by moving it to the front of the file. The default is to release the freed file space to the operating system.
TRUNCATEONLY option causes any unused space in the data files to be released to the operating system. No attempt is made to relocate rows to unallocated pages. When TRUNCATEONLY is used, target_size and target_percent are ignored.
EMPTYFILE option, available only with DBCC SHRINKFILE, empties the contents of a data file and moves them to other files in the filegroup.

-.-.-.-.-.-.-.-.-.-.-.-
Restore

Database Restore:
RESTORE DATABASE database_name
[ FROM backup_device ]
[ WITH options ]

E.g. Restore Database with New Database name on same Server:
RESTORE DATABASE nwind_new FROM DISK = 'c:\backups\northwind\nwind.bak'
WITH
MOVE 'northwind' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new.mdf'

MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new_log.ldf

File or file group Restore:
RESTORE DATABASE database_name
[FILE = file_name ]
[FILEGROUP = filegroup_name ]
[ FROM backup_device ]
[ WITH options ]

Transaction Log restore:
RESTORE LOG database_name
[ FROM backup_device ]
[ WITH options ]

OPTIONS-
PASSWORD = password
NORECOVERY | RECOVERY | STANDBY = undo_file

RECOVERY: The RECOVERY option should not be specified on any backup except the very last one. When the undo phase is performed with the RECOVERY completion state, no additional backups can be applied. If a database is recovered too early (meaning that you still have backups to apply) then you will need to restart the restore sequence with the first backup.

NORECOVERY: This phase does not allow anyone, including system administrators, to access the database. This option leaves the database nonoperational but able to restore additional backups. NORECOVERY is not the default, but it is always safer to err on the side of NORECOVERY. Perform every restore with NORECOVERY and when you are absolutely certain that you just applied the very last transaction log (usually the tail of the transaction log), recover the database using the RECOVERY option.

STANDBY: This is a special combination of the two. With this recovery completion state specified, SQL Server performs undo yet keeps the transactional information that was undone in a file that can be used when the next log is applied. STANDBY recovery completion state offers the ability to use the database for read activity in between the restores. This allows you to verify the state of the data between restores. This is extremely useful if you are trying to determine when data became damaged. Additionally, this allows you to create a secondary copy of the database that can be used for read activity until the next restore must be performed.

Using RESTORE LABELONLY to view information about the backup media set and the retention period, use RESTORE LABELONLY. If you have used good naming conventions then you should easily be able to see if devices belong to the same media set. Otherwise, you will need to review the MediaFamilyID, which is a GUID, to make sure you have all devices in the parallel striped backup. To see the device label use:
RESTORE LABELONLY FROM BackupDevice

Using RESTORE HEADERONLY To view information about the backups that exist on a multifile backup, use the RESTORE HEADERONLY command. This command works against a single device— even when the device is part of a parallel striped media set. If the device is part of a media set, all devices in that media set will return exactly the same information Lists of backup performed on Backup device: This lists all of the backups that have been performed to this device or these devices.
RESTORE HEADERONLY FROM WITH Password = 'password'
This list following columns-

Backup type-
1 = Database
2 = Transaction Log
4 = File
5 = Differential Database
6 = Differential File

Device Type-
Disk 2 = Logical 102 = Physical
Tape 5 = Logical 105 = Physical
Pipe 6 = Logical 106 = Physical
Virtual Device 7 = Logical 107 = Physical

To verify Validity of backup-
RESTORE VERIFYONLY FROM

Recovery Model:
SQL Server Automatic Recovery:
Recovery Models- Simple, Bulk-logged or Full
USE master
ALTER DATABASE mydatabase SET RECOVERY FULL ;

Full and Bulk-logged Recovery Model: In those recovery models, a sequence of transaction log backups is being maintained. The part of the logical log before the MinLSN cannot be truncated until those log records have been copied to a log backup. NOTRUNCATE, TRUNCATEONLY, EMPTYFILE.

Simple Recovery Model: NO_LOG and TRUNCATE_ONLY are the only BACKUP LOG options that are valid for a database.
MinLSN-minimum recovery log sequence number.

Important things to remember while you Restore Database:
1. When we restore database, we always need to start restore procedure from Full backup followed by Differential backup and/or Transaction log backup.
2. The Differential backup take backup of all changes made since last full backup (and not from last differential backup)
3. The Transaction log backup take log backup of all changes made since last backup (any of full, differential or transaction).
4. To restore database, first restore last Full backup then restore following last differential backup (if any) and lastly all transaction log backup followed by differential backup (of full backup if diff backup is not taken/restored).
5. In restoration procedure, all restores must specify NORECOVERY option except last restore. Otherwise we need to start recovery process again.
6. By NORECOVERY option the database is restored but we cannot read it as there may be some uncommitted transactions. If you want to read/check status of the database after each restore then restore database with STAND BY option. This allows us to read database in between restore procedures.
7. You can provide password to media/backup device for your safety. To restore such password protected backup you need to provide PASSWORD in options of recovery.
8. If the database backup is large and you want to check the status whether restore is going or not, you can specify STATS = percentage option so that, restoration process gets updated on screen.