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 
TO backup_device [ ,…n ] 
[ WITH with_options [ ,…o ] ] ;

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
The tail of the log for the database “lenistest” has not been backed
up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains
work you do not want to lose. Use the WITH REPLACE or WITH STOPAT
clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.

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 ;
Msg 3035, Level 16, State 1, Line 11
Cannot perform a differential backup for database “pankajconnect”, because
a current database backup does not exist. Perform a full database
backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL
option.
Msg 3013, Level 16, State 1, Line 11
BACKUP DATABASE is terminating abnormally

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’;
If I didn’t make a full backup beforehand, let’s see what happens when I back up the log directly:
Msg 4214, Level 16, State 1, Line 15
BACKUP LOG cannot be performed because there is no current database
backup.
Msg 3013, Level 16, State 1, Line 15
BACKUP LOG is terminating abnormally.

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 
TO TAPE = ‘\\.\tape0’, TAPE = ‘\\.\tape1’, TAPE = ‘\\.\tape2’ 
WITH FORMAT, 
MEDIANAME = ‘MyAdvWorks_MediaSet_1’

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
to disk = 'E:\Data_BU\pankajconnect__backup01.bck', disk = 'E:\Data_BU\lenistest5__backup02.bck', disk = 'E:\Data_BU\pankajconnect__backup03.bck'
with format, medianame = 'pankajconnectbackupset' ;
backup database pankajconnect
to disk = 'E:\Data_BU\pankajconnect__backup01.bck', disk = 'E:\Data_BU\pankajconnect__backup03.bck'
with noinit, differential, medianame = 'pankajconnecttbackupset'
Msg 3231, Level 16, State 1, Line 10
The media loaded on “E:\Data_BU\pankajconnect__backup01.bck” is formatted
to support 3 media families, but 2 media families are expected
according to the backup device specification.
Msg 3013, Level 16, State 1, Line 10
BACKUP DATABASE is terminating abnormally.

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 
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 = ‘pankajconnectbackupset’ ;

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 ,
 replace, norecovery ;
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 = 2 ,
recovery ;

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)  
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\pankajconnect5__backup02.bck', disk = 'E:\Data_BU\pankajconnect__backup03.bck' with format, medianame = 'pankajconnect' ;  go

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
backup log pankajconnect
to disk = ‘E:\Data_BU\pankajconnect__backup01.bck’, disk = ‘E:\Data_BU\pankajconnect__backup03.bck’, disk = ‘E:\Data_BU\pankajconnect__backup02.bck’
with noinit, medianame = ‘lenistestbackupset’ ;

Then we do restoration and recovery:
restore database pankajconnec
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 , replace, norecovery ;
restore database pankajconnec
from disk = ‘E:\Data_BU\pankajconnect__backup01.bck’, disk = ‘E:\Data_BU\pankajconnect__backup03.bck’, disk = ‘E:\Data_BU\pankajconnect__backup02.bck’
with file = 2 , norecovery ;
restore database pankajconnec
from disk = ‘E:\Data_BU\pankajconnect__backup01.bck’, disk = ‘E:\Data_BU\pankajconnect__backup03.bck’, disk = ‘E:\Data_BU\pankajconnect__backup02.bck’
with file = 3 , recovery ;

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.
To find media_set_id with the file name through msdb.dbo.backupmediafamily
Select Top 20 Convert(Varchar(40), S.database_name) AS [Database Name]
,Convert(varchar(19),S.backup_start_date, 120) AS [Start Date]
,Convert(varchar(19), S.backup_finish_date, 120) the AS [Finish a Date]
, DateDiff (S, S.backup_start_date, S.backup_finish_date) the AS [the Duration (S)]
, Case
type
the When 'D' the Then 'FULL'
the When 'L' the Then 'the Log'
the When 'the I' the Then 'Differential'
the When 'F.' the Then 'File Group' the Type End of As
, the Convert (Varchar (15), the Convert (a Decimal (. 5, 2), Round (S.backup_size / Square (1024), 2))) the AS [ Size (MB)]
,Convert(Varchar(15), Convert(Decimal(5, 2), Round(S.compressed_backup_size / Square(1024), 2))) AS [CSize (MB)]
,Convert(varchar(19 ), S.expiration_date,80) AS [Expiration Date]
,M.physical_device_name AS [Phys. Device Name]
,M.logical_device_name As [Logi. Device Name]
,S.backup_size ,*
From
msdb.dbo.backupset S Inner Join
msdb.dbo.backupmediafamily M ON S.media_set_id =M. media_set_id
Order By
S.backup_finish_date Desc

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.