Backup and restore MS SQL Server

There are three main types of SQL Server backup:

1.One is full backup . The data that this backup includes is debatable. We know that two types of files, data files and log files, are in the database. Is the backup complete? Pack and back up all the data files and log files into one file? So, when you recover, do you need to restore and re-execute the transactions that happened at the time of the backup after the backup was executed? The questions above are all positive. What the total backup does is first flush all the caches to the disk, irrespective of whether or not the transaction in progress is committed. This ensures the continuity of the log and the consistency of the log and the data. If the transaction is not committed, the log file data will not be blanked if the mark is active. Start this log the next time you restore it and then use the new log for execution. Therefore, before the full backup, a checkpoint will certainly be performed.

2. The second is a differential backup (differential backup), this backup will also replicate the full backup phase, perform checkpoint first, and then adjust the data page after the last backup All pages are backed up, and this part of the backup will not be logged. But the backup volume is relatively high, as is the complete backup. Regardless of whether there is just one data update on this page or all changes, the differential backup backs up the data page; the third is transaction log backup, log The understanding of uncommitted transactions is what you need to pay attention to during backup. The VLF of the log file is also filled by uncommitted transactions. Log space cannot be recovered by Shrink; if the log of the committed transaction is backed up, the VLF log will be marked as non-active or truncated. Label, by executing shrink at this time, this portion of the log VLF can be retrieved. The amount of log backup is minimal, which is more appropriate, such as once every 5 minutes, for regular execution.

Backup in full:

There are two parameters involved in the commands used with complete backup. One is to specify a suitable backup system that may be a disk or a tape; the other is the backup options that are available, such as whether to compress or encrypt.

BACKUP DATABASE database 

You can identify the logical device in advance, or you can specify the physical device directly. The backup device is very precise. I have never seen a tape backup machine, but it is still possible to address daily disk backups:

A local physical file name with a path can be listed as a backup device:

backup database pankajconnect 
to disk = 'E:\Data_BU\pankajconnect_backup.bck' ;

You can also specify a physical file name with a path on the network as the backup device:

BACKUP DATABASE AdventureWorks2012  
TO DISK = '\\BackupSystem\BackupDisk1\pankajconnect_backups\AdventureWorksData.Bak';
GO

An important concept happens here. If, after the full backup, we forget to back up the log, the loss happens immediately and we need to restore it. During the recovery, however, the log will be rewritten, so data will be lost. If you do not take additional steps, an error is recorded by the system:

restore database pankajconnect 
from disk = 'E:\Data_BU\pankajconnect__backup.bck'
Msg 3159, Level 16, State 1, Line 6

So if you don’t care about the lost data or think that you will not lose the data, you can use the with replace option to rewrite the original log file for forced recovery.

restore database lenistest 
from disk = ‘E:\Data_BU\pankajconnect__backup.bck’ with replace ;

Backup Differential:

The advantage of differential backup is that the size of backup data is smaller compared to full backup, but the interesting thing is that differential backup does not exist separately (log backup does not exist independently, they can only be added to full backup, i.e. when doing differential backup and log When you back up, you must first have a full backup there.) The difference You may decide, on this basis, the data pages have been modified. These revised pages of data will be estimated and backed up.

use master 
go
backup database pankajconnect to disk = ‘E:\Data_BU\lenistest5__backup.bck’ ;
backup database pankajconnect to disk = ‘E:\Data_BU\pankajconnect5__backup.bck’ with differential ;

If we do a differential backup without making a full backup beforehand, then this is unsuccessful:

backup database lenistest  to disk = 'E:\Data_BU\pankajconnect__backup2.bck'  with differential ;

Log backup:

Log backup is smaller than differential backup, and it also requires full backup to exist in advance:

backup log pankajconnect to disk = ‘E:\Data_BU\pankajconnect__backup.bck’;

Prompt you first to do a complete backup!

We all mentioned the backup, let’s look at restoring it. Usually, there are two steps to recover, one to restore, and the other to restore. Of course, without restoration, we can still restore it directly, but data can be lost until after the complete backup there is no service. Suppose we have a full day backup, every 15 minutes a differential backup, and every 5 minutes a log backup. How do we have our database restored?

We usually need to know our backup file name or physical path first. This location contains several difficult to understand words, such as backup computer, backupset, backup media, media collection, family of media.

On MSDN, there is an explanation. Look at this script first:

BACKUP DATABASE AdventureWorks2017 

Clarify that a media set is generated by this backup process, this media set is called MyAdvWorks MediaSet1, this media set also has a media header, the backup file can be written into it until the media header is generated. A backup file spanning three tapes at once is also created by this script. Collectively, they are considered a set of backups.

All subsequent backups need to specify these 3 backup devices as the backup set at the same time when we specify 3 backup devices as the backup set and perform the first complete backup:

backup database pankajconnect

I first made a complete backup above, and listed as a backup collection three backup devices. I listed only two of the backup devices as the backup collection when I made a differential backup. The mission failed and the urge was that one was missing. Device for backups.

backup database pankajconnect 

We specified the same number of backup devices this time, but the backup device order was reversed and the operation succeeded.

So far, a new media package, called pankajconnectbackupset, and 2 backup sets have been created for our script. A full backup set is the first backup set, and a differential backup is the other backup set. So a backup set will be created by any backup. The time the media set is created is when the database is first completely backed up.

We need to rebuild the database at this stage, so the first step is to first restore the full backup, but not restore it first, and then use the differential backup to restore it after restoring the full backup:

restore database pankajconnect from disk = ‘E:\Data_BU\pankajconnect__backup01.bck’, disk = ‘E:\Data_BU\pankajconnect__backup03.bck’, disk = ‘E:\Data_BU\pankajconnect__backup02.bck’ with file = 1 ,

Here must be used to rewrite the log.

select mf.media_set_id  ,isnull(ms.name,'no media name') as media_name  ,mf.physical_device_name  ,mf.family_sequence_number  ,mf.media_family_id  ,bs.database_name  ,bs.backup_start_date  ,bs.backup_finish_date  from backupmediafamily mf  inner join backupset bs on mf.media_set_id = bs.media_set_id  left join backupmediaset ms on bs.media_set_id = ms.media_set_id  where bs.database_name = 'pankajconnect'

The above script can capture this media family, media set, and backup set information. If we use 3 backup devices to hold the backup, as in the above example, then these 3 backup devices, according to family sequence number orchestration, 1, 2, 3, form a media family.

An example of the entire process from backup to recovery is as follows. Before complete backup, differential backup, and log backup respectively, the same data is entered and different, and see if it can be restored correctly when restored:

insert into dbo.dataloading(object_id,object_name)  

insert into dbo.dataloading(object_id,object_name)

select object_id, name as object_name

from sys.objects

backup database lenistest
to disk = ‘E:\Data_BU\pankajconnect__backup01.bck’, disk = ‘E:\Data_BU\pankajconnect__backup03.bck’, disk = ‘E:\Data_BU\pankajconnect__backup02.bck’
with noinit, differential, medianame = ‘lenistestbackupset’ ;

go

insert into dbo.dataloading(object_id,object_name)
select object_id, name as object_name
from sys.objects

The file option here is the backup set option, meaning the first set of backups, the second set of backups, and the third set of backups. None of the previous restores can be restored if you want to restore to the last point in time when the loss happened, and recovery can only be performed at the last time.

We only need to run the recovery if we just want to restore the complete backup data, but the data is certainly missing:

restore database lenistest from disk = ‘E:\Data_BU\lpankajconnect__backup01.bck’, disk = ‘E:\Data_BU\pankajconnect__backup03.bck’, disk = ‘E:\Data_BU\pankajconnect__backup02.bck’ with file = 1 , replace, recovery ;

Query the size of the backup file

In SQL SEVER, the detailed information of each backup file will be recorded in msdb.dbo.backupset, including size, compressed size, processing time, etc.

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS