SQL Server database backup

 

Types of backups in the SQL Server

 

a.     Common types of backups:

·        Full

·        Differential

·        Transaction log

·        Tail log backup

b.     Other types of backups:

·        Copy-only backup

·        File backup

·        Partial backup

.     Backup Administration

 

Full Backups

It backs up everything. It is a base backup of any database backup. This is also called a complete database backup which includes all of the objects like tables, indexes, views, procedures, functions etc.

How to create full database backup:

Using T-SQL:

BACKUP DATABASE [AdventureWorks2012]

TO DISK = 'D:\SQL_backup\full\adventure_full_db.bak'

WITH NOINIT,

NAME =N'BACKUP DATABASE - FULL DATABASE BACKUP BAKCUP',

STATS = 10

GO

 

Using GUI Method:

a.      Select database which you want to backup (Select Task and backup)

b.      Select backup type and the location where to store the database backup using “Add” and “backup type” option

c.      Select media option to whether override or append the existing backup

d.      Select Backup options: Backup expiration policy, and compression method

 

 

 

 

 

a.      Select database which you want to backup

 

b.     Select the type and the location where to store the database backup:

 

 

 

 

 

c.      Select media option to whether override or append the existing backup

d.     Select Backup options: Backup expiration policy, and compression method

Click OK

Backup completion message:

 

Full database backup to multiple files:

Sometime we don’t have enough space to do a entire database backup and we are limited to the amount of space so this case we could achieve it by splitting the backup. Additionally, if you would like to backup the database over network this will improve the backup transfer performance.

 

BACKUP DATABASE [AdventureWorks2012] TO

DISK ='D:\SQL_backup\full\adventure_full_db_1.bak',

DISK ='D:\SQL_backup\full\adventure_full_db_2.bak',

DISK ='D:\SQL_backup\full\adventure_full_db_3.bak'

WITH INIT,

NAME ='FULL AdventureWorks2012 backup',

STATS = 5

GO

 

Mirror copies of backup file:

BACKUP DATABASE [AdventureWorks2012]

TO DISK = 'D:\SQL_backup\full\adventure_full_db_org.bak'

MIRROR TO DISK = 'D:\SQL_backup\full\adventure_full_db_mirror.bak'

WITH FORMAT

Note: you can have up to 3 mirror copies of backup

 

 

 

 

 

 

 

 

Differential Backups

A differential database backup is a superset backup of the last successful full database backup and it contains all changes that have been made since last full backup (recent full backup). Generally, the differential will be small in size when compare to full database backup but if there are large number of transactions then the differential backup could be large in size.

A differential backup doesn’t backup everything, the backup usually runs quicker than a full database backup. A differential database backup captures the changed extents at the time of the backup executed. Additionally, if you create a series of differential backup then it’s likely to contain different data in each differential backup.

 

How to create differential database backup:

We can backup differential using three parameters: Database name, backup device, and Differential clause.

Using T-SQL:

BACKUP DATABASE [AdventureWorks2012]

TO DISK = 'D:\SQL_backup\full\adventure_full_db_DIFF.bak' WITH DIFFERENTIAL,

NAME = 'DIFFERENTIAL BACKUP OF AdventureWorks2012'

 

Using GUI:

 

Transaction log backup

The log backup backs up the transaction logs. You could only take this backup type if the database is in full or bulk-recovery model. A transaction log file stores a series of database transactions or modifications in the database. Additionally, a transaction log backup includes all the log records that have not been taken in the previous transaction log backup.

The benefit of transaction log backup is, it allows the database to be recovered to a specific point in time. This clarifies that, the transaction log backups are incremental and differential backups are cumulative.

 

How to create Transaction log backup using T-SQL:

BACKUP LOG [AdventureWorks2012]

TO DISK ='D:\SQL_backup\full\adventure_LOG.trn'

WITH

NAME ='AdventureWorks2012 TLOG BACKUP';

 

Note: You can’t backup only archive log without having at least an incremental or full backup on the same day.

 

Tail log backup:

Tail log backups are very important because it is a tail log backup of the live transaction log. In the event of failure, when you need the database to get back up and in running state, the first step is to take the tail log backup before performing restore and recovery.

 

How to create Tail log backup using T-SQL

USE MASTER

GO

BACKUP LOG [AdventureWorks2012]

TO DISK ='D:\SQL_backup\full\adventure_TAILLOG.log'

WITH CONTINUE_AFTER_ERROR

GO

Here CONTINUE_AFTER_ERROR clause will force the SQL Server to store the log file even though it’s generating an error.

 

 

 

 

 

Copy-only backups

 

A copy-only backup is a special type of full backup, which is independent of the conventional sequence of backups. It doesn’t become a base backup for the next differential backups.

 

How to create copy-only backup using T-SQL:

 

BACKUP DATABASE [AdventureWorks2012]

TO DISK = 'D:\SQL_backup\full\adventure_full_db_COPYONLY.bak'

WITH COPY_ONLY,

NAME ='AdventureWorks2012 COPY ONLY BACKUP';

BACKUP LOG [AdventureWorks2012]

TO DISK ='D:\SQL_backup\full\adventure_full_db_COPYONLYLOG.log'

WITH COPY_ONLY

GO

 

Note: The backup log using doesn’t truncate the transaction log but it just generates the copy-only log backup.

 

Partial backups

Partial backups are mostly used in SIMPLE recovery model databases in order to improve the flexibility when backing up the large databases that contain read-only filegroups.

We use READ_WRITE_FILEGROUP option to backup the database.

 

BACKUP DATABASE [AdventureWorksDW2012] READ_WRITE_FILEGROUPS 

TO DISK ='D:\SQL_backup\full\adventure_PARTIALBACKUP.bkp'

GO

 

File and File Group backups

 

How to backup file using T-SQL:

T-SQL to find list of files in the database:

select name from sys.database_files;

Command to backup files:

BACKUP DATABASE PRD_FINANCE

FILE = 'PRD_FINANCE_1',

FILE = 'PRD_FINANCE_2'

TO DISK = 'D:\SQL_backup\full\adventure_FILE.bkp'

How to backup Filegroup using T-SQL:

T-SQL to list filegroups in the database:

SELECT * FROM sys.filegroups;

 

Command to backup filegroup:

BACKUP DATABASE PRD_FINANCE

FILEGROUP = 'PRIMARY',

FILEGROUP = 'SECONDARY'

TO DISK = 'D:\SQL_backup\full\adventure_FILEGROUP.bkp'

GO

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Automate the database backups using SQL Server Agent

 

 

 

 

 

Backup Administration

 

List the backup details:

T-SQL:

 

SELECT

logical_name,

physical_name,

file_type,

filegroup_name,

file_size,

backup_set_id

FROM msdb.dbo.backupfile

 

Validate the backup file:

Method-1: T-SQL

RESTORE VERIFYONLY FROM DISK ='D:\SQL_backup\full\adventure_full_db.bak'

GO

Method-2: GUI

While doing backup you could check the option “Reliability”

 

 

Database Backups for all databases For Previous Week

 

 

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

   msdb.dbo.backupset.database_name,

   msdb.dbo.backupset.backup_start_date,

   msdb.dbo.backupset.backup_finish_date,

   msdb.dbo.backupset.expiration_date,

   CASE msdb..backupset.type

      WHEN 'D' THEN 'Database'

      WHEN 'L' THEN 'Log'

      END AS backup_type,

   msdb.dbo.backupset.backup_size,

   msdb.dbo.backupmediafamily.logical_device_name,

   msdb.dbo.backupmediafamily.physical_device_name,

   msdb.dbo.backupset.name AS backupset_name,

   msdb.dbo.backupset.description

FROM

   msdb.dbo.backupmediafamily

   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE

   (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)

ORDER BY

   msdb.dbo.backupset.database_name,

   msdb.dbo.backupset.backup_finish_date

 

 

Most Recent Database Backup for Each Database

 

SELECT 

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

   msdb.dbo.backupset.database_name, 

   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date

FROM

   msdb.dbo.backupmediafamily 

   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 

WHERE msdb..backupset.type = 'D'

GROUP BY

   msdb.dbo.backupset.database_name 

ORDER BY 

   msdb.dbo.backupset.database_name

 

 

Most Recent Database Backup for Each Database - Detailed

 

SELECT 

   A.[Server], 

   A.last_db_backup_date, 

   B.backup_start_date, 

   B.expiration_date,

   B.backup_size, 

   B.logical_device_name, 

   B.physical_device_name,  

   B.backupset_name,

   B.description

FROM

   (

   SELECT  

      CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

      msdb.dbo.backupset.database_name, 

      MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date

   FROM

      msdb.dbo.backupmediafamily 

      INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 

   WHERE

      msdb..backupset.type = 'D'

   GROUP BY

      msdb.dbo.backupset.database_name 

   ) AS A

   LEFT JOIN 

   (

   SELECT  

      CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

      msdb.dbo.backupset.database_name, 

      msdb.dbo.backupset.backup_start_date, 

      msdb.dbo.backupset.backup_finish_date,

      msdb.dbo.backupset.expiration_date,

      msdb.dbo.backupset.backup_size, 

      msdb.dbo.backupmediafamily.logical_device_name, 

      msdb.dbo.backupmediafamily.physical_device_name,  

      msdb.dbo.backupset.name AS backupset_name,

      msdb.dbo.backupset.description

   FROM

      msdb.dbo.backupmediafamily 

      INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 

   WHERE

      msdb..backupset.type = 'D'

   ) AS B

   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]

ORDER BY 

   A.database_name

 

 

 

Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours

 

--Databases with data backup over 24 hours old

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

   msdb.dbo.backupset.database_name,

   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,

   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]

FROM

   msdb.dbo.backupset

WHERE

   msdb.dbo.backupset.type = 'D' 

GROUP BY

   msdb.dbo.backupset.database_name

HAVING

   (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE())) 

 

UNION 

 

--Databases without any backup history

SELECT     

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 

   master.sys.sysdatabases.NAME AS database_name, 

   NULL AS [Last Data Backup Date], 

   9999 AS [Backup Age (Hours)] 

FROM

   master.sys.sysdatabases

   LEFT JOIN msdb.dbo.backupset ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name

WHERE

   msdb.dbo.backupset.database_name IS NULL

   AND master.sys.sysdatabases.name <> 'tempdb'

ORDER BY 

   msdb.dbo.backupset.database_name

Comments

Post a Comment

Popular posts from this blog

Auditing in Oracle database

Unified Auditing: Oracle Database 12c Release 1 introduces a new auditing feature called Unified Auditing (12.1). Oracle Unified Auditing modifies the database's fundamental auditing capabilities. In earlier versions of Oracle, each component has its own audit trail. With unified auditing, all auditing is compiled into a single repository and view. Due to the fact that all audit data is now in a single location and a single format, there is a double simplification as a result. This data is made available in a consistent format in the UNIFIED AUDIT TRAIL data dictionary view, which is made possible by the unified audit trail, which is stored in a read-only table in the AUDSYS schema in the SYSAUX tablespace.  DBAs can build audit rules and then assign them to various users using Oracle 12c Unified Auditing, which has the same advantages as giving users varying system capabilities based on their responsibilities. The unified_audit_trail database will contain all of the audit records....
Read more

rs.stepDown() in MongoDB replication

It instructs the primary of the replica set to become a secondary. After the primary steps down,the eligible secondaries will hold an election for primary. Generally, the rs.stepDown() does not immediately step down the primary. If no electable secondaries are up to date with the primary, the primary waits up to secondaryCatchUpPeriodSecs (by default 10 seconds)  for a secondary to catch up. Once an electable secondary is available, the method steps down the priamry.  Once stepped down, the original primary becomes a secondary and is ineligible from becoming primary again for the remainder of time specified by stepDownSecs. The rs.stepDown() method attempts to terminate long-running user's operations that block the primary from stepping down, such as an index build, a write operation, or a map-reduce job. Syntax: rs.stepDown(stepDownSecs, secondaryCatchUpPeriodSecs)
Read more

Tuples in Python

 Tuples Basically Tuples in Python are very similar to Lists, but, Tuples are immutable which means that they can not be modified/changed. We generally use Tuples to make sure the values are not changed like, dates, Year, and days of the week or dates on a Calendar.  Creating a Tuple: >>> my_t1=(1,2,3) >>> my_t2=('one','two','three') >>> my_t1 (1, 2, 3) >>> my_t2 ('one', 'two', 'three') -- check the length of tupple: >>> len(my_t1) 3 >>> len(my_t2) 3 -- Tuples can have mixed objects as like Lists. >>> my_t3=(1,'two',3,'four') >>> my_t3 (1, 'two', 3, 'four') -- indexing with tuples  >>> my_t3=(1,'two',3,'four') >>> my_t3[2] 3 Immutable Tuples We can not modify the value. Here is the example: >>> my_t3=(1,'two',3,'four') >>> my_t3[2]=4 Traceback (most recent call last):  ...
Read more