Search

Manage Backups and Restore

An organization´s data is one of its most important assets. Planning for backups is easy when your database is very small. They complete quickly and don't take up much storage space. But when your database is mature and grow to gigabytes or terabytes in size, then backups become an entirely different beast.





Develop a backup strategy


A correct backup strategy helps ensure that you incur minimal or no data loss, developing this correct backup strategy is probably the most important task you will have as database administrator. Do not make assumptions about what your organization requires, but instead engage the relevant stakeholders within your organization.


Design a backup strategy


To design a disaster recovery plan your organization's management or business stakeholders will need to define the following three main requirements:

  • Recovery Time Objective (RTO) The RTO defines the maximum allowable downtime following a disaster incident. the goal for the database administrator is to restore normal business operations within this time frame.

  • Recovery Point Objective (RPO) The RPO defines the maximum acceptable amount of data loss following disaster incident. The RPO is commonly expressed in minutes.

  • Recovery Level Objective (RLO) The RLO defines granularity of the data needs to be restored following disaster incident. For SQL Server it could be SQL Server instance, group of database, a database, a set of tables, or a table.

Backup Operations


  • Full A full backup captures everything in the database.

  • Differential A differential backup captures everything in the database since the last full backup.

  • Incremental An incremental backup captures everything in the database since the last incremental backup. In SQL Server it is called a log backup. You need to perform a full backup before you can perform log backups.




To design a backup strategy, you need to take into account a number of factors including:

  • The size of your database A larger database will take longer to backup.

  • The structure of your database files A database that consist of a single primary datafile will be difficult to back up within an appropriate maintenance window as it gets larger.

  • The speed/throughout of the network and storage subsystems involved The underlying hardware and networking infrastructure can substantially impact the duration your backup and restore operations. this will in turn impact your RTO.

Configure database recovery models


In the Simple Recovery Model, the transaction log is automatically purged and kept to a small file size. Because of this, you can't make log backups or use some high availability features such as Always On or Database Mirroring. Most importantly though, in the Simple Recovery Model, you cannot restore your database to an arbitrary point in time. This means that should you need to restore, your database will only be as current as the last full or differential backup, and data loss is a real possibility.


The Full Recovery Model, completely logs every transaction that occurs on the database. By having this information, a backup procedure can arbitrarily choose a point in time and restore the database to the way that it was at that point. It does this, by first restoring a full backup, then replaying the changes recorded in the transaction log, step by step, to modify the data up to the point in time desired. Of course, to do this, the transaction logs, will need to be backed up, in addition to the full database back up.


Recovery Model called Bulk-Logged. This model is similar to the Full Recovery Model, in that a transaction log is kept, but certain transactions regarding, bulk-loading of data are only minimally recorded. This makes the bulk data imports perform quicker and keeps the file size of the transaction log down, but does not support the point in time recovery of the data.


Exercise Zone 1



-- create a new database

CREATE DATABASE RecoveryModelDB;

GO


USE RecoveryModelDB;

GO


-- set the recovery model in SSMS


-- review the recovery model with T-SQL


SELECT name, recovery_model_desc

FROM sys.databases

WHERE name = 'RecoveryModelDB';

GO


SELECT name, recovery_model_desc

FROM sys.databases

WHERE name = 'model';

GO


-- change the recovery model

ALTER DATABASE RecoveryModelDB SET RECOVERY SIMPLE;

GO


-- clean up the server, this step is optional

USE TempDB;

GO

DROP DATABASE RecoveryModelDB;

GO


Manage Transaction Log Backups


The primary technique of transaction log management is to periodically perform transaction log backups. A secondary technique is to simply truncate the transaction periodically, typically after a full backup, although it is not commonly used as most organizations want a backup of the log for recoverability purposes. Transaction log backups are also NOT possible in the simple recovery or bulk-logged.


Exercise Zone 2


-- create a new database

CREATE DATABASE TransactionLogDB;

GO


USE TransactionLogDB;

GO


-- review the recovery model with T-SQL

SELECT name, recovery_model_desc

FROM sys.databases

WHERE name = 'TransactionLogDB';

GO

-- set the recovery model if needed

ALTER DATABASE TransactionLogDB SET RECOVERY FULL;

GO


-- add a table and some records to the database

CREATE SCHEMA Warehouse;

GO


CREATE TABLE Warehouse.NewProducts (

ProductID int IDENTITY(1,1) PRIMARY KEY,

ProductName nvarchar(100) NOT NULL

);

GO


INSERT Warehouse.NewProducts

VALUES ('Salted Cashews'),

('Roasted Peanuts'),

('Honey Almonds');

GO


SELECT * FROM Warehouse.NewProducts;

GO


-- create full backup

BACKUP DATABASE TransactionLogDB

TO DISK = 'C:\TempSQL\TransactionLogDB.bak'

WITH FORMAT;

GO


-- add additional records

INSERT Warehouse.NewProducts

VALUES ('Toasted Macadamia'),

('Shelled Pistachios'),

('Whole Walnuts');

GO


SELECT GetDate();

GO


-- add additional record

INSERT Warehouse.NewProducts

VALUES ('Dried Raisins');

GO


SELECT * FROM Warehouse.NewProducts;

GO


-- create transaction log backup

BACKUP LOG TransactionLogDB

TO DISK = 'C:\TempSQL\TransactionLogDB.log';

GO


-- remove the database

USE TempDB;

GO

DROP DATABASE TransactionLogDB;

GO


-- restore from backup with transaction log


-- check the results

USE TransactionLogDB;

GO

SELECT * FROM Warehouse.NewProducts;

GO


-- clean up the server

USE TempDB;

GO

DROP DATABASE TransactionLogDB;

GO


-- also remove the backup file from C:\TempSQL



Need more review files, scripts or examples? OK Let me know here - Email Me and we have a complete zone of documentation, scripts and solutions.


Things that you can find in the Paddock Zone.


  • Perform database snapshots

  • Backup databases to Azure

  • Backup VLDB's

  • Understand transaction log chains.

  • Manage full transaction log incident.

38 views