/*============================================================================
  File:     9_Backups_copy only full.sql

  Summary:  This script shows how to perform a
			partial restore from scratch

  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by SQLskills.com

  (c) SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  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.
============================================================================*/

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


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

/*
	Backup the log
*/
BACKUP LOG [AdventureWorks2014] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_TLog1Test.trn' 
	WITH NOFORMAT, 
	NOINIT,  
	NAME = N'AdventureWorks2014-Transaction Log  Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10;
GO


/*
	Backup the database with COPY ONLY
*/
BACKUP DATABASE [AdventureWorks2014] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_BaseFullCopyOnly.bak' 
	WITH NOFORMAT,
	COPY_ONLY, 
	INIT,   
	COMPRESSION,  
	STATS = 10;
GO

/*
	restore with a move
*/
USE [master];
GO

RESTORE DATABASE [AdventureWorks2014_CO_Primary] 
	FROM  DISK = N'C:\Backups\SQL2014\AW2014_BaseFullCopyOnly.bak' 
	WITH  FILE = 1,  
	MOVE N'AdventureWorks2014_Data' TO N'C:\Databases\AW_CopyOnly_Restore\AdventureWorks2014_Data.mdf',  
	MOVE N'AdventureWorks2014_Log' TO N'C:\Databases\AW_CopyOnly_Restore\AdventureWorks2014_Log.ldf',  
	NOUNLOAD,  
	REPLACE,  
	STATS = 5
GO

/*
	take a diff of it
*/
BACKUP DATABASE [AdventureWorks2014_CO_Primary] 
	TO  DISK = N'C:\Backups\SQL2014\AW2014_BaseFullCO_Primary_Diff.bak' 
	WITH  DIFFERENTIAL , 
	NOFORMAT, 
	INIT,  
	NAME = N'AdventureWorks2014_CO_Primary-Diff Database Backup', 
	STATS = 10
GO

/*
	restore copy only again, with no recovery!!
*/
USE [master];
GO

RESTORE DATABASE [AdventureWorks2014_CO_Secondary] 
	FROM  DISK = N'C:\Backups\SQL2014\AW2014_BaseFullCopyOnly.bak' 
	WITH  FILE = 1,  
	MOVE N'AdventureWorks2014_Data' TO N'C:\Databases\AW_CopyOnly_Secondary\AdventureWorks2014_Data.mdf',  
	MOVE N'AdventureWorks2014_Log' TO N'C:\Databases\AW_CopyOnly_Secondary\AdventureWorks2014_Log.ldf',  
	NOUNLOAD,  
	REPLACE,  
	NORECOVERY,
	STATS = 5
GO

/*
	restore the diff, with no recovery...
	This will fail!
	It's because the DIFF can only be applied against the last FULL
	that was taken that is NOT COPY_ONLY
*/
USE [master];
GO

RESTORE DATABASE [AdventureWorks2014_CO_Secondary] 
	FROM  DISK = N'C:\Backups\SQL2014\AW2014_BaseFullCO_Primary_Diff.bak' 
	WITH  FILE = 1,  
	NORECOVERY,
	STATS = 5
GO


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

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

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

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

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

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


DROP DATABASE AdventureWorks2014_CO_Primary;
GO
DROP DATABASE AdventureWorks2014_CO_Secondary;
GO




/*
	Proper sequence for restore, starting
	with the COPY_ONLY backup
	restore with a move
*/
USE [master];
GO

RESTORE DATABASE [AdventureWorks2014_CO_Primary] 
	FROM  DISK = N'C:\Backups\SQL2014\AW2014_BaseFullCopyOnly.bak' 
	WITH  FILE = 1,  
	MOVE N'AdventureWorks2014_Data' TO N'C:\Databases\AW_CopyOnly_Restore\AdventureWorks2014_Data.mdf',  
	MOVE N'AdventureWorks2014_Log' TO N'C:\Databases\AW_CopyOnly_Restore\AdventureWorks2014_Log.ldf',  
	NOUNLOAD,  
	REPLACE,  
	STATS = 5
GO


/*
	Back it up
*/
BACKUP DATABASE [AdventureWorks2014_CO_Primary] 
	TO  DISK = N'C:\Backups\SQL2014\AdventureWorks2014_CO_Primary_Full.bak' 
	WITH NOFORMAT, 
	INIT,   
	COMPRESSION,  
	STATS = 10;
GO

/*
	take a diff of it
*/
BACKUP DATABASE [AdventureWorks2014_CO_Primary] 
	TO  DISK = N'C:\Backups\SQL2014\AdventureWorks2014_CO_Primary_Diff.bak' 
	WITH  DIFFERENTIAL , 
	NOFORMAT, 
	INIT,  
	NAME = N'AdventureWorks2014_CO_Primary-Diff Database Backup', 
	STATS = 10
GO


/*
	restore copy only again, with no recovery!!
*/
USE [master];
GO

RESTORE DATABASE [AdventureWorks2014_CO_Secondary] 
	FROM  DISK = N'C:\Backups\SQL2014\AdventureWorks2014_CO_Primary_Full.bak' 
	WITH  FILE = 1,  
	MOVE N'AdventureWorks2014_Data' TO N'C:\Databases\AW_CopyOnly_Secondary\AdventureWorks2014_Data.mdf',  
	MOVE N'AdventureWorks2014_Log' TO N'C:\Databases\AW_CopyOnly_Secondary\AdventureWorks2014_Log.ldf',  
	NOUNLOAD,  
	REPLACE,  
	NORECOVERY,
	STATS = 5
GO

/*
	restore the diff, with no recovery...
*/
USE [master];
GO

RESTORE DATABASE [AdventureWorks2014_CO_Secondary] 
	FROM  DISK = N'C:\Backups\SQL2014\AdventureWorks2014_CO_Primary_Diff.bak' 
	WITH  FILE = 1,  
	NORECOVERY,
	STATS = 5
GO
