/*============================================================================
  File:     Salvage.sql

  Summary:  This script shows data salvage behavior

  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com

  (c) 2016, 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.
============================================================================*/

USE [master];
GO
IF DATABASEPROPERTYEX (N'SalesDB_Snapshot', N'Version') > 0
	DROP DATABASE [SalesDB_Snapshot];
GO
ALTER DATABASE [SalesDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE SalesDB
	FROM DISK = N'D:\SQLskills\DemoBackups\SalesDB2014.bak'
WITH STATS = 10, REPLACE;
GO
-- Create the snapshot
CREATE DATABASE [SalesDB_Snapshot]
ON (
	NAME = N'SalesDBData',
	FILENAME = N'C:\SQLskills\SalesDBData.mdfss')
AS SNAPSHOT OF [SalesDB];
GO

-- Show size on disk of snapshot
SELECT [vfs].[file_id] AS [File ID],
	[mf].[name] AS [Filename],
	[vfs].[size_on_disk_bytes] / 1024 AS [Sparse File PHYSICAL Size (KB)]
FROM sys.dm_io_virtual_file_stats (NULL, NULL) AS [vfs]
JOIN sys.master_files AS [mf]
	ON [vfs].[file_id] = [mf].[file_id]
WHERE [mf].[database_id] = [vfs].[database_id]
	AND [mf].[database_id] = DB_ID (N'SalesDB_Snapshot');
GO

-- Update the source database in a transaction
DROP TABLE [SalesDB].[dbo].[Sales];
GO

-- Show size on disk of snapshot
SELECT [vfs].[file_id] AS [File ID],
	[mf].[name] AS [Filename],
	[vfs].[size_on_disk_bytes] / 1024 AS [Sparse File PHYSICAL Size (KB)]
FROM sys.dm_io_virtual_file_stats (NULL, NULL) AS [vfs]
JOIN sys.master_files AS [mf]
	ON [vfs].[file_id] = [mf].[file_id]
WHERE [mf].[database_id] = [vfs].[database_id]
	AND [mf].[database_id] = DB_ID (N'SalesDB_Snapshot');
GO


-- Huh? Why wasn't it all pushed into the snapshot?

SELECT COUNT (*) FROM [SalesDB_Snapshot].[dbo].[Sales];
GO

-- We can recreate the sales table from the snapshot

-- Script the table in Object Explorer and
-- recreate it

SET IDENTITY_INSERT
	[SalesDB].[dbo].[Sales] ON;
GO

INSERT [SalesDB].[dbo].[Sales] (
	[SalesID],
	[SalesPersonID],
	[CustomerID],
	[ProductID],
	[Quantity])
SELECT *
	FROM [SalesDB_Snapshot].[dbo].[Sales] 
	WHERE [SalesID] < 100000;
GO

SET IDENTITY_INSERT
	[SalesDB].[dbo].[Sales] OFF;
GO

-- Or we can revert

-- But beforehand, what does the SalesDB log look like?
DBCC LOGINFO (N'SalesDB');
DBCC SQLPERF (LOGSPACE);
GO

-- Now revert (make sure Object Explorer
-- is disconnected) - might have to kill SSMS
USE [master];
GO

RESTORE DATABASE [SalesDB]
FROM DATABASE_SNAPSHOT = N'SalesDB_Snapshot';
GO

-- Cool, but...
DBCC LOGINFO (N'SalesDB');
DBCC SQLPERF (LOGSPACE);
GO
