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.
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'
WITH FORMAT;GO
Full Recovery Mode in SQL Server 2012
This model backup transaction logs addition to the database.
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;
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO
-- Back up the AdventureWorks2012 database to new media set (backup set 1).
BACKUP DATABASE AdventureWorks2012
TO DISK = 'E:\SQLServer2012Backup\AdventureWorks2012.bak'
WITH FORMAT;
GO
--Create a routine log backup (backup set 2).
BACKUP LOG AdventureWorks2008R2 TO DISK = 'E:\SQLServer2012Backup \AdventureWorks20011.bak';
GO
Creating a maintenance plan:
- In Object Explorer, expand Management.
- Right-click Maintenance Plans>Maintenance Plan Wizard.
- 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:
-
Full database backup is setup, but one of thefilegroup of the database is offline. Since all filegroups are implicitly included in a full database backup, this process will fail.
-
Partial backup is set to run, but a read/write filegroup is offline. Since all read/write filegroups are mandatory for a partial backup, the operation will fail.
-
File backup of specific files are set to backup, but one of the files is offline. This process will failed as you predicted. To successfully back up the online files, you can ignore the the file that is not online from the file list and run the backup again.
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:
-
ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options are now allowed.
-
You cannot shrink a database while backup is running.
-
Most obviously you cannot create or delete databases while backup is running.