-- Demo: Backup/Restore

USE MASTER
GO

-- DROP DATABASE NETDUG

-- Create empty database

CREATE DATABASE NETDUG
GO

USE NETDUG
GO

CREATE TABLE Test (ID INT IDENTITY(1,1))

INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES

-- BACKUP DATABASE NETDUG TO DISK = 'C:\NETDUG.bak' WITH STOP_ON_ERROR, INIT

BACKUP DATABASE NETDUG
    TO DISK = 'C:\NETDUG.bak'
  WITH CHECKSUM, STOP_ON_ERROR, INIT

RESTORE VERIFYONLY
   FROM DISK = 'C:\NETDUG.bak'

-- Use Visual Studio's hex editor to make a goof

RESTORE VERIFYONLY
   FROM DISK = 'C:\NETDUG.bak'

-- RESTORE VERIFYONLY verifies:
--
-- That the backup set is complete and all volumes are readable.
-- Page ID (as if it were about to write the data)
-- Checksum (if present on the media)
-- Checking for sufficient space on destination devices

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: Security

USE NETDUG
GO

-- DROP LOGIN MyLogin

-- Create a login with a weak password

CREATE LOGIN MyLogin WITH PASSWORD = 'password'

-- Create a login with a strong(er) password

CREATE LOGIN MyLogin WITH PASSWORD = 'P@ssw0rd'

-- You can skip the password policy

-- DROP LOGIN MyLogin

CREATE LOGIN MyLogin WITH PASSWORD = 'password', CHECK_POLICY = OFF

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: Encryption

USE NETDUG
GO

-- Create certificate

CREATE CERTIFICATE SampleCert
 ENCRYPTION BY PASSWORD = 'P@ssw0rd'
 WITH SUBJECT = 'SampleCertificate' 

-- Load certificate

-- First, create the certificate and private key files (go to the .NET command prompt)
-- MAKECERT.EXE c:\sample.cer -sv c:\sample.pvk
-- Use Pass@word1 for all passwords
 
-- Create Certificate
 
CREATE CERTIFICATE SampleCert
FROM FILE = 'C:\Sample.cer'
WITH PRIVATE KEY (FILE='C:\sample.pvk',ENCRYPTION BY PASSWORD  = N'P@ssw0rd',DECRYPTION BY PASSWORD  = N'P@ssw0rd')
 
-- Verify
 
SELECT * FROM SYS.CERTIFICATES
SELECT Cert_ID('SampleCert')
 
-- Encrypt
 
DECLARE @Encrypted varbinary(4000)
SET @Encrypted = EncryptByCert(Cert_ID('SampleCert'), 'SQL Server 2005 Rocks!')
SELECT @Encrypted AS Encrypted
 
-- Decrypt
 
SELECT CONVERT(VARCHAR,DecryptByCert(Cert_ID('SampleCert'),@Encrypted,N'P@ssw0rd'))
 
-- Done
 
DROP CERTIFICATE SampleCert

-- Encrypt by simple passphrase

DECLARE @Encrypted varbinary(4000)
SET @Encrypted = EncryptByPassPhrase('P@ssw0rd','SQL Server 2005 Rocks!')
SELECT @Encrypted

-- Decrypt by simple passphrase

SELECT CONVERT(varchar(30),DecryptByPassPhrase('P@ssw0rd',@Encrypted))

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: MAX specifier

USE NETDUG
GO

SET NOCOUNT ON

CREATE TABLE BigText (ID int, Data text)

INSERT BigText VALUES (1,'Hello World')
INSERT BigText VALUES (2,REPLICATE('x',10000))
SELECT * FROM BigText

-- Can't pass TEXT to functions :-(

SELECT UPPER(Data) FROM BigText

-- Use MAX instead!

DROP Table BigText
CREATE TABLE BigText (ID int, Data nvarchar(max))

INSERT BigText VALUES (1,'Hello World')
INSERT BigText VALUES (2,REPLICATE('x',10000))
SELECT * FROM BigText
SELECT UPPER(Data) FROM BigText

DROP Table BigText

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: TRY-CATCH

USE AdventureWorks
GO

-- DROP PROCEDURE MyProc

CREATE PROCEDURE MyProc
AS
BEGIN
 BEGIN TRY 
   SELECT * FROM Person.Address WHERE 1 > 2/0 -- Divide by zero!
 END TRY
 BEGIN CATCH
  PRINT 'Error Occurred'
  PRINT ERROR_NUMBER()
  PRINT ERROR_SEVERITY()
  PRINT ERROR_STATE()
  PRINT ERROR_MESSAGE()
 END CATCH
END
GO

EXEC MyProc 

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: OUTPUT IDENTITIES

USE NETDUG
GO

SET NOCOUNT ON
GO

-- Create table

CREATE TABLE IdentityTest (ID int identity(1,1), Data varchar(20))
GO

-- Insert a row

INSERT IdentityTest (Data) Values ('Red')
SELECT * FROM IdentityTest

-- @@IDENTITY returns last identity value assigned

INSERT IdentityTest (Data) Values ('Blue')
SELECT @@IDENTITY

-- @@IDENTITY doesn't handle inserting multiple rows

INSERT IdentityTest (Data) SELECT Data FROM IdentityTest
SELECT @@Identity

-- Neither does SCOPE_IDENTITY()

INSERT IdentityTest (Data) SELECT Data FROM IdentityTest
SELECT SCOPE_IDENTITY()

-- Using INSERT ... with OUTPUT is the solution!

DECLARE @OutputTable TABLE (ID int, Data varchar(20))
INSERT IdentityTest (Data) 
       OUTPUT Inserted.ID, Inserted.Data INTO @OutputTable
       SELECT Data FROM IdentityTest
SELECT * FROM @OutputTable



