Backup and restore MS SQL Server

BACKUP DATABASE database TO backup_device [ ,…n ] [ WITH with_options [ ,…o ] ] ;
backup database pankajconnect 
to disk = 'E:\Data_BU\pankajconnect_backup.bck' ;
BACKUP DATABASE AdventureWorks2012  
TO DISK = '\\BackupSystem\BackupDisk1\pankajconnect_backups\AdventureWorksData.Bak';
GO
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 6RESTORE DATABASE is terminating abnormally.
restore database lenistest 
from disk = ‘E:\Data_BU\pankajconnect__backup.bck’ with replace ;
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 ;
backup database lenistest  to disk = 'E:\Data_BU\pankajconnect__backup2.bck'  with differential ;Msg 3035, Level 16, State 1, Line 11Cannot 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 11BACKUP DATABASE is terminating abnormally
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 15BACKUP LOG cannot be performed because there is no current database
backup.
Msg 3013, Level 16, State 1, Line 15BACKUP LOG is terminating abnormally.
BACKUP DATABASE AdventureWorks2017 TO TAPE = ‘\\.\tape0’, TAPE = ‘\\.\tape1’, TAPE = ‘\\.\tape2’ WITH FORMAT, MEDIANAME = ‘MyAdvWorks_MediaSet_1’
backup database pankajconnectto 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 pankajconnectto 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 10The 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 10BACKUP DATABASE is terminating abnormally.
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’ ;
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 ;
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'
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
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 ;
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.backupmediafamilySelect 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

--

--

--

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Adding a Hello World System Call to Linux Kernel

10 Must Have Linux Apps 2021/2022 Edition

The Microsoft Teams Keyboard Shortcuts Cheat Sheet

The Microsoft Teams Keyboard Shortcuts Cheat Sheet

Logging in Kubernetes with Loki and the PLG Stack

Connecting to Atlas using Robo 3T/Studio 3T

Kotlin Channels Piping Generating Prime Number Illustrated

Docker Tutorial for Beginner

Making Laravel Nova custom fields more developer-friendly

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

Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS

More from Medium

“Multiple” Special Character Replacements in “Multiple” Columns

Loop and Accumulate

Introduction to Docker in R

Out With The Old, In With The Tools

PANDAS: A Glimpse into Rare Paediatric Disorders