/*
name | recovery model_desc 
magazyn | FULL

SELECT name, recovery_model_desc  FROM sys.databases  WHERE name = 'magazyn' ;  
GO

2.
Command(s) completed successfully.

USE magazyn;  
GO
ALTER DATABASE magazyn SET RECOVERY SIMPLE;

3.
Command(s) completed successfully.

USE magazyn ;
GO
EXEC sp_addumpdevice 'DISK', 'maga','C:\backup_new\MagazynBackup.bak';
GO

 4.
 name | type | type_desc | physical_name
  maga	2	DISK	C:\backup_new\MagazynBackup.bak
  
USE magazyn ;
GO
SELECT * FROM   sys.backup_devices;
GO
 
5.
33 percent processed.
62 percent processed.
91 percent processed.
Processed 192 pages for database 'magazyn', file 'magazyn' on file 1.
Processed 1 pages for database 'magazyn', file 'magazyn_log' on file 1.
BACKUP DATABASE successfully processed 193 pages in 0.205 seconds (7.321 MB/sec).
 

  USE magazyn;
  GO 
  BACKUP DATABASE magazyn 
  TO DISK='C:\backup_new\MagazynBackup.bak' WITH FORMAT,
  DESCRIPTION = 'Pelna kopia bazy danych ' ,
  NAME = 'pelna_kopia',
  STATS=30  ;
  GO 
   
6.
Command(s) completed successfully.

USE magazyn
GO
CREATE TABLE Test(nr int identity, tekst varchar(20));
GO

DECLARE @i int
SET @i=1
WHILE @i<10000
BEGIN
INSERT INTO Test VALUES('rekord test ' + CAST(@i AS varchar(5)) )
SET @i=@i+1
END
SELECT * FROM Test

7.
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

USE magazyn;
GO 
BACKUP LOG magazyn WITH 
DESCRIPTION = 'Dziennik bazy danych ' ,
STATS=30  ;
GO
   
8.
Command(s) completed successfully.
     
USE magazyn;
GO
ALTER DATABASE magazyn SET RECOVERY BULK_LOGGED;
 
9.  
Trzeba zrobic ponownie kopie BD 
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

USE magazyn;
GO 
BACKUP LOG magazyn WITH 
DESCRIPTION = 'Dziennik bazy danych ' ,
STATS=30  ;
GO
   
   
1.

USE magazyn;
GO
SELECT * FROM [magazyn].[dbo].[Test];
GO
 
2.
33 percent processed.
62 percent processed.
92 percent processed.
Processed 240 pages for database 'magazyn', file 'magazyn' on file 1.
Processed 2 pages for database 'magazyn', file 'magazyn_log' on file 1.
BACKUP DATABASE successfully processed 242 pages in 0.445 seconds (4.248 MB/sec).

  USE magazyn;
  GO
  ALTER DATABASE magazyn SET RECOVERY FULL;
      
  USE magazyn;
  GO 
  BACKUP DATABASE magazyn TO 
  DISK = 'C:\backup_new\MagazynBackup1.bak' ,
  DISK='C:\backup_new\MagazynBackup2.bak' ,
  DISK='C:\backup_new\MagazynBackup3.bak' 
  MIRROR TO 
  DISK = 'C:\backup_new\MagazynBackup1m.bak' ,
  DISK='C:\backup_new\MagazynBackup2m.bak' ,
  DISK='C:\backup_new\MagazynBackup3m.bak' 
  WITH EXPIREDATE='2015-09-12', FORMAT ,
  DESCRIPTION = 'Mirror bazy danych ' ,
  NAME = 'pelna_kopia',
  STATS=30  ;
  GO 
   
3. 
USE magazyn
GO
DECLARE @i int
SET @i=1
WHILE @i<10000
BEGIN
INSERT INTO Test VALUES('differ test ' + CAST(@i AS varchar(5)) )
SET @i=@i+1
END
SELECT * FROM Test

4.
33 percent processed.
66 percent processed.
92 percent processed.
Processed 120 pages for database 'magazyn', file 'magazyn' on file 1.
Processed 2 pages for database 'magazyn', file 'magazyn_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 122 pages in 0.025 seconds (37.832 MB/sec).

       
  USE magazyn;
  GO 
  BACKUP DATABASE magazyn TO 
  DISK = 'C:\backup_new\MagazynBackup1.bak' ,
  DISK = 'C:\backup_new\MagazynBackup2.bak' ,
  DISK = 'C:\backup_new\MagazynBackup3.bak' 
  MIRROR TO 
  DISK = 'C:\backup_new\MagazynBackup1m.bak' ,
  DISK = 'C:\backup_new\MagazynBackup2m.bak' ,
  DISK = 'C:\backup_new\MagazynBackup3m.bak' 
  WITH  RETAINDAYS=10, DIFFERENTIAL ,
  DESCRIPTION = 'Mirror bazy danych ' ,
  NAME = 'roznicowa_kopia',
  STATS=30  ;
  GO 
   
5.
USE magazyn
GO
DECLARE @i int
SET @i=1
WHILE @i<10000
BEGIN
INSERT INTO Test VALUES('log test ' + CAST(@i AS varchar(5)) )
SET @i=@i+1
END
SELECT * FROM Test

6.
30 percent processed.
60 percent processed.
90 percent processed.
Processed 1261 pages for database 'magazyn', file 'magazyn_log' on file 1.
BACKUP LOG successfully processed 1261 pages in 1.019 seconds (9.666 MB/sec).

   
  USE magazyn;
  GO 
  BACKUP LOG magazyn TO 
  DISK = 'C:\backup_new\MagazynBackup1.bak' ,
  DISK='C:\backup_new\MagazynBackup2.bak' ,
  DISK='C:\backup_new\MagazynBackup3.bak' 
  MIRROR TO 
  DISK = 'C:\backup_new\MagazynBackup1m.bak' ,
  DISK='C:\backup_new\MagazynBackup2m.bak' ,
  DISK='C:\backup_new\MagazynBackup3m.bak' 
  WITH  EXPIREDATE = '2015-09-12' ,
  DESCRIPTION = 'Log bazy danych ' ,
  NAME = 'log_bazy',
  STATS=30;
  GO 
   
7.
name | description
pelna_kopia | Pelna kopia bazy danych 

USE magazyn;
GO
RESTORE HEADERONLY FROM DISK = 'C:\backup_new\MagazynBackup1.bak';
GO 
   
8.
31 percent processed.
60 percent processed.
92 percent processed.
Processed 304 pages for database 'magazyn', file 'magazyn' on file 1.
Processed 1 pages for database 'magazyn', file 'magazyn_log' on file 1.
BACKUP DATABASE successfully processed 305 pages in 0.141 seconds (16.850 MB/sec).


USE magazyn;
GO 
BACKUP DATABASE magazyn TO 
DISK = 'C:\backup_new\MagazynNietrwala1.bak' ,
DISK='C:\backup_new\MagazynNietrwala2.bak' ,
DISK='C:\backup_new\MagazynNietrwala3.bak' 
WITH  EXPIREDATE='2015-09-12' , COPY_ONLY ,
DESCRIPTION = 'Mirror bazy danych do domu' ,
NAME = 'Dom_mirror_backup',
STATS=30  ;
GO 

9.
USE magazyn;
GO
RESTORE HEADERONLY FROM DISK = 'C:\backup_new\MagazynBackup1.bak';
GO
 
   ////////C
1.
INSERT INTO Test VALUES ('przed DELETE');
GO
SELECT * FROM Test
GO

2. 
(29998 row(s) affected)

BEGIN TRAN DelData WITH MARK
DELETE FROM Test;
COMMIT TRAN DelData

3.
Backup taila.
USE master;
GO
BACKUP LOG magazyn TO 
DISK = 'C:\backup_new\TailBackup1.bak'
WITH NO_TRUNCATE, NAME='Tail LOG ', 
DESCRIPTION='kopia dziennika po awarii', STATS=30;
GO 

Command(s) completed successfully.

USE magazyn;
GO
ALTER DATABASE magazyn SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE master ;
GO 
RESTORE DATABASE magazyn FROM 
DISK = 'C:\backup_new\MagazynBackup1.bak',
DISK = 'C:\backup_new\MagazynBackup2.bak',
DISK = 'C:\backup_new\MagazynBackup3.bak'
WITH NORECOVERY; 
GO

Processed 224 pages for database 'magazyn', file 'magazyn' on file 1.
Processed 1 pages for database 'magazyn', file 'magazyn_log' on file 1.
RESTORE DATABASE successfully processed 225 pages in 0.053 seconds (33.166 MB/sec).

	
4.
USE magazyn;
GO
RESTORE HEADERONLY FROM DISK = 'C:\backup_new\MagazynBackup1.bak' ;
GO 
   
    
////////ALTER DATABASE magazyn SET MULTI_USER;
      */