Backup and Restore

Hi Friends,

I am going to start a series on SQL Server Backups from past to present. So in my first blog of this series, I want to put some light on the introduction and Importance of backups.

During our study or even in most blogs, we generally read a statement “Data is one of the most important assets for an organization.”  This statement itself explains the importance of data. To protect this important asset from any type of loss, we use ‘Backup’.

Meaning of Backup as per dictionary:

Something, that can be called on if necessary, a reserve.

Meaning of Backup as per SQL Server Books Online:

A copy of SQL Server data that can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. In addition to data backups, the full recovery model requires creating backups of the transaction log.

For a Database Administrator, Backup is the primary responsibility because you are the administrator for most important asset of an organization. There are various types of backup in SQL Server like full, differential, log backup etc. Database Administrator also prepares a good backup strategy by using them to minimize the data loss as much as possible as per business requirement or Service Level Agreement (SLA).

Implementing only a good backup strategy is not sufficient. There may be a situation where you have the backup but that may be corrupted due to any reason. So testing of backup correctness is also another responsibility of Database Administrator. Generally we can check the correctness of backups by restoring them on test servers regularly. This testing is really important to say “We are good from backup side.”

There are many third party tools available in the market which also provides functionality of backups. There are various types of option also available to use while using backup like INIT, CHECKSUM, BLOCKSIZE, BUFFERCOUNT etc. I’ll try to cover as much as possible.

HAPPY LEARNING!

SQL Server Copy Only Backup

Hi Friends,

Using SQL Server Copy Only Backup option for ad hoc backups.

Problem:

We always setup a bulletproof backup strategy for our production server using Full, Differential and transaction Log backups. Which always

Suppose on our production server the backup strategy is as follows:

Full Backup: 1 AM (Sunday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

Differential Backup: 1 AM (Monday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

DIfferential Backup: 1 AM (Tuesday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

DIfferential Backup: 1 AM (Wednesday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

DIfferential Backup: 1 AM (Thursday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

DIfferential Backup : 1 AM (Friday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

Differential Backup: 1 AM (Saturday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

But suppose if I need an ad hoc database full backup at 11:30 AM on Wednesday to troubleshoot some issue.

Now suppose if I take normal full backup at 11:30 AM then Backup chain will be breakup, because differential backup contain data modified after the full backup.

Here, I just want to take the backup of database as a single file without the breakup of Backup chain or without knowing the complexity of Production server backup strategy.

Solution:

To solve this problem SQL Server provides a special type of Backup Option – “Copy Only Backup”

This feature was introduced in SQL Server 2005 but it is also work in SQL Server 2005 and 2008, if your database compatibility level is 80. It never breaks your backup chain.

We can explain Copy Only Backup as “Copy only backup is a type of backups in SQL Server, which is mostly used for taking ad hoc backup and never”

In SQL Server 2005:

(a)    Only through TSQL as:

1	Backup Database TEST to Disk=’D:\TEST_FULL.bak’ WITH COPY_ONLY

In SQL Server 2008 and 2008 R2:

(a)    Using TSQL as :

1	Backup Database TEST to Disk=’D:\TEST_FULL.bak’ WITH COPY_ONLY

(b)    Using SSMS :

When you taking Full Database Backup from SSMS, then to make it copy only just click on check box option as shown below.

Regards

Prince Rastogi

SQL Server – DATABASE Backup in terms of Percent_Complete

Hi Friends,

Here I am explaining  a very general scenario about database backup.

Problem :

Suppose if we have a large database. The Backup process of this database will take long time but we just want to know how much percent of that backup is completed at the time of Backup process is running.

Solution :

When we take the backup from Management Studio then it shows the amount of percentage completed in the bottom – left corner progress section.

But what happen, when we take the backup of the database using TSQL.

I have a database GEEKS of 10 GB. When we execute the backup command as mention below..

1	BACKUP DATABASE GEEKS TO DISK='M:\DATABASE\GEEKS.bak'

Then SSMS shows no information about how much backup process is completed. As shown below..

To know such type of information microsoft provides a special type of DMV “sys.dm_exec_requests”. This DMV provides many columns data to show various information. here we just want to know only the value of column “percent_complete”. So we can execute this command..

1 2	select percent_complete,* from sys.dm_exec_requests where command='BACKUP DATABASE'

Output of this query is shown below…

Regards

Prince Rastogi

SQL Server – To Find out the database restore information..

To Find out the last database restore information..

Problem:

Sometimes we need such type of information like who restore the backup of live database on test server and on which date and time?

Solution:

We can find out such type of information from SQL Server Logs, But here the person who restore the database also delete the logs, then how can we identify?

Microsoft SQL Server provide msdb database to store all backup and restore information and many more tasks related to sql server agent.

MSDB contain special type of table named as [restorehistory] which store information of all the restore operation.

1 2 3

Use msdb GO Select restore_date, destination_database_name, user_name  from restorehistory

Where restore_date = date and time of restoration

Destination_database_name = name of destination database on which restore perform

Username = Name of user who perform the restore operation

Regards

Prince Rastogi

SQL Server – Database Backup Compression – Faster Disaster Recovery

Hi Friends,

Today, I am focusing on very interested feature provided by Microsoft SQL Server for DBA people. This feature is “Database Backup Compression”. This feature first introduced in SQL Server 2008, only for Enterprise editions. This feature is also available in later versions. If we want to use this feature then we need to turn on this feature. There are some major benefits of using database backup compression:

1- Less backup time: after compressing the data SQL Server takes backup, which really decrease the backup time for our database. But keep in mind that performs compression before writing the data to backup file will increase the CPU uses.

2- Less Restore time: the most beautiful feature of database backup compression is less time to restore the database. We all know that there are four steps perform by SQL Server to restore the database – file creation and initialization, data copy, redo ,undo. Here backup restore will take less amount of time in second step “data copy” due to database backup compression. This is just because of very less IO. That means this feature also help us to obtain a fast recovery time in case of disaster recovery.

3- Less Disk Space: Due to compression database backup file will take less amount of space on disk. Compression percentage is totally dependent on type of data, consistency of data, data is encrypted or not, database is compressed or not. If you are using data compression then database backup compression percentage will be very low. If you are not using data compression then it will provide you 80% to 85% compression percentage.

You can setup this feature at instance level by using SSMS and TSQL.

Using SSMS:

Connect your SQL Server Instance in SSMS. Then in object explorer right click on instance name and go to properties. This will open up server properties window. Here click on database settings. Here on right side panel just click on compress backup option to enable database backup compression setting at instance level as shown below:

Using TSQL:

1 2 3 4

Use Master; GO Exec sp_configure 'backup compression default','0'; Reconfigure with override;

You can also set this setting of database backup compression at database backup level.

Using SSMS:

When you take the backup through SSMS then go to Options under back up database window. Here you can change the setting as shown below:

Using TSQL:

I am having a database GEEKS on my test instance, here I am going to take full backup of GEEKS by using below TSQL

1 2	BACKUP DATABASE [GEEKS] TO  DISK = N'F:\DATABASE\GEEKS_12-OCT-2012.bak' WITH NOFORMAT, NOINIT,  NAME = N'GEEKS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 GO

Regards

Prince Rastogi

SQL Server Database Restore with Snapshot – Backup Chain Breaker

Hi Friends,

Today I will show you practical for one of the reason of Transaction Log backup chain breaker i.e.  SQL Server Database Restore with database snapshot. Suppose I am having one database named as SQLTEST having one table named as xtSQLTest with some amount of data.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

--create database create database SQLTEST go USE SQLTEST go -- create a table create table xtTest (     id int identity(1,1) ) go --insert some data in the table insert into xtTest default values; go 100

Now take full backup and insert some more data then again take first transaction log backup:

1 2 3 4 5 6 7 8 9

--take the full backup backup database SQLTEST to disk='e:\Full.bak' go --insert some more data in the table insert into xtTest default values; go 100 --take first Transaction log backup backup log SQLTEST to disk='e:\tlog1.trn' go

Now create database snapshot here and insert some more data then again take second transaction log backup:

1 2 3 4 5 6 7 8 9 10 11

create database snap_SQLTEST on (NAME=SQLTEST, FILENAME='E:\SQLTEST.snap') as snapshot of SQLTEST go --insert some more data in the table insert into xtTest default values; go 100 --take second Transaction log backup backup log SQLTEST to disk='e:\tlog2.trn' go

So everything is running fine till here, now just SQL Server Database Restore with the snapshot which you created earlier, then again try to take third transaction log backup:

1 2 3 4 5 6 7 8

-- restore database snapshot use master go restore database SQLTEST from database_snapshot='snap_SQLTEST' go --now taking my third Transaction log backup backup log SQLTEST to disk='e:\tlog3.trn' go

Here Snapshot restoration ran fine but we receive below mention error when trying to take third transaction log backup:

Msg 4214, Level 16, State 1, Line 2

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 2

BACKUP LOG is terminating abnormally.

So keep in mind that every time when you restore database snapshot, reinitialize the backup chain with full backup after that you can also take transaction log backups:

1 2 3 4 5 6

--Take New Full backup to start new backup chain backup database SQLTEST to disk='e:\Full_AfterSnapshotRestore.bak' go --take first Transaction log backup backup log SQLTEST to disk='e:\tlog1_AfterSnapshotRestore.trn' go

Regards

Prince Rastogi

SQL Server 2012 Restore Page using SSMS

Dear Friends,

Every release of SQL Server shipped with full of new features so is SQL Server 2012. Some of them are Availability Groups, Contained Databases, Column Store Indexes, User Defined roles,  DQS, Power View to name a few. We do observe lot of enhancements in GUI as well which ease up our task and helpful to the ones who doesn’t want to play with T-SQL. Needless to say, we do look on how newer versions of SQL Server have matured in terms of GUI. With this in mind, today I’m going to show you can restore a page using SSMS on SQL 2012 .

To begin with; at different times we do observe page level corruptions in a database as such definitely as database admins we are interested in restoring single page rather than restoring the entire database mostly to minimize downtime. In practical also when your database is quite huge one in size, you never want to go for full restoration only when a single page is identified to be corrupted. By saying so, my statement definitely is not a generic one understanding this may differ across environments.

We can say that page level restoration is not something new to us but yes SQL Server 2012 Restore Page using SSMS definitely something new in SQL Server 2012. Till SQL Server 2012 page restoration was possible using T-SQL but now with the launch of SQL Server 2012, you have the flexibility to use its improved GUI to achieve the same objective.

Today, I’ll be using AdventureWorks2012 database for the demo purpose. Table I’ll be using here is HumanResources.JobCandidate and I’m interested to corrupt Index Page for this table today. You can read more on how to identify a page here.

As we are discussing on SQL Server 2012 as such I’m not going to use DBCC IND to find out index page details instead will be using new DMVSYS.DM_DB_DATABASE_PAGE_ALLOCATIONS to retrieve similar information. This is an undocumented change and you can read more on this link.

So I ran the query as can be seen on following screenshot to obtain similar details;

Ok, from query output I identified PageID 1057 of Page Type 2 i.e. Index Page (image below);

Now time to corrupt this page but as always recommended before I do that, let me backup my database first (to be at the safer side);

We are all set to corrupt PageID 1057. I’ll be using XVI32 editor to corrupt the page which can be downloaded from here; but before I do so I need to OFFLINE the database.

Now, before I open up hex-editor; I need to find out the page offset and for me it is 1057 * 8192 (i.e. No of bytes for the page) = 8658944. By doing so, I’m all set to corrupt the page now. I went to the exact page and corrupted that by entering some letter;

By doing so, in technical terms I’ve corrupted the page. Time to bring database online and query corrupted table. Let’s do that;

All set to run Select query on HumanResources.JobCandidate table so going forward;

It can be seen very clearly that I’ve corrupted the same page matched in hex-editor. Now it is the time to think about remediating the corrupted page.

As mentioned before starting SQL Server 2012 we can select this from SSMS by right clicking on database and choose restore page. As soon as I do that, I’ll get the following window;

What’s great to see here is SQL Server has already ran a DBCC CHECKDB and pulled details on corrupted page. Good one to have for sure. This wizard also lets you select backup that you want to use to repair corrupted pages so let’s move ahead.

Yes, I’ve successfully restored corrupted page; so let’s check out if we can queryHumanResources.JobCandidate.

We can very much retrieve data out of table is in question. Needless to say, this indeed a cool feature offered by SQL Server 2012.

Please note I’ve tested this in a LAB system for demonstration purpose and you should never ever try these steps in any production or live environments.

Regards

Kanchan Bhattacharyya

SQL Server Full Recovery Model – Impact of checkpoints

Hi Friends,

We all knew that, SQL Server Full Recovery Model log truncation occurs due to transaction log backup while under Simple recovery model log truncation occurs due to checkpoints. But here I want to show you the impact of checkpoint on your database while database is in SQL Server Full recovery model. Actually the concept is when you create any new database then by default the recovery model of that database will be full until you made any change on default setting. Here this database will be treated as under simple recovery model until first full backup will not be performed for this database. That means full recovery mode will not be effective for log truncation until you take first full backup. Let me explain you this thing practically:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

--create the database use master go create database TESTDB go   --Recovery model of this database should be FULL(By Default FULL) select recovery_model_desc from sys.databases where name='TESTDB' go --create a table and insert some data to generate log records use TESTDB go create table xttest (     id int identity(1,1),     name varchar(20) ) go begin tran declare @temp int set @temp=1 while @temp<10000 begin insert into xttest values('SQLServerGeeks-Hyd') set @temp=@temp+1 end commit tran go

Now we have one table named as xttest under TESTDB database where recovery model of database is full and inserted 9999 records in that table. Now let me show you the log usage of this database before checkpoint and as well as after the checkpoint occurs.

1 2 3 4 5 6 7 8 9 10 11

--now you can check the uses of log file use testdb go DBCC SQLPERF('LOGSPACE') go --Now run checkpoint to truncate the log file checkpoint go --check that log file has been truncated by checkpoint or not DBCC SQLPERF('LOGSPACE') Go

Here log space used before checkpoint = 52.63283

Here log space used after checkpoint = 16.47296

These values may be different on your systems. Difference between both above values clearly shows that log truncation took place under full recovery model. If we will take first full back on database then log truncation will not be occur due to checkpoint. let me show you this also practically :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

use master go backup database TESTDB to disk='C:\TestDB_Native_Full.BAK' go use TESTDB go begin tran declare @temp int set @temp=1 while @temp<10000 begin insert into xttest values('SQLServerGeeks-Ggn') set @temp=@temp+1 end commit tran go --now you can check the uses of log file use testdb go DBCC SQLPERF('LOGSPACE') go --Now run checkpoint to truncate the log file checkpoint go --check that log file has been truncated by checkpoint or not DBCC SQLPERF('LOGSPACE') go

Here log space used before checkpoint = 54.17457

Here log space used after checkpoint = 54.37619

This clearly shows that now log truncation not occurs due to checkpoint. Now truncation will only happen after taking transaction log backup:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Use testdb go begin tran declare @temp int set @temp=1 while @temp<10000 begin   insert into xttest values('SQLServerGeeks-chn') set @temp=@temp+1 end commit tran go --now you can check the uses of log file use testdb go DBCC SQLPERF('LOGSPACE') go --Now take tlog backup Backup log TESTDB to disk='C:\TestDB_Native_log.trn' go --check that log file has been truncated by tlog backup DBCC SQLPERF('LOGSPACE') go

Here log space used before Transaction log backup = 65.78065

Here log space used after Transaction log backup = 17.26533

Which clearly shows that log backup will take care of log truncation after first full backup for a database under full recovery model.

HAPPY LEARNING!

SQL Server Backup Encryption, SQL 2014 CTP2

Dear Friends,

SQL Server 2014 CTP2 introduced SQL Server backup encryption using which you can encrypt database backups. A certificate or asymmetric key is used with encryption algorithm to achieve this.  Previously you would have used any third party tools to encrypt your native backup files but this feature is now part of SQL Server 2014 CTP2. You have variety of encryption algorithms to choose from, to name them AES 128, AES 192, AES 256 and Triple DES and can control the process using T-SQL, PowerShell, SSMS or SMO.

This feature is available in Standard, Enterprise or BI edition of SQL Server 2014. Pre-requisites for this feature are to have either a certificate or asymmetric key and to choose from a range of encryption algorithm. Let’s us try this out step by step using T-SQL statements for this demo.

To start with at first we will create a master key;

We will now create an encryption certificate;

We are set to create our encrypted backup file by specifying the certificate and encryption algorithm. Syntax is pretty straight forward as shown below;

You might have observed the warning message, which says certificate used for encryption is not backed up as such it is very much recommended that the certificate or key used for encryption must be backed up to a different location than the backup files.  Without this certificate or key the backup file you cannot restore databases.

This feature is amongst one of the long waited ones which will eliminate dependency on third party tools thus help organizations in saving costs and great to have inbuilt with 2014 CTP2.

Backup

TYPE OF BACKUP

Full Backup

• It will be a complete backup

Incremental (Differential) Backup

• It will be applied on full backup and cannot be created until full backup exists
• Each differential backup will contain all the changes since full backup has been taken
• Header of the database has special page called DCM (Differential Change Map), this page will have the list of all pages which have been changed after full backup.
• Once we take a full backup, DCM will be wiped out.
• Differential Backup 1 = changes made after full backup
• Differential Backup 2 = changes made after full backup
• Differential Backup N = changes made after full backup

Transaction Log Backup

• It will be applied on full backup and cannot be created until full backup exists
• Each transactional log backup will contain the data which has been changed after full backup/last transactional log backup
• Each transaction will have LSN (Log Sequence Number). Each transactional log backup will contain the data (COMMITTED) since last LSN which was part of full backup/last transactional log backup.
• Any gap in LSN will corrupt taking/restoring backup, which can be solved by taking a full backup.
• Transactional Log Backup 1 = changes made after full backup
• Transactional Log Backup 2 = changes made after Transactional Log Backup 1
• Transactional Log Backup 3 = changes made after Transactional Log Backup 2
• Transactional Log Backup N = changes made after Transactional Log Backup N-1

FileGroup Backup

PROCESS OF TAKING BACKUP

• Lock the database and lock all transactions
• Place a mark in transaction log
• Releases lock
• Extracts all the pages from database files and put them in backup device
• Lock the database and lock all transactions
• Place a mark in transaction log
• Releases lock
• Extracts all the pages between marks and append them in backup

RESTORING A BACKUP

• If it is full backup then it is very easy to restore it using a wizard.
• If it is differential/transactional log backup, we have to follow below steps
• Restore a full backup WITH NORECOVERY option.
• Once you restore a backup WITH NORECOVERY, it will display like this.
• Restore a first transactional log backup WITH NORECOVERY option
• Restore final transactional log backup / differential backup WITH RECOVERY option which is default
• Once backup has been restored WITH RECOVERY option, no other transactional / differential backup can be restored on top of that.