- /*
- 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;
- */