Chere! Chere! N'ezie, nke a abụghị akụkọ ọzọ gbasara ụdị nkwado ndabere nke SQL Server. Agaghị m ekwu maka ọdịiche dị n'etiti ụdị mgbake na otu esi emeso log toro eto.
Ikekwe (ma eleghị anya), mgbe ị gụchara akwụkwọ a, ị ga-enwe ike ijide n'aka na ndabere nke ewepụrụ gị site na iji ụkpụrụ ọkọlọtọ ga-ewepụ n'abalị echi, nke ọma, 1.5 ugboro ngwa ngwa. Na naanị n'ihi na ị na-eji ntakịrị ihe ndabere DATABASE paramita.
Ọ bụrụ na ọdịnaya nke post ahụ doro gị anya, ọ dị m nwute. Agụrụ m ihe niile Google nwetara maka nkebi ahịrịokwu "habr sql ndabere nkesa", na ọ bụghị otu isiokwu ka m hụrụ aha ọ bụla nke eziokwu ahụ bụ na enwere ike imetụta oge ndabere n'ụzọ ụfọdụ site na iji paramita.
M ga-adọta uche gị ozugbo na nkwupụta nke Alexander Gladchenko ():
Agbanwela BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE paramita na mmepụta. A na-eme ha naanị maka ide akụkọ ndị dị otú ahụ. Na omume, ị ga-ewepụ nsogbu ncheta n'oge na-adịghị anya.
N'ezie, ọ ga-adị mma ka ị bụrụ ndị kasị mara ihe na biputere ọdịnaya pụrụ iche, mana, ọ dị mwute ikwu, nke a abụghị ikpe. Enwere ma akụkọ / post nke asụsụ Bekee na nke Russian (m na-enwe mgbagwoju anya mgbe niile maka ihe m ga-akpọ ha nke ọma) na-etinye aka na isiokwu a. Nke a bụ ụfọdụ n'ime ndị m hụrụ: , , .
Ya mere, ibido na, aga m agbakwunyere syntax BACKUP gbadara ntakịrị site na (Site n'ụzọ, edere m n'elu gbasara BACKUP DATABASE, mana ihe a niile metụtara ma ndabere ndekọ azụmahịa yana nkwado ndabere dị iche, mana ikekwe na-enwe mmetụta na-edochaghị anya):
BACKUP DATABASE { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
<...>
[ WITH { <...>
| <general_WITH_options> [ ,...n ] } ]
[;]
<general_WITH_options> [ ,...n ]::=
<...>
--Media Set Options
<...>
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Data Transfer Options
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
<...><…> - ọ pụtara na e nwere ihe dị ebe ahụ, ma m wepụrụ ya n'ihi na ugbu a ọ dịghị mkpa na isiokwu.
Kedu ka ị na-esi ewere nchekwa ndabere? Kedu ka ha si "kuzie" ka esi ewere nkwado ndabere na mpaghara ọtụtụ ijeri akụkọ? N'ozuzu, ọ bụrụ na m kwesịrị ime ndabere otu oge nke ụfọdụ na-abụghị nnukwu nchekwa data, m ga-akpaghị aka dee ihe dị ka nke a:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееNa, n'ozuzu, eleghị anya 75-90% nke paramita niile a na-ekwukarị na isiokwu gbasara ndabere ka edepụtara ebe a. Ọfọn, enwekwara INIT, MAKA. Ị garala MSDN? Ị hụla na enwere nhọrọ maka otu ihuenyo na ọkara? M hụkwara...
O nwere ike ịbụ na ị ghọtara na n'ihu, anyị ga-ekwu maka paramita atọ fọdụrụ na ngọngọ mbụ nke koodu - BLOCKSIZE, BUFFERCOUNT na MAXTRANSFERSIZE. Nke a bụ nkọwa ha sitere na MSDN:
BLOCKSIZE = { blocksize | @ blocksize_variable } - na-egosi nha ngọngọ anụ ahụ na bytes. Nha akwadoro bụ 512, 1024, 2048, 4096, 8192, 16, 384, na 32 bytes (768 KB). Uru ndabara bụ 65 maka ngwaọrụ teepu yana 536 maka ngwaọrụ ndị ọzọ. Dịka oke a adịghị mkpa n'ihi na nkwupụta BACKUP na-ahọrọ na-akpaghị aka nha ngọngọ kwesịrị ekwesị maka ngwaọrụ ahụ. Ịtọba nha ngọngọ ahụ n'ụzọ doro anya na-ewepụ nhọrọ nha ngọngọ akpaka.
echekwabara = { ihe nchekwa | @ buffercount_variable } - Akọwapụta mkpokọta ọnụọgụ I/O nke a ga-eji maka ọrụ ndabere. Ị nwere ike ịkọwa uru integer ọ bụla dị mma, mana ọnụ ọgụgụ buru ibu nke nchekwa nwere ike ịkpata njehie na-enweghị ebe nchekwa n'ihi oghere adreesị mebere oke na usoro Sqlservr.exe.
A na-ekpebi mkpokọta oghere nke ndị na-echekwa ihe na-eji site na usoro a:
BUFFERCOUNT * MAXTRANSFERSIZE.
ỌKWUOZI = { maxtransfersize | @ maxtransfersize_variable } na-akọwapụta nha data kacha ukwuu, na bytes, iji gbanwee n'etiti SQL Server na mgbasa ozi ndabere ndabere. A na-akwado ọtụtụ bytes 65 (536 KB) ruo 64 bytes (4 MB).
M na-aṅụ iyi - Agụọla m nke a mbụ, mana ọ dịtụbeghị mgbe ọ bụla mmetụta ha nwere ike inwe na nrụpụta. Ọzọkwa, o doro anya na ọ dị m mkpa ime ụdị "ịpụta" ma kweta na ọbụna ugbu a aghọtaghị m nke ọma ihe ha na-eme. Eleghị anya ọ dị m mkpa ịgụkwu gbasara I/O echekwara na ịrụ ọrụ na draịvụ ike. Otu ụbọchị, m ga-eme nke a, mana maka ugbu a, enwere m ike dee edemede nke ga-elele ka ụkpụrụ ndị a si emetụta ọsọ a na-ewere nkwado ndabere.
Emere m obere nchekwa data, ihe dịka 10 GB n'ogo, tinye ya na SSD, tinye akwụkwọ ndekọ maka nkwado ndabere na mpaghara HDD.
M na-emepụta tebụl nwa oge iji chekwaa nsonaazụ ya (Enweghị m ya nwa oge, n'ihi ya enwere m ike igwu n'ime nsonaazụ ya n'ụzọ zuru ezu, ma ị na-ekpebi n'onwe gị):
DROP TABLE IF EXISTS ##bt_results;
CREATE TABLE ##bt_results (
id int IDENTITY (1, 1) PRIMARY KEY,
start_date datetime NOT NULL,
finish_date datetime NOT NULL,
backup_size bigint NOT NULL,
compressed_size bigint,
block_size int,
buffer_count int,
transfer_size int
);Ụkpụrụ nke edemede ahụ dị mfe - loops nested, nke ọ bụla na-agbanwe uru nke otu paramita, tinye paramita ndị a n'ime iwu BACKUP, chekwaa ndekọ ikpeazụ na akụkọ ihe mere eme site na msdb.dbo.backupset, hichapụ faịlụ ndabere na ntinye ọzọ. . Ebe ọ bụ na ewepụtara data mmezu nkwado ndabere na mpaghara ndabere, izi ezi na-efunahụ ya (enweghị akụkụ nke sekọnd), mana anyị ga-adị ndụ nke a.
Mbụ ị ga-eme ka xp_cmdshell ka ihichapụ ndabere (mgbe ahụ echefula ị gbanyụọ ya ma ọ bụrụ na ịchọghị ya):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOỌfọn, n'ezie:
DECLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH
COMPRESSION,
BLOCKSIZE = {bs},
BUFFERCOUNT = {bc},
MAXTRANSFERSIZE = {ts}';
DECLARE @sql AS nvarchar(max);
/* BLOCKSIZE values */
DECLARE @bs int = 4096,
@max_bs int = 65536;
/* BUFFERCOUNT values */
DECLARE @bc int = 7,
@min_bc int = 7,
@max_bc int = 800;
/* MAXTRANSFERSIZE values */
DECLARE @ts int = 524288, --512KB, default = 1024KB
@min_ts int = 524288,
@max_ts int = 4194304; --4MB
SELECT TOP 1
@bs = COALESCE (block_size, 4096),
@bc = COALESCE (buffer_count, 7),
@ts = COALESCE (transfer_size, 524288)
FROM ##bt_results
ORDER BY id DESC;
WHILE (@bs <= @max_bs)
BEGIN
WHILE (@bc <= @max_bc)
BEGIN
WHILE (@ts <= @max_ts)
BEGIN
SET @sql = REPLACE (REPLACE (REPLACE(@tmplt, N'{bs}', CAST(@bs AS nvarchar(50))), N'{bc}', CAST (@bc AS nvarchar(50))), N'{ts}', CAST (@ts AS nvarchar(50)));
EXEC (@sql);
INSERT INTO ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
SELECT TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size, @bs, @bc, @ts
FROM msdb.dbo.backupset
ORDER BY backup_set_id DESC;
EXEC xp_cmdshell 'del "D:SQLServerbackupbt.bak"', no_output;
SET @ts += @ts;
END
SET @bc += @bc;
SET @ts = @min_ts;
WAITFOR DELAY '00:00:05';
END
SET @bs += @bs;
SET @bc = @min_bc;
SET @ts = @min_ts;
ENDỌ bụrụ na ịchọrọ nkọwa na mberede maka ihe na-eme ebe a, dee na nkwupụta ma ọ bụ PM. Maka ugbu a, naanị m ga-agwa gị gbasara paramita m tinyere na BACKUP DATABASE.
Maka BLOCKSIZE anyị nwere ndepụta “mechiri emechi” nke ụkpụrụ, ma ejighị m BLOCKSIZE mee nkwado ndabere na mpaghara <4KB. Zụpụta nọmba ọ bụla nke nwere otutu 64KB - site na 64KB ruo 4MB. Ihe ndabara na sistemụ m bụ 1024KB, ewere m 512 - 1024 - 2048 - 4096.
Ọ siri ike na BUFFERCOUNT - ọ nwere ike ịbụ ọnụọgụ ọ bụla dị mma, mana njikọ ahụ kwuru . Ọ na-ekwukwa ka esi enweta ozi gbasara nke BUFFERCOUNT ndabere na-eme n'ezie - maka m ọ bụ 7. Ọ dịghị uru ịbelata ya, na njedebe nke elu achọpụtara nnwale - na BUFFERCOUNT = 896 na MAXTRANSFERSIZE = 4194304 nkwado ndabere na mpaghara dara. njehie (nke edere na njikọ dị n'elu):
Msg 3013, Ọkwa 16, Steeti 1, Ahịrị 7 BACKUP DATABASE na-akwụsị n'ụzọ nkịtị.
Msg 701, Ọkwa 17, steeti 123, ahịrị 7 Enweghị ebe nchekwa sistemu ezughi oke na ọdọ mmiri 'default' iji mee ajụjụ a.
Maka ntụnyere, m ga-ebu ụzọ gosi nsonaazụ nke ịgba ọsọ ndabere na-akọwapụtaghị paramita ọ bụla ma ọlị:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Ọfọn, ndabere na ndabere:
Haziri ibe 1070072 maka nchekwa data 'bt', faịlụ 'bt' na faịlụ 1.
Haziri ibe 2 maka nchekwa data 'bt', faịlụ 'bt_log' na faịlụ 1.
BACKUP DATABASE hazie nke ọma ibe 1070074 n'ime sekọnd 53.171 (157.227 MB/sk).
Edemede n'onwe ya, na-anwale paramita ahụ, na-arụ ọrụ n'ime awa ole na ole, nha niile dị . Na ebe a bụ nhọrọ nke nsonaazụ ya na oge igbu oge atọ kachasị mma (m gbalịrị ịmepụta eserese mara mma, ma na post ahụ, m ga-eme ya na tebụl, na na nkwupụta. gbakwunyere ).
SELECT TOP 7 WITH TIES
compressed_size,
block_size,
buffer_count,
transfer_size,
DATEDIFF(SECOND, start_date, finish_date) AS backup_time_sec
FROM ##bt_results
ORDER BY backup_time_sec ASC;
Ntị, a dị ezigbo mkpa ndetu si si :
Anyị nwere ike iji obi ike na-ekwu na mmekọrịta dị n'etiti paramita na nkwado ndabere na mpaghara ọsọ n'ime ndị a ranges nke ụkpụrụ bụ random, ọ dịghị ụkpụrụ. Ma ịpụ na paramita arụnyere n'ụzọ doro anya nwere mmetụta dị mma na nsonaazụ ya
Ndị ahụ. Naanị site na ijikwa ọkọlọtọ BACKUP ọkọlọtọ bụ uru okpukpu abụọ na oge iwepụ ndabere: 2 sekọnd, karịa 26 na mbido. Nke ahụ adịghị njọ, nri? Ma anyị kwesịrị ịhụ ihe na-eme na mweghachi ahụ. Gịnị ma ọ bụrụ na ọ na-ewe oge 53 ugbu a iji nwetaghachi?
Nke mbụ, ka anyị tụọ ogologo oge ọ na-ewe iji weghachi ndabere na ntọala ndabara:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Ọfọn, gị onwe gị maara na, ụzọ dị n'ebe ahụ, dochie adịghị edochi, mgbake abụghị mgbake. M na-eme ya dị ka nke a:
Haziri ibe 1070072 maka nchekwa data 'bt', faịlụ 'bt' na faịlụ 1.
Haziri ibe 2 maka nchekwa data 'bt', faịlụ 'bt_log' na faịlụ 1.
Weghachite DATABASE nke ọma hazie ibe 1070074 n'ime sekọnd 40.752 (205.141 MB/sk).
Ugbu a, m ga-agbalị iweghachi nkwado ndabere na mpaghara ejiri BLOCKSIZE, BUFFERCOUNT na MAXTRANSFERSIZE gbanwere.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304Weghachite DATABASE nke ọma hazie ibe 1070074 n'ime sekọnd 32.283 (258.958 MB/sk).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304Weghachite DATABASE nke ọma hazie ibe 1070074 n'ime sekọnd 32.682 (255.796 MB/sk).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152Weghachite DATABASE nke ọma hazie ibe 1070074 n'ime sekọnd 32.091 (260.507 MB/sk).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304Weghachite DATABASE nke ọma hazie ibe 1070074 n'ime sekọnd 32.401 (258.015 MB/sk).
Nkwupụta RESTORE DATABASE adịghị agbanwe n'oge mgbake; akọwapụtaghị paramita ndị a na ya; SQL Server n'onwe ya na-ekpebi ha site na nkwado ndabere. O doro anya na ọbụlagodi na mgbake nwere ike nweta uru - ihe fọrọ nke nta ka ọ bụrụ 20% ngwa ngwa (N'ikwu eziokwu, anaghị m etinye oge dị ukwuu na mgbake, agbapụrụ m ọtụtụ n'ime nkwado ndabere na mpaghara "ngwa ngwa" ma jide n'aka na ọ dịghị njọ.).
Ọ bụrụ na, ka m dokwuo anya na ndị a abụghị ụfọdụ paramita kacha mma maka onye ọ bụla. Ị nwere ike nweta parampat kacha mma maka onwe gị site na ịnwale. Enwetara m nsonaazụ ndị a, ị ga-enweta nke dị iche iche. Ma ị na-ahụ na ị nwere ike "na-emegharị" nkwado ndabere na mpaghara gị na ha nwere ike ịmepụta na ibuga ngwa ngwa.
Ana m akwadosi ike ka ị gụọ akwụkwọ ahụ n'ozuzu ya, n'ihi na enwere ike ịnwe nuances kpọmkwem na sistemụ gị.
Ebe ọ bụ na m malitere ide banyere nkwado ndabere na mpaghara, achọrọ m ide ozugbo banyere otu "njikarịcha", nke a na-ahụkarị karịa "nlegharị anya" (dịka m ghọtara, ọ dịkarịa ala ụfọdụ ihe nkwado ndabere na-eji ya, ikekwe yana paramita. akọwara ya na mbụ), mana a kọwabeghị ya na Habré.
Ọ bụrụ na anyị elee anya n'ahịrị nke abụọ na akwụkwọ ahụ, n'okpuru BACKUP DATABASE, anyị na-ahụ:
TO <backup_device> [ ,...n ]Kedu ihe ị chere ga-eme ma ọ bụrụ na ị kọwapụta ọtụtụ backup_devices? The syntax na-enye ya ohere. Ma ihe na-adọrọ mmasị ga-eme - nkwado ndabere na mpaghara ga-abụ "agbasa" n'ofe ngwaọrụ dị iche iche. Ndị ahụ. "ngwaọrụ" ọ bụla n'otu n'otu ga-abụ ihe na-abaghị uru, otu furu efu, furu efu ndabere dum. Mana kedu ka mmetọ dị otú ahụ ga-esi metụta ọsọ ndabere?
Ka anyị gbalịa ime nkwado ndabere na mpaghara "ngwaọrụ" abụọ dị n'akụkụ n'akụkụ n'ime otu nchekwa ahụ:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Ndị nna nke ụwa, gịnị kpatara eji eme nke a?
Haziri ibe 1070072 maka nchekwa data 'bt', faịlụ 'bt' na faịlụ 1.
Haziri ibe 2 maka nchekwa data 'bt', faịlụ 'bt'log' na faịlụ 1.
BACKUP DATABASE hazie nke ọma ibe 1070074 n'ime sekọnd 40.092 (208.519 MB/sk).
Ndabere ahụ ọ ghọrọ 25% ngwa ngwa site na-acha anụnụ anụnụ? Kedu ihe ma ọ bụrụ na anyị gbakwunye ngwaọrụ abụọ ọzọ?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE hazie nke ọma ibe 1070074 n'ime sekọnd 34.234 (244.200 MB/sk).
Na mkpokọta, uru ahụ bụ ihe dịka 35% nke oge ịweghachite ndabere naanị n'ihi eziokwu ahụ bụ na edere ya na faịlụ 4 na otu diski ozugbo. Achọpụtara m ọnụ ọgụgụ buru ibu - enweghị uru na laptọọpụ m, nke kachasị - ngwaọrụ 4. Maka gị - amaghị m, ịkwesịrị ịlele. Ọfọn, site n'ụzọ, ọ bụrụ na ị nwere ngwaọrụ ndị a - ndị a bụ diski dị iche iche n'ezie, ekele, uru kwesịrị ịdị mkpa karị.
Ugbu a, ka anyị kwuo banyere otu esi eweghachi obi ụtọ a. Iji mee nke a, ị ga-agbanwe iwu mgbake wee depụta ngwaọrụ niile:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;Weghachite DATABASE nke ọma hazie ibe 1070074 n'ime sekọnd 38.027 (219.842 MB/sk).
Obere ngwa ngwa, mana ebe dị nso, ọ bụghị ihe dị mkpa. N'ozuzu, a na-ewepụ nkwado ndabere na mpaghara ngwa ngwa ma weghachite n'otu ụzọ ahụ - ịga nke ọma? Ma mụ onwe m, ọ bụ ihe ịga nke ọma. Nke a dị mkpa, yabụ m ikwugharị - ọ bụrụ na ị ọ bụrụ na ị tufuo opekata mpe otu n'ime faịlụ ndị a, ị ga-atụfu ndabere niile.
Ọ bụrụ n’ileba anya na ndekọ na ozi ndabere gosipụtara site na iji Trace Flags 3213 na 3605, ị ga-achọpụta na mgbe ị na-akwado ọtụtụ ngwaọrụ, ọbụlagodi ọnụọgụ BUFFERCOUNT na-abawanye. Eleghị anya, ị nwere ike ịnwa ịhọrọ paramita kachasị mma maka BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, mana enweghị m ihe ịga nke ọma ozugbo, ọ dịkwa m umengwụ ịme ụdị nnwale ahụ ọzọ, mana maka ọnụọgụ faịlụ dị iche. Ma ọ bụ ihe ihere banyere wiil. Ọ bụrụ na ịchọrọ ịhazi ule dị otú ahụ n'ụlọ, ọ naghị esiri ike ịmegharị edemede ahụ.
N'ikpeazụ, ka anyị kwuo banyere ọnụahịa. Ọ bụrụ na e wepụrụ nkwado ndabere na mpaghara ọrụ nke ndị ọrụ, ịkwesịrị ịme usoro dị oke mkpa maka ịnwale, n'ihi na ọ bụrụ na ewepụrụ nkwado ndabere ngwa ngwa, diski ahụ na-emebi emebi karịa, ibu na processor na-abawanye (ị ka ga-akpakọ ya na ijiji), ya mere, n'ozuzu nzaghachi nke usoro ahụ na-ebelata.
Naanị egwu egwu, mana aghọtara m nke ọma na emeghị m mkpughe ọ bụla. Ihe edere n'elu bụ naanị ngosipụta nke otu ị nwere ike isi họrọ parampat kacha mma maka iwere ndabere.
Cheta na ihe niile ị na-eme bụ ihe egwu na ihe egwu nke gị. Lelee nkwado ndabere na mpaghara gị ma echefula maka DBCC CHECKDB.
isi: www.habr.com