Facebook
From Piotr, 5 Years ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 214
  1. /*
  2. name | recovery model_desc
  3. magazyn | FULL
  4.  
  5. SELECT name, recovery_model_desc  FROM sys.databases  WHERE name = 'magazyn' ;  
  6. GO
  7.  
  8. 2.
  9. Command(s) completed successfully.
  10.  
  11. USE magazyn;  
  12. GO
  13. ALTER DATABASE magazyn SET RECOVERY SIMPLE;
  14.  
  15. 3.
  16. Command(s) completed successfully.
  17.  
  18. USE magazyn ;
  19. GO
  20. EXEC sp_addumpdevice 'DISK', 'maga','C:\backup_new\MagazynBackup.bak';
  21. GO
  22.  
  23.  4.
  24.  name | type | type_desc | physical_name
  25.   maga  2       DISK    C:\backup_new\MagazynBackup.bak
  26.  
  27. USE magazyn ;
  28. GO
  29. SELECT * FROM   sys.backup_devices;
  30. GO
  31.  
  32. 5.
  33. 33 percent processed.
  34. 62 percent processed.
  35. 91 percent processed.
  36. Processed 192 pages for database 'magazyn', file 'magazyn' on file 1.
  37. Processed 1 pages for database 'magazyn', file 'magazyn_log' on file 1.
  38. BACKUP DATABASE successfully processed 193 pages in 0.205 seconds (7.321 MB/sec).
  39.  
  40.  
  41.   USE magazyn;
  42.   GO
  43.   BACKUP DATABASE magazyn
  44.   TO DISK='C:\backup_new\MagazynBackup.bak' WITH FORMAT,
  45.   DESCRIPTION = 'Pelna kopia bazy danych ' ,
  46.   NAME = 'pelna_kopia',
  47.   STATS=30  ;
  48.   GO
  49.    
  50. 6.
  51. Command(s) completed successfully.
  52.  
  53. USE magazyn
  54. GO
  55. CREATE TABLE Test(nr int identity, tekst varchar(20));
  56. GO
  57.  
  58. DECLARE @i int
  59. SET @i=1
  60. WHILE @i<10000
  61. BEGIN
  62. INSERT INTO Test VALUES('rekord test ' + CAST(@i AS varchar(5)) )
  63. SET @i=@i+1
  64. END
  65. SELECT * FROM Test
  66.  
  67. 7.
  68. Msg 4208, Level 16, State 1, Line 1
  69. The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
  70. Msg 3013, Level 16, State 1, Line 1
  71. BACKUP LOG is terminating abnormally.
  72.  
  73. USE magazyn;
  74. GO
  75. BACKUP LOG magazyn WITH
  76. DESCRIPTION = 'Dziennik bazy danych ' ,
  77. STATS=30  ;
  78. GO
  79.    
  80. 8.
  81. Command(s) completed successfully.
  82.      
  83. USE magazyn;
  84. GO
  85. ALTER DATABASE magazyn SET RECOVERY BULK_LOGGED;
  86.  
  87. 9.  
  88. Trzeba zrobic ponownie kopie BD
  89. Msg 4214, Level 16, State 1, Line 1
  90. BACKUP LOG cannot be performed because there is no current database backup.
  91. Msg 3013, Level 16, State 1, Line 1
  92. BACKUP LOG is terminating abnormally.
  93.  
  94. USE magazyn;
  95. GO
  96. BACKUP LOG magazyn WITH
  97. DESCRIPTION = 'Dziennik bazy danych ' ,
  98. STATS=30  ;
  99. GO
  100.    
  101.    
  102. 1.
  103.  
  104. USE magazyn;
  105. GO
  106. SELECT * FROM [magazyn].[dbo].[Test];
  107. GO
  108.  
  109. 2.
  110. 33 percent processed.
  111. 62 percent processed.
  112. 92 percent processed.
  113. Processed 240 pages for database 'magazyn', file 'magazyn' on file 1.
  114. Processed 2 pages for database 'magazyn', file 'magazyn_log' on file 1.
  115. BACKUP DATABASE successfully processed 242 pages in 0.445 seconds (4.248 MB/sec).
  116.  
  117.   USE magazyn;
  118.   GO
  119.   ALTER DATABASE magazyn SET RECOVERY FULL;
  120.      
  121.   USE magazyn;
  122.   GO
  123.   BACKUP DATABASE magazyn TO
  124.   DISK = 'C:\backup_new\MagazynBackup1.bak' ,
  125.   DISK='C:\backup_new\MagazynBackup2.bak' ,
  126.   DISK='C:\backup_new\MagazynBackup3.bak'
  127.   MIRROR TO
  128.   DISK = 'C:\backup_new\MagazynBackup1m.bak' ,
  129.   DISK='C:\backup_new\MagazynBackup2m.bak' ,
  130.   DISK='C:\backup_new\MagazynBackup3m.bak'
  131.   WITH EXPIREDATE='2015-09-12', FORMAT ,
  132.   DESCRIPTION = 'Mirror bazy danych ' ,
  133.   NAME = 'pelna_kopia',
  134.   STATS=30  ;
  135.   GO
  136.    
  137. 3.
  138. USE magazyn
  139. GO
  140. DECLARE @i int
  141. SET @i=1
  142. WHILE @i<10000
  143. BEGIN
  144. INSERT INTO Test VALUES('differ test ' + CAST(@i AS varchar(5)) )
  145. SET @i=@i+1
  146. END
  147. SELECT * FROM Test
  148.  
  149. 4.
  150. 33 percent processed.
  151. 66 percent processed.
  152. 92 percent processed.
  153. Processed 120 pages for database 'magazyn', file 'magazyn' on file 1.
  154. Processed 2 pages for database 'magazyn', file 'magazyn_log' on file 1.
  155. BACKUP DATABASE WITH DIFFERENTIAL successfully processed 122 pages in 0.025 seconds (37.832 MB/sec).
  156.  
  157.        
  158.   USE magazyn;
  159.   GO
  160.   BACKUP DATABASE magazyn TO
  161.   DISK = 'C:\backup_new\MagazynBackup1.bak' ,
  162.   DISK = 'C:\backup_new\MagazynBackup2.bak' ,
  163.   DISK = 'C:\backup_new\MagazynBackup3.bak'
  164.   MIRROR TO
  165.   DISK = 'C:\backup_new\MagazynBackup1m.bak' ,
  166.   DISK = 'C:\backup_new\MagazynBackup2m.bak' ,
  167.   DISK = 'C:\backup_new\MagazynBackup3m.bak'
  168.   WITH  RETAINDAYS=10, DIFFERENTIAL ,
  169.   DESCRIPTION = 'Mirror bazy danych ' ,
  170.   NAME = 'roznicowa_kopia',
  171.   STATS=30  ;
  172.   GO
  173.    
  174. 5.
  175. USE magazyn
  176. GO
  177. DECLARE @i int
  178. SET @i=1
  179. WHILE @i<10000
  180. BEGIN
  181. INSERT INTO Test VALUES('log test ' + CAST(@i AS varchar(5)) )
  182. SET @i=@i+1
  183. END
  184. SELECT * FROM Test
  185.  
  186. 6.
  187. 30 percent processed.
  188. 60 percent processed.
  189. 90 percent processed.
  190. Processed 1261 pages for database 'magazyn', file 'magazyn_log' on file 1.
  191. BACKUP LOG successfully processed 1261 pages in 1.019 seconds (9.666 MB/sec).
  192.  
  193.    
  194.   USE magazyn;
  195.   GO
  196.   BACKUP LOG magazyn TO
  197.   DISK = 'C:\backup_new\MagazynBackup1.bak' ,
  198.   DISK='C:\backup_new\MagazynBackup2.bak' ,
  199.   DISK='C:\backup_new\MagazynBackup3.bak'
  200.   MIRROR TO
  201.   DISK = 'C:\backup_new\MagazynBackup1m.bak' ,
  202.   DISK='C:\backup_new\MagazynBackup2m.bak' ,
  203.   DISK='C:\backup_new\MagazynBackup3m.bak'
  204.   WITH  EXPIREDATE = '2015-09-12' ,
  205.   DESCRIPTION = 'Log bazy danych ' ,
  206.   NAME = 'log_bazy',
  207.   STATS=30;
  208.   GO
  209.    
  210. 7.
  211. name | description
  212. pelna_kopia | Pelna kopia bazy danych
  213.  
  214. USE magazyn;
  215. GO
  216. RESTORE HEADERONLY FROM DISK = 'C:\backup_new\MagazynBackup1.bak';
  217. GO
  218.    
  219. 8.
  220. 31 percent processed.
  221. 60 percent processed.
  222. 92 percent processed.
  223. Processed 304 pages for database 'magazyn', file 'magazyn' on file 1.
  224. Processed 1 pages for database 'magazyn', file 'magazyn_log' on file 1.
  225. BACKUP DATABASE successfully processed 305 pages in 0.141 seconds (16.850 MB/sec).
  226.  
  227.  
  228. USE magazyn;
  229. GO
  230. BACKUP DATABASE magazyn TO
  231. DISK = 'C:\backup_new\MagazynNietrwala1.bak' ,
  232. DISK='C:\backup_new\MagazynNietrwala2.bak' ,
  233. DISK='C:\backup_new\MagazynNietrwala3.bak'
  234. WITH  EXPIREDATE='2015-09-12' , COPY_ONLY ,
  235. DESCRIPTION = 'Mirror bazy danych do domu' ,
  236. NAME = 'Dom_mirror_backup',
  237. STATS=30  ;
  238. GO
  239.  
  240. 9.
  241. USE magazyn;
  242. GO
  243. RESTORE HEADERONLY FROM DISK = 'C:\backup_new\MagazynBackup1.bak';
  244. GO
  245.  
  246.    ////////C
  247. 1.
  248. INSERT INTO Test VALUES ('przed DELETE');
  249. GO
  250. SELECT * FROM Test
  251. GO
  252.  
  253. 2.
  254. (29998 row(s) affected)
  255.  
  256. BEGIN TRAN DelData WITH MARK
  257. DELETE FROM Test;
  258. COMMIT TRAN DelData
  259.  
  260. 3.
  261. Backup taila.
  262. USE master;
  263. GO
  264. BACKUP LOG magazyn TO
  265. DISK = 'C:\backup_new\TailBackup1.bak'
  266. WITH NO_TRUNCATE, NAME='Tail LOG ',
  267. DESCRIPTION='kopia dziennika po awarii', STATS=30;
  268. GO
  269.  
  270. Command(s) completed successfully.
  271.  
  272. USE magazyn;
  273. GO
  274. ALTER DATABASE magazyn SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  275. USE master ;
  276. GO
  277. RESTORE DATABASE magazyn FROM
  278. DISK = 'C:\backup_new\MagazynBackup1.bak',
  279. DISK = 'C:\backup_new\MagazynBackup2.bak',
  280. DISK = 'C:\backup_new\MagazynBackup3.bak'
  281. WITH NORECOVERY;
  282. GO
  283.  
  284. Processed 224 pages for database 'magazyn', file 'magazyn' on file 1.
  285. Processed 1 pages for database 'magazyn', file 'magazyn_log' on file 1.
  286. RESTORE DATABASE successfully processed 225 pages in 0.053 seconds (33.166 MB/sec).
  287.  
  288.        
  289. 4.
  290. USE magazyn;
  291. GO
  292. RESTORE HEADERONLY FROM DISK = 'C:\backup_new\MagazynBackup1.bak' ;
  293. GO
  294.    
  295.    
  296. ////////ALTER DATABASE magazyn SET MULTI_USER;
  297.       */