summit2020_demo_006.txt

#######################################################################################
# PASS Virtual Summit 2020 - SQL Server on Linux from A to Z (Randolph West)          #
# Pre-conference session (2020-11-10)                                                 #
#######################################################################################
# The scripts in this document are procured from several sources, including Microsoft #
# Docs, Wikipedia, genuinecoder.com, linuxhint.com, howtogeek.com, linux.org,         #
# ss64.com, and tecmint.com.                                                          #
# No copyright is claimed or intended from these code samples                         #
#######################################################################################

------------------------------------------
-- EXAMPLE 1: Database BackupTestEx1
------------------------------------------

USE [master];
GO

-- Hey! Yes, you! Have you turned on backup
-- compression at the server level?

SELECT	name, value, value_in_use
FROM	sys.configurations
WHERE	name = N'backup compression default';
GO

EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;
GO

-- Create a new database for demo purposes
DROP DATABASE IF EXISTS BackupTestEx1;
GO

CREATE DATABASE BackupTestEx1;
GO

USE BackupTestEx1;
GO

DROP TABLE IF EXISTS dbo.TableTest;
GO

CREATE TABLE TableTest
(
	Column1 INT PRIMARY KEY IDENTITY(1, 1)
);
GO

-- Insert some data
INSERT INTO dbo.TableTest
DEFAULT VALUES;
GO 10

-- See how the data looks
SELECT
	*
FROM
	dbo.TableTest;
GO

-- Take a full backup
BACKUP DATABASE BackupTestEx1
TO DISK = N'/var/opt/mssql/data/BackupTestEx1.bak';
GO

-- Take a log backup to record any changes
-- and truncate (set inactive) the VLFs
BACKUP LOG BackupTestEx1
TO DISK = N'/var/opt/mssql/data/BackupTestEx1Log1.trn';
GO

-- Insert more data
INSERT INTO dbo.TableTest
DEFAULT VALUES;
GO 10

-- Take another log backup to record changes
BACKUP LOG BackupTestEx1
TO DISK = N'/var/opt/mssql/data/BackupTestEx1Log2.trn';
GO

BACKUP DATABASE BackupTestEx1
TO DISK = N'/var/opt/mssql/data/BackupTestEx1.diff'
WITH DIFFERENTIAL;
GO

-- Insert more data
INSERT INTO dbo.TableTest
DEFAULT VALUES;
GO 10

-- One more log backup to record these changes
BACKUP LOG BackupTestEx1
TO DISK = N'/var/opt/mssql/data/BackupTestEx1Log3.trn';
GO

-- Similate a disaster recovery scenario
USE [master];
GO

DROP DATABASE IF EXISTS BackupTestEx1;
GO

-- Restore full backup, with recovery 
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1.bak';
GO

-- See how the data looks
USE BackupTestEx1;
GO

SELECT	*
FROM	dbo.TableTest;
GO

USE [master];
GO

DROP DATABASE IF EXISTS BackupTestEx1;
GO

-- Restore full backup, with no recovery 
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1.bak'
WITH NORECOVERY;
GO

USE BackupTestEx1;
GO

-- Restore most recent transaction log file
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1Log3.trn'
WITH RECOVERY;
GO

-- Transaction log backups are incremental, so all of them are required
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1Log1.trn'
WITH NORECOVERY;
GO
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1Log2.trn'
WITH NORECOVERY;
GO
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1Log3.trn'
WITH NORECOVERY;
GO

-- Roll forward committed transactions and roll back uncommitted transactions
RESTORE DATABASE BackupTestEx1
WITH RECOVERY;
GO

-- See how the data looks
USE BackupTestEx1;
GO

SELECT	*
FROM	dbo.TableTest;
GO

USE [master];
GO

DROP DATABASE IF EXISTS BackupTestEx1;
GO

-- Restore full backup, with no recovery 
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1.bak'
WITH NORECOVERY;
GO

-- Restore differential backup, with no recovery 
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1.diff'
WITH NORECOVERY;
GO

-- Differential backups are not incremental...
-- so this will fail
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1Log1.trn'
WITH NORECOVERY;
GO
-- and this will fail too
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1Log2.trn'
WITH NORECOVERY;
GO
-- ... but this transaction log backup will work
-- because of the LSN
RESTORE DATABASE BackupTestEx1
FROM DISK = N'/var/opt/mssql/data/BackupTestEx1Log3.trn'
WITH NORECOVERY;
GO

-- Roll forward committed transactions and roll back uncommitted transactions
RESTORE DATABASE BackupTestEx1
WITH RECOVERY;
GO

-- See how the data looks
USE BackupTestEx1;
GO

SELECT	*
FROM	dbo.TableTest;
GO

------------------------------------------
-- EXAMPLE 2: Database BackupTestEx2
------------------------------------------

-- Copy only backup with transaction log backups
-- Delete the old backup files from the C: drive

USE [master];
GO

-- Create a new database for demo purposes
DROP DATABASE IF EXISTS BackupTestEx2;
GO

CREATE DATABASE BackupTestEx2;
GO

USE BackupTestEx2;
GO

DROP TABLE IF EXISTS dbo.TableTest;
GO

CREATE TABLE TableTest
(
	Column1 INT PRIMARY KEY IDENTITY(1, 1)
);
GO

-- Insert some data
INSERT INTO dbo.TableTest
DEFAULT VALUES;
GO 10

-- See how it looks
SELECT	*
FROM	dbo.TableTest;
GO

-- Everyone needs a good full backup to seed the backup chain
BACKUP DATABASE BackupTestEx2
TO DISK = N'/var/opt/mssql/data/BackupTestEx2.bak';
GO

-- Let's do a copy-only backup now as well
BACKUP DATABASE BackupTestEx2
TO DISK = N'/var/opt/mssql/data/BackupTestEx2CopyOnly.bak'
WITH COPY_ONLY;
GO

-- Insert some data
INSERT INTO dbo.TableTest
DEFAULT VALUES;
GO 10

-- Let's do a copy-only log backup as well
-- Does not truncate the log
BACKUP LOG BackupTestEx2
TO DISK = N'/var/opt/mssql/data/BackupTestEx2CopyOnly.trn'
WITH COPY_ONLY;
GO

-- Does truncate the log
BACKUP LOG BackupTestEx2
TO DISK = N'/var/opt/mssql/data/BackupTestEx2Log1.trn';
GO

-- Let's do another full backup for fun
BACKUP DATABASE BackupTestEx2
TO DISK = N'/var/opt/mssql/data/BackupTestEx2Full2.bak';
GO

-- Insert some more data
INSERT INTO dbo.TableTest
DEFAULT VALUES;
GO 10


BACKUP LOG BackupTestEx2
TO DISK = N'/var/opt/mssql/data/BackupTestEx2Log2.trn';
GO

-- Prove that Copy-Only Backups don't
-- affect the backup chain
USE [master];
GO

DROP DATABASE IF EXISTS BackupTestEx2;
GO

-- Restore copy-only backup, with no recovery 
RESTORE DATABASE BackupTestEx2
FROM DISK = N'/var/opt/mssql/data/BackupTestEx2CopyOnly.bak'
WITH NORECOVERY;
GO
RESTORE LOG BackupTestEx2
FROM DISK = N'/var/opt/mssql/data/BackupTestEx2Log1.trn'
WITH NORECOVERY;
GO
RESTORE LOG BackupTestEx2
FROM DISK = N'/var/opt/mssql/data/BackupTestEx2Log2.trn'
WITH NORECOVERY;
GO
RESTORE DATABASE BackupTestEx2
WITH RECOVERY;
GO

-- See that all the data is there
USE BackupTestEx2;
GO

SELECT	*
FROM	dbo.TableTest;
GO

-- That works even with the full backup that
-- was taken in between, because transaction
-- logs are incremental

-- Let's try with the second full backup
USE [master];
GO

DROP DATABASE IF EXISTS BackupTestEx2;
GO


-- Restore full backup, with log
RESTORE DATABASE BackupTestEx2
FROM DISK = N'/var/opt/mssql/data/BackupTestEx2Full2.bak'
WITH NORECOVERY;
GO
RESTORE LOG BackupTestEx2
FROM DISK = N'/var/opt/mssql/data/BackupTestEx2Log2.trn';
GO

-- See that all the data is there
USE BackupTestEx2;
GO

SELECT	*
FROM	dbo.TableTest;
GO

-- Always run DBCC CHECKDB after a restore
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO