/*============================================================================
  File:     3_Taking_Full_TLog_Backups.sql

  Summary:  This script shows how to take full and 
			transaction log backups.

  Date:     April 2015

  SQL Server Version: 2005/2008/2008R2/2012/2014
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  For more scripts and other useful content, go to:
	http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Erin Stellato.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/
/*
	reset database
*/

/*
	Verify the recovery model
*/ 
USE [master];
GO

SELECT [name], [recovery_model_desc]
FROM [sys].[databases]
WHERE [name] = 'AdventureWorks2014';

/*
	Set recovery to FULL if needed
*/
ALTER DATABASE [AdventureWorks2014] SET RECOVERY FULL;
GO


/* 
	Begin your "recovery strategy setup" by taking the initial backup
*/

/*
	If you script out the backup from SSMS, what does it look like?
*/
BACKUP DATABASE [AdventureWorks2014] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_BaseBackup.bak' 
	WITH NOFORMAT, 
	NOINIT,  
	NAME = N'AdventureWorks2014-Full Database Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10;
GO


/*
	When you don't know what's in a backup, use these
*/
RESTORE HEADERONLY 
FROM DISK = N'C:\Backups\SQL2014\AW2014_BaseBackup.bak';
GO

RESTORE FILELISTONLY
FROM DISK = N'C:\Backups\SQL2014\AW2014_BaseBackup.bak';
GO

/*
	Modify some data
*/
USE [AdventureWorks2014];
GO

UPDATE [Person].[Person] SET [FirstName] = 'Tony';
GO


/*
	Take another full backup
	The only difference is the NAME is modified slightly
	What's going to happen?
*/
BACKUP DATABASE [AdventureWorks2014] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_BaseBackup.bak' 
	WITH NOFORMAT, 
	NOINIT,  
	NAME = N'AdventureWorks2014-*SECOND* Full Database Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10;
GO


/*
	Check to see what's in the backup 
*/
RESTORE HEADERONLY 
FROM DISK = N'C:\Backups\SQL2014\AW2014_BaseBackup.bak';
GO



/* 
	Let's start our "recovery strategy setup" again...
*/	
	USE [master];
	GO

/*
	recreate AW2014
*/

/*
	Set recovery to FULL 
*/
USE [master];
GO
ALTER DATABASE [AdventureWorks2014] SET RECOVERY FULL;
GO


/*
	Backup the database
	exclude: SKIP, NOREWIND, NOUNLOAD
	use INIT
*/
BACKUP DATABASE [AdventureWorks2014] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_BaseFull.bak' 
	WITH NOFORMAT, 
	INIT,   
	COMPRESSION,  
	STATS = 10;
GO


RESTORE HEADERONLY 
FROM DISK = N'C:\Backups\SQL2014\AW2014_BaseFull.bak';
GO

RESTORE FILELISTONLY
FROM DISK = N'C:\Backups\SQL2014\AW2014_BaseFull.bak';
GO



/*
	Now that we have a full backup, we can take transaction log backups
	But first, check out log usage
*/
DBCC LOGINFO('AdventureWorks2014');
GO
--note number of VLFs:



/*
	Modify some data and then check VLFs
*/
USE [AdventureWorks2014];
GO

UPDATE [Person].[Person]
SET [LastName] = 'Stark'
WHERE [FirstName] = 'Tony';

DBCC LOGINFO('AdventureWorks2014');
GO

UPDATE [Person].[Person]
SET [LastName] = 'Stark'
WHERE [FirstName] like 'T%';

DBCC LOGINFO('AdventureWorks2014');
GO


/*
	Try to checkpoint as we did with SIMPLE recovery model
*/
CHECKPOINT;
GO

DBCC LOGINFO('AdventureWorks2014');
GO


/*
	What does the UI give us if we script out the
	transaction log backup?
*/
BACKUP LOG [AdventureWorks2014] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_TLog1.trn' 
	WITH NOFORMAT, 
	NOINIT,  
	NAME = N'AdventureWorks2014-Transaction Log  Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10;
GO


DBCC LOGINFO('AdventureWorks2014');
GO

/*
	Sidebar: Check the ERRORLOG - notice that the backup was logged
	disable that...
*/
DBCC TRACEON (3226, -1)

/*
	Modify more data
*/
USE [AdventureWorks2014];
GO

UPDATE [Person].[Person]
SET [LastName] = 'Wayne'
WHERE [FirstName] like 'Bruce';

UPDATE [Person].[Person]
SET [LastName] = 'Howlett'
WHERE [FirstName] like 'James';

UPDATE [Person].[Person]
SET [LastName] = 'Xavier'
WHERE [FirstName] like 'Charles';

UPDATE [Person].[Person]
SET [LastName] = 'Bristow'
WHERE [FirstName] like 'Sydney';

UPDATE [Person].[Person]
SET [Title] = 'Ms.'
WHERE [LastName] = 'Bristow';

UPDATE [Person].[Person]
SET [Title] = 'Mr.'
WHERE [LastName] IN ('Stark', 'Wayne', 'Howlett', 'Xavier');

UPDATE [Person].[Person]
SET [ModifiedDate] = GETDATE()
WHERE [LastName] IN ('Stark', 'Wayne', 'Howlett', 'Bristow', 'Xavier');


DBCC LOGINFO('AdventureWorks2014');
GO


/*
	Slightly different syntax
	Removed SKIP, NOREWIND, NOUNLOAD, NOFORMAT
*/
BACKUP LOG [AdventureWorks2014] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_TLog2.trn'
	WITH INIT,  
	STATS = 10;
GO

DBCC LOGINFO('AdventureWorks2014');
GO


UPDATE [Person].[Person]
SET [ModifiedDate] = GETDATE()
WHERE [PersonType] = 'SC' ;

DBCC LOGINFO('AdventureWorks2014');
GO


BACKUP LOG [AdventureWorks2014] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_TLog3.trn'
	WITH INIT,  
	STATS = 10;
GO


DBCC LOGINFO('AdventureWorks2014');
GO


UPDATE [Person].[Person]
SET [ModifiedDate] = GETDATE()
WHERE [PersonType] <> 'IN' ;


DBCC LOGINFO('AdventureWorks2014');
GO


BACKUP LOG [AdventureWorks2014] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_TLog4.trn'
	WITH INIT,  
	STATS = 10;
GO
