Backing up a database is one of the most important things you need to do when having a database depended application. It’s only all of your data in there, right? But often developers and management don’t realize the importance of backups and overall proper backup strategy for the most important side of the business – data and it’s consistency.
How backup works
When you run a backup database command SQL Server performs a Checkpoint on the data pages in memory. Checkpoint means that all transactionally committed dirty pages are written to disk. Dirty pages are simply changed pages in memory that haven’t been written to disk yet. After this, the data on the disk is backed up in one or multiple files depending on your requirements. A backup must be able to be restored to a transactionally consistent state which means that it will also contain the data from the transaction log needed to undo all of the transactions which are running while the backup is taken.
Types of Backups
Full database Backup
This backs up the whole database. In order to have a further differential or transaction log backups, you have to create the full database backup first.
-- Back up the OurTechIdeas.com database as full backup BACKUP DATABASE [OurTechIdeas.com] TO DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Full Database Backup.bak' GO
Differential database backup
Differential database backups are cumulative. This means that each differential database backup backs up all the changes from the last Full database backup and NOT last Differential backup.
-- Back up the OurTechIdeas.com database as differential backup BACKUP DATABASE [OurTechIdeas.com] TO DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Diff Database Backup.dif' GO
Transaction log backup
Transaction log backup isn’t possible under the Simple Recovery model. Two transaction logs can’t contain the same transactions which means that when restoring you have to restore every backup in the order, they were taken.
-- Back up the OurTechIdeas.com database as Transaction log backup BACKUP LOG [OurTechIdeas.com] TO DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Trn Database Backup.trn' GO
Tail log backup
There seems to be a lot of confusion about this one since it’s a new term in SQL Server 2008 (I haven’t heard it being used in SS2k). A Tail log backup is the last Transaction log backup that you make prior to restoring a database. What this means is that if your DB crashes for whatever reason, you have to backup your transaction log so that you can do point in time recovery. This last backup is called Tail log backup. If your data file (MDF) is unavailable you need to use WITH NO_TRUNCATE option. # If your database is in OFFLINE or EMERGENCY state then tail log backup isn’t possible.
-- Back up the OurTechIdeas.com database as tail-log backup BACKUP LOG [OurTechIdeas.com] TO DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-tail-log Database Backup.trn' WITH NO_TRUNCATE GO
Mirrored backups simply write the backup to more than one destination. You can write up to four mirrors per media set. This increases the possibility of a successful restore if a backup media gets corrupted. The following statement gives us two backups that we can restore from.
-- make one backup on d: disk and a mirror on e: disk BACKUP DATABASE [OurTechIdeas.com] TO DISK = 'F:\SQL DATABASE\Gama\Mirror bkp\Mirror01\OurTechIdeas.com_Mirror_1.bak' -- 1st location MIRROR TO DISK = 'F:\SQL DATABASE\Gama\Mirror bkp\Mirror02\OurTechIdeas.com_Mirror_2.bak' -- 2nd location -- create a new mirrored backup set WITH FORMAT GO
Copy-only backups are new in SQL Server 2008 and are used to create a full database or transaction log backup without breaking the log chain. A copy-only full backup can’t be used as a basis for a differential backup, nor can you create a differential copy-only backup.
Script for the full copy-only backup
-- Back up the OurTechIdeas.com database as copy only BACKUP DATABASE [OurTechIdeas.com] TO DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Copy-only backup.bak' WITH COPY_ONLY GO
Script for the transaction copy-only backup
-- Back up the OurTechIdeas.com database as transaction log copy only BACKUP DATABASE [OurTechIdeas.com] TO DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Copy-only backup.trn' WITH COPY_ONLY GO
Result for the full copy-only backup
Result for the transaction copy-only backup
Types of Restore
To determine the state of the database after the store operation, you must select one of the options of the Recovery state panel.
RESTORE WITH RECOVERY
Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Recovers the database after restoring the final backup checked in the Select the backup sets to restore grid on the General page. This is the default option and is equivalent to specifying WITH RECOVERY in a RESTORE statement (Transact-SQL).
-- RESTORE WITH RECOVERY USE [master] RESTORE DATABASE [OurTechIdeas.com_new] FROM DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Full Database Backup.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_new_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_new_Log.ldf' GO
RESTORE WITH NORECOVERY
Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. —Used in Mirroring. Leaves the database in the restoring state. This allows you to restore additional backups in the current recovery path. To recover the database, you will have to perform a restore operation by using the RESTORE WITH RECOVERY option (see the preceding option). This option is equivalent to specifying WITH NORECOVERY in a RESTORE statement. If you select this option, the Preserve replication settings option is unavailable.
-- RESTORE WITH NORECOVERY USE [master] RESTORE DATABASE [OurTechIdeas.com_new2] FROM DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Full Database Backup.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_new2_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_new2_Log.ldf', NORECOVERY GO
RESTORE WITH STANDBY
Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. () —Used in log-shipping. Leaves the database in a standby state, in which the database is available for limited read-only access. This option is equivalent to specifying WITH STANDBY in a RESTORE statement. Choosing this option requires that you specify a standby file in the Standby file text box. The standby file allows the recovery effects to be undone. Standby file: Specifies a standby file. You can browse for the standby file or enter its pathname directly in the text box.
-- RESTORE WITH STANDBY USE [master] RESTORE DATABASE [OurTechIdeas.com_2019] FROM DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Full Database Backup.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_2019_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_2019_Log.ldf', STANDBY = N'F:\SQL DATABASE\Gama\OurTechIdeas.com_RollbackUndo_2019-10-02_10-28-03.bak' GO