Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Бэкап работающей базы  [new]
mikki_messer
Member

Откуда:
Сообщений: 3
Добрый день, Коллеги.
Возникла необходимость снимать резервную копию базы прямо во время работы. При этом, теоретически, есть вероятность, что другие пользователи будут в это время работать с базой.
Вопрос: в бэкап попадут только данные на начало бэкапа? Не отвалится ли процедура бэкапа, если с другого компьютера базу будут ковырять?
13 дек 11, 13:33    [11756036]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds_
Guest
mikki_messer,

Никто ниоткуда не отвалиться. Единственное будет просадка по системе ввода-вывода, но если хорошие диски и/или рейд, то можете быть спокойным.
В бекап попадут только те транзакции которые были зафиксированы на момент начала бекапа. Если у вас модель full, то остальное будет в фурнале транзакций.
13 дек 11, 13:38    [11756074]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
mikki_messer
Member

Откуда:
Сообщений: 3
gds_, Т.е. если мне нужны именно те данные, которые были в базе на начало бэкапа, я спокойно могу использовать вышеописанную схему?
13 дек 11, 13:48    [11756169]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
mikki_messer
gds_, Т.е. если мне нужны именно те данные, которые были в базе на начало бэкапа, я спокойно могу использовать вышеописанную схему?

Да спокойно можете бекапить БД.
13 дек 11, 13:52    [11756218]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
full backup
Guest
gds_
В бекап попадут только те транзакции которые были зафиксированы на момент начала бекапа.



http://msdn.microsoft.com/en-us/library/ms186289.aspx

автор
A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished.
13 дек 11, 14:27    [11756611]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
full backup,

А если проверить?

use [master]
GO

create database db_test
GO

use [db_test]
GO

create table dbo.t001(
id int identity not null primary key,
dt datetime not null
)
GO

declare @i int = 0
while @i < 1000 begin
insert into dbo.t001(dt) values (getdate())
set @i +=1
end;
GO

select * from dbo.t001
GO

begin transaction
delete from dbo.t001



во второй сессии делаем (после завершения удаления, но не закрываем транзакцию)
use [master]
GO

backup database [db_test]
to disk = N'c:\MSSQL\BACKUP\db_test.bak'
with init
GO


Получается что данные удалились, но транзакция не завершена. После чего делаем либо коммит либо роллбек.
тут же делаем
use [master]
GO
alter database [db_test] set single_user with rollback immediate;
GO
restore database [db_test]
from disk = N'c:\MSSQL\BACKUP\db_test.bak'
with recovery
GO


По вашим утверждениям, данные из таблицы должны быть удалены, т.к. удаление произошло до завершения снятия бекапа.
По моим утверждениям, данные в таблице должны остаться т.к. до завершения бекапа транзакция была активная и изменения не попали в файл данных, но они присутствуют в журнале транзакций.

После ресторинга подключаемся к бд и делаем селект - данные присутствуют.
13 дек 11, 15:03    [11757003]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
Glory
Member

Откуда:
Сообщений: 104751
gds
По вашим утверждениям, данные из таблицы должны быть удалены, т.к. удаление произошло до завершения снятия бекапа.
По моим утверждениям, данные в таблице должны остаться т.к. до завершения бекапа транзакция была активная и изменения не попали в файл данных, но они присутствуют в журнале транзакций.

Читайте всю цитату
Ваша транзакция не была завершена на момент окончания бэкапа
13 дек 11, 15:11    [11757106]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
gds, читаем цитату внимательно. А если уж решили проверить, то проверять тоже надо правильно.
В первой сессии
use [master]
GO

backup database [db_test]
to disk = N'c:\MSSQL\BACKUP\db_test.bak'
with init
GO

Потом во второй
begin transaction
delete from dbo.t001
commit

Затем
use [master]
GO
alter database [db_test] set single_user with rollback immediate;
GO
restore database [db_test]
from disk = N'c:\MSSQL\BACKUP\db_test.bak'
with recovery
GO

И наконец
use db_test
select count(*) from dbo.t001
13 дек 11, 15:12    [11757118]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
mikki_messer
Member

Откуда:
Сообщений: 3
invm,

Т.е. получается, что все изменения, которые пользователи закоммитят в процессе создания бэкапа также попадут в резервную копию? Если да, это даже лучше, чем я мог надеяться
13 дек 11, 15:26    [11757270]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
full backup
Guest
mikki_messer
invm,

Т.е. получается, что все изменения, которые пользователи закоммитят в процессе создания бэкапа также попадут в резервную копию?

Именно так.



я вам даже скажу больше, туда попадут даже не подтвержденные транзакции.
13 дек 11, 16:06    [11757689]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Уважаемые Glory и invm

Не хотелось бы с вами спорить, зная вашу квалификацию, но.
Вы утверждаете, что если транзакция была закончена в процессе бекапа то данные попадут в файл банных и в базу? я правильно понимаю.
т.е.
в коннекте 1 открываем транзакцию, удаляем данные, но не коммитим.
в коннекте 2 делаем бекап.
пока делается бекап делаем коммит в коннекте 1 и он проходит до завершения бекапа.
Получается данных при восстановлении не будет в таблице?

У меня такая хитрость не получилась. вот ссылка на видео. Где видно что удаление происходит до бекапа, но коммит происходит во время бекапа и до его окончания. И все равно данные остались. Может я не так что то делаю?
13 дек 11, 16:10    [11757730]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
gds
вот ссылка на видео.

Ссылка у меня не открылась, наверное файл битый. Вот рабочая ссылка
13 дек 11, 16:21    [11757878]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
Glory
Member

Откуда:
Сообщений: 104751
gds
Вы утверждаете, что если транзакция была закончена в процессе бекапа то данные попадут в файл банных и в базу? я правильно понимаю.
т.е.
в коннекте 1 открываем транзакцию, удаляем данные, но не коммитим.
в коннекте 2 делаем бекап.
пока делается бекап делаем коммит в коннекте 1 и он проходит до завершения бекапа.
Получается данных при восстановлении не будет в таблице?


Вы ссылку от "full backup " открывали ?

You can re-create a whole database in one step by restoring the database from a full database backup to any location. Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. When the database is recovered, uncommitted transactions are rolled back. The restored database matches the state of the original database when the restored backup finished, minus any uncommitted transactions.
13 дек 11, 16:28    [11757951]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Glory

Вы ссылку от "full backup " открывали ?

You can re-create a whole database in one step by restoring the database from a full database backup to any location. Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. When the database is recovered, uncommitted transactions are rolled back. The restored database matches the state of the original database when the restored backup finished, minus any uncommitted transactions.


Открывал и даже читал. Возможно мы о разном говорим. Я лишь хотел показать , что в базу (файл данных) не попали закоммиченные транзакции, которые были сделаны во время выполнения бекапа и до его завершения. Они попали в лог транзакций. А то что в файле бекапа содержится вся информация на момент окончания бекапа, я знаю. Просто часть её содержится в файле данных, а часть файле журнале транзакций. И что при with recovery незавершенные транзакции откатываются, а при with norecovery, они остаются, но база остается в режиме recovery. И то что восстановить на определенный момент времени (так можно только при ресторинге журнала транзакций restore log) можно я тоже все это знаю.
Да признаю первый пример был некорректным, но ...
в коннекте 1 открываем транзакцию, удаляем данные, но не коммитим.
в коннекте 2 делаем бекап, пока делается бекап делаем коммит в коннекте 1 и он проходит до завершения бекапа.
Данные остались (хотя судя по доке должны были удалиться т.к. коммит произошел до окончания бекапа). Но транзакция об удалении находится в файле журнала транзакций, которые как я понимаю откатывается при восстановлении.

Ладно, думаю бессмысленно дальше о чем либо спорить. Если в жизни все так как в msdb готов признать свою неправоту.
13 дек 11, 17:05    [11758355]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
Glory
Member

Откуда:
Сообщений: 104751
gds
Открывал и даже читал. Возможно мы о разном говорим. Я лишь хотел показать , что в базу (файл данных) не попали закоммиченные транзакции, которые были сделаны во время выполнения бекапа и до его завершения.

Транзакции, как известно, находятся в журнале транзакций, а не в базе
И "Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished"
И эта порция лога применяется на восстанавливаемой базе
13 дек 11, 17:08    [11758386]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
gds, вы что-то не так делаете. Потому что, если
gds
в коннекте 1 открываем транзакцию, удаляем данные, но не коммитим.
в коннекте 2 делаем бекап, пока делается бекап делаем коммит в коннекте 1 и он проходит до завершения бекапа.
Данные остались (хотя судя по доке должны были удалиться т.к. коммит произошел до окончания бекапа). Но транзакция об удалении находится в файле журнала транзакций, которые как я понимаю откатывается при восстановлении.

то БД будет в неконсистентном состоянии после восстановления из резервной копии.
13 дек 11, 17:25    [11758573]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Glory,

ок, в данном 11756074 контексте я лажанулся (или не правильно высказал свои мысли), ну с кем не бывает. Ведь кто не делает ошибок? на них учатся.
Меня интересует другое, вы цитируете msdn
Glory
И "Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished"
И эта порция лога применяется на восстанавливаемой базе

Подскажите (если я вас не сильно утомил) как мне восстановить БД на момент окончания бекапа, так что бы все закоммиченные транзакции по окончании бекапа попали в файл данных? Ведь "full backup" это имел ввиду?
на видео я записал следующий сценарий
в коннекте 1 открываем транзакцию, удаляем данные, но не коммитим.
в коннекте 2 делаем бекап, пока делается бекап делаем коммит в коннекте 1 и он проходит до завершения бекапа.
После ресторинга - данные остались. И при ресторинге видно, что транзакции проходят откат. И я понимаю что моя транзакция, которая была выполнена в момент бекапа попала в журнал транзакций.
Я так понимаю "full backup" намекал на то, что данных там быть не должно (т.к. транзакция была закоммичена до того как бекап сделался)?
Нет ну конечно если восстановить full backup, а потом накатить порцию транзакт логов сделанных после коммита, то да естественно их там и не будет.
13 дек 11, 17:38    [11758690]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
full backup
Guest
gds
Glory,

ок, в данном 11756074 контексте я лажанулся (или не правильно высказал свои мысли), ну с кем не бывает. Ведь кто не делает ошибок? на них учатся.
Меня интересует другое, вы цитируете msdn
Glory
И "Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished"
И эта порция лога применяется на восстанавливаемой базе

Подскажите (если я вас не сильно утомил) как мне восстановить БД на момент окончания бекапа, так что бы все закоммиченные транзакции по окончании бекапа попали в файл данных? Ведь "full backup" это имел ввиду?
на видео я записал следующий сценарий
в коннекте 1 открываем транзакцию, удаляем данные, но не коммитим.
в коннекте 2 делаем бекап, пока делается бекап делаем коммит в коннекте 1 и он проходит до завершения бекапа.
После ресторинга - данные остались. И при ресторинге видно, что транзакции проходят откат. И я понимаю что моя транзакция, которая была выполнена в момент бекапа попала в журнал транзакций.
Я так понимаю "full backup" намекал на то, что данных там быть не должно (т.к. транзакция была закоммичена до того как бекап сделался)?
Нет ну конечно если восстановить full backup, а потом накатить порцию транзакт логов сделанных после коммита, то да естественно их там и не будет.

если в момент рестора полного бекапа эта транзакция откатилась, значит вы закоммители ее после завершения бекапа.
13 дек 11, 17:40    [11758710]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
invm
gds, вы что-то не так делаете. Потому что, если
gds
в коннекте 1 открываем транзакцию, удаляем данные, но не коммитим.
в коннекте 2 делаем бекап, пока делается бекап делаем коммит в коннекте 1 и он проходит до завершения бекапа.
Данные остались (хотя судя по доке должны были удалиться т.к. коммит произошел до окончания бекапа). Но транзакция об удалении находится в файле журнала транзакций, которые как я понимаю откатывается при восстановлении.

то БД будет в неконсистентном состоянии после восстановления из резервной копии.


Уважаемый invm, вы видео смотрели? я ведь не просто так выложил его 11757878. Я вот тоже хочу понять если "full backup" утверждает, что если коммит произошел до окончания бекапа, то данные буду зафиксированы (попадут в бд) в моем случае будут удалены.
вот скрипты которые я делал (на видео видно)
-- 1 подготовка данных
use [master]
GO

create database db_test
GO

use [db_test]
GO

create table dbo.t001(
id int identity not null primary key,
dt datetime not null
)
GO

declare @i int = 0
while @i < 500000 begin -- тут побольше, что бы бекап делался подольше
insert into dbo.t001(dt) values (getdate())
set @i +=1
end;
GO

-- 2 проверка
-- 7 проверка после восстановления
use [db_test]
GO
select count(*) from dbo.t001
GO

-- 3 удаление дожидаемся когда удалит, но не закрываем транзакцию
begin transaction
delete from dbo.t001
-- 5 закрываем транзакцию во время выполнения процедуры бекапа
commit
GO


другая сессия
-- 4 запускаем бекап и сразу же переходим на 5-й шаг и выполняем
use [master]
GO
backup database [db_test]
to disk = N'c:\MSSQL\BACKUP\db_test.bak'
with init
GO

-- 6 после бекапа восстанавливает БД
use [master]
GO
alter database [db_test] set single_user with rollback immediate;
GO
restore database [db_test]
from disk = N'c:\MSSQL\BACKUP\db_test.bak'
with recovery
GO
13 дек 11, 17:49    [11758805]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
full backup
если в момент рестора полного бекапа эта транзакция откатилась, значит вы закоммители ее после завершения бекапа.

Уважаемый full backup вы видео смотрели? Ну хотите я новое сделаю где будет отчетливо видно что коммит произошел после начала бекапа и до его завершения.
13 дек 11, 17:51    [11758825]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
gds
full backup
если в момент рестора полного бекапа эта транзакция откатилась, значит вы закоммители ее после завершения бекапа.

Уважаемый full backup вы видео смотрели? Ну хотите я новое сделаю где будет отчетливо видно что коммит произошел после начала бекапа и до его завершения.


Смотрите покадрово с 56 секунды я переключался быстро что бы успеть до завершения бекапа.
13 дек 11, 17:53    [11758844]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
Glory
Member

Откуда:
Сообщений: 104751
gds
я переключался быстро что бы успеть до завершения бекапа.

Это конечно аргумент, но может лучше посмотреть, что получилось в журнале транзакций ?
Вот например ваш бэкап какую информацию в конце выдал о заархивированных страницах журнала ?
13 дек 11, 17:58    [11758881]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Glory
Вот например ваш бэкап какую информацию в конце выдал о заархивированных страницах журнала?

Он обработал (заархивировал) 8000 страниц. На момент начала бекапа страницы были считаны в буферный кеш и изменены - стали грязными. Перед бекапом сервак вызвал checkpoint, но страницы не были скинуты т.к. были заняты транзакцией и не были завершенные. В момент бекапа я делаю коммит и во втором тесте явный чек поинт. И все это проходит до окончания бекапа. После смотрю страницы были скинуты на диск и был очищен журнал транзакций, (опять же до окончания бекапа). Кстати тоже самое происходит и без явного checkpoint (т.е. если явно не вызывать). После восстановления мои данные остались. т.е. не удалены.

На моей тестовой БД режим восстановления simple т.к. топикастер не указал режим восстановления своей БД и насколько я понял не указал режим бекапа, но подразумивал full backup.
mikki_messer
Добрый день, Коллеги.
Возникла необходимость снимать резервную копию базы прямо во время работы. При этом, теоретически, есть вероятность, что другие пользователи будут в это время работать с базой.
Вопрос: в бэкап попадут только данные на начало бэкапа? Не отвалится ли процедура бэкапа, если с другого компьютера базу будут ковырять?

Сегодня проведу опыт с режимом восстановления фулл - возможно другое поведение.
14 дек 11, 11:50    [11762678]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
Glory
Member

Откуда:
Сообщений: 104751
gds
Он обработал (заархивировал) 8000 страниц.

Вы про файл данных или про файл лога ?
14 дек 11, 11:55    [11762720]     Ответить | Цитировать Сообщить модератору
 Re: Бэкап работающей базы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Glory
gds
Он обработал (заархивировал) 8000 страниц.

Вы про файл данных или про файл лога ?

про файл лога.
Я так понимаю 216 страниц обработано для файла данных и 8000 для файла лога.
Обработано 216 страниц для базы данных "db_test", файл "db_test'
Обработано 8000 страниц для базы данных "db_test", файл "db_test_log'
14 дек 11, 12:01    [11762777]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить