SQL Server 2012 Backup


This article will explain and walk you through backing up SQL Server 2012 databases and restore plans for SQL Server 2012 and security contemplations for SQL Server 2012 backup and restore. The SQL Server 2012 backup and restore feature provides an vital safeguard for shielding critical data stored in your SQL Server 2012 databases in case of a DR event. You want to back up your databases to preserve changes to your data on a steady basis. A well-planned backup strategy will help protect databases against loss of information by hardware and other type of failures. Routine testing and restoring your database to prepare you be SOX compliant and restore quickly if DR event were to occur.

Simple Recovery Mode in SQL Server 2012
Using the simple recovery model, next each backup, the database is unprotected to potential data-loss if a disaster were to occur. The data-loss exposure rises with each update until the next backup, when data-loss disclosure returns to zero and a new cycle of data-loss exposure starts. Below is a diagram from MS that displays data-loss risk for a backup strategy that uses only full database backups.


SQL Server 2011 Data Loss Risk for BackUp



Below is a code that emonstrates how to create a full database.

-- Back up the AdventureWorks2012 database to new-media set.
BACKUP DATABASE AdventureWorks2012
TO DISK = 'E:\SQLServer2012Backup\AdventureWorksSimpleRM.bak'


Full Recovery Mode in SQL Server 2012

This model backup transaction logs addition to the database.

SQL Server 2011 <img src="file:///C|/Documents and Settings/Administrator/My Documents/infotechguyz/SQLServer2011 backuptransactionlogsadditiontothedatabase

Below demonstrates how to create a full database backup by using WITH FORMAT to overwrite any current backups and create brandnew media set.

USE master;
-- Back up the AdventureWorks2012 database to new media set (backup set 1).
BACKUP DATABASE AdventureWorks2012
TO DISK = 'E:\SQLServer2012Backup\AdventureWorks2012.bak'
--Create a routine log backup (backup set 2).
BACKUP LOG AdventureWorks2008R2 TO DISK = 'E:\SQLServer2012Backup \AdventureWorks20011.bak';


Creating a maintenance plan:

  1. In Object Explorer, expand Management.
  2. Right-click Maintenance Plans>Maintenance Plan Wizard.
  3. Follow on screen instructions to create maintenance plan.


Online Database Backup Myth

Any backup process that implicitly or explicitly references data that is offline will fail. Below are a few examples:

Log backup will succeed even if one data files are unavailable unlike filegroups. But if any file comprises bulk-logged alterations made under the bulk-logged recovery model, all the files must be online for backup to be successful.

SQL Server 2012 uses an online-backup method to permit for a database backup while the database is still online. During this backup, most of all operations are possible include INSERT, UPDATE, or DELETE statements. Moreever if you try to start a backup process while a database file is being created or deleted, the backup operation waits until the create or delete process is finished or the backup times out. During a database backup or transaction log backup below are the limitations: