/*

Tworzenie migawek baz danych

*/

/* --opis składni polecenia tworzącego migawkę bazy danych
CREATE DATABASE database_snapshot_name
    ON
        (
        NAME = logical_file_name,
        FILENAME = 'os_file_name'
        ) [ ,...n ]
    AS SNAPSHOT OF source_database_name

*/


CREATE DATABASE AdventureWorks2008_200808010600
ON (NAME = 'AdventureWorks2008'
 , FILENAME = 'D:\R03\AdventureWorks2008_200808010600.SNP')
AS SNAPSHOT OF AdventureWorks2008;


CREATE DATABASE AdventureWorks2008_200808010600
ON (NAME = 'AdventureWorks2008'
 , FILENAME = 'D:\R03\AdventureWorks2008_200808010600.SNP')
AS SNAPSHOT OF AdventureWorks2008;


/*

Migawki baz danych w konsoli SSMS

*/

USE master
GO
SELECT S1.[Name] AS [Baza źródłowa]
, S2.[Name] 'Migawka'
FROM sys.databases S1
INNER JOIN sys.databases S2
ON S1.database_id=S2.source_database_id;

/*

Zastosowanie migawek baz danych

*/

RESTORE DATABASE AdventureWorks2008 
FROM DATABASE_SNAPSHOT = 'AdventureWorks2008_200808010600.SNP';


/*

Kompresja kopii zapasowych (dotyczy edycji Enterprise)

*/
USE master
GO
EXEC sp_configure 'backup compression default',1;
RECONFIGURE;

/*

Zmiana domyślnych ustawień kompresji kopii zapasowej

*/
BACKUP DATABASE AdventureWorks2008
TO DISK = N'D:\R03\AdventureWorks2008.bak'
WITH NO_COMPRESSION;

/*

Porównanie kopii zapasowych skompresowanych i nieskompresowanych

*/
SELECT backup_size/compressed_backup_size as [współczynnik kompresji]
FROM msdb..backupset;


BACKUP DATABASE AdventureWorks2008
TO DISK = N'D:\R03\AdventureWorks2008.bak'
WITH NO_COMPRESSION;


BACKUP DATABASE AdventureWorks2008
TO DISK = N'D:\R03\AdventureWorks2008.bak'
WITH NO_COMPRESSION;


SELECT CAST(backup_size/compressed_backup_size AS DECIMAL(5,2)) AS [wsp_kompresji]
  ,CAST(backup_size/1024/1024   AS DECIMAL(5,2)) AS [rozmiar pliku bez kompresji - MB]
  ,CAST(compressed_backup_size/1024/1024 AS DECIMAL(5,2)) AS [rozmiar pliku skompresowanego - MB]
FROM msdb..backupset
WHERE database_name='AdventureWorks2008';

/*

Konfiguracja replikacji

*/

SELECT [LoginID]
      ,[BirthDate]
FROM [AdventureWorks2008].[HumanResources].[Employee]
WHERE [EmployeeID]=275


  UPDATE [AdventureWorks2008].[HumanResources].[Employee]
  SET BirthDate = '1976-01-01'
  WHERE [EmployeeID]=275

SELECT [LoginID]
      ,[BirthDate]
FROM [AdventureWorks2008].[HumanResources].[Employee]
WHERE [EmployeeID]=275

