FACULDADE DE ADMINISTRAÇÃO E NEGÓCIOS DE SERGIPE – FANESE
NÚCLEO DE PÓS-GRADUAÇÃO E EXTENSÃO – NPGE
PROCEDIMENTOS DE BACKUP E RESTAURAÇÃO - SQL SERVER
DAYSE SOARES SANTOS
ARACAJU
2011
FACULDADE DE ADMINISTRAÇÃO E NEGÓCIOS DE SERGIPE – FANESE
NÚCLEO DE PÓS-GRADUAÇÃO E EXTENSÃO – NPGE
PROCEDIMENTOS DE BACKUP E RESTAURAÇÃO - SQL SERVER
Trabalho de Conclusão de Módulo de Procedimentos de backup e restauração – SQL server, do curso de Especialização em Bancos de Dados – FANESE, submetido à banca examinadora para avaliação de conclusão do referido módulo.
Orientador: Prof.David Couto
DAYSE SOARES SANTOS
ARACAJU
2011
AMBIENTE
Nome da Empresa: Seda Confecção
Campo de negócio
A empresa Seda Confecção é voltada para confecção e venda de roupas
femininas e já possuem 7 unidades no estado de Sergipe. Com o seu crescimento,
foi necessário a contratação de um sistema ERP(Enterprise Resource Planning) o
SysERP.
A base de dados da empresa hoje possui 25GB e para evitar qualquer
eventualidade de perca dos dados, foi definido um plano de backup.
Para criar uma estratégia de backup é necessário obter algumas informações:
Horário de funcionamento: Segunda a Sexta das 08:00 as 18:00 hs e aos
Sábados das 08:00 às 16:00 hs.
Tolerância de perda de dados: 03:00 hs.
Tempo de limite de recuperação dos dados: 02:00hs
Trafego de dados por dia: 600MB a 800MB
DEFINIÇÃO DE ESTRATÉGIA PARA BACKUP
Backup FULL: Sábado a partir das 23:30hs .
Backup Diferencial: Diariamente (de segunda a sábado) a partir das 20:00hs.
Backup Log: a cada 3hs, sendo que o backup iniciará das 8:00hs às 19:00hs
ESTRATÉGIA PARA RESTAURAÇÃO
Antes de restaurar o backup:
Deve-se ter a mídia exata em mãos.
Saber com toda a certeza o caminho de destino para reversão.
Autorização para efetuar essa ação.
SCRIPT
INICIANDO A RESTAURAÇÃO
RESTORE DATABASE [bd_seda] FROM DISK =
N'C:\Backups\FULL\bd_seda_backup_2011_10_28_233438_4396928.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [bd_seda] FROM DISK =
N'C:\Backups\DIFERENCIAL\bd_seda_backup_2011_10_28_234847_5148365.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [bd_seda] FROM DISK =
N'C:\Backups\LOG\bd_seda_backup_2011_10_28_234937_3965629.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [bd_seda] FROM DISK =
N'C:\Backups\LOG\bd_seda_backup_2011_10_29_001943_6849065.trn' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
DBCC CHECKDB(bd_seda)
BACKUP AUTOMÁTICO (JOB)
FULL
USE [msdb] GO
/****** Object: Job [Backup FULL.Subplan_1] Script Date: 10/29/2011 00:24:06 ******/ BEGIN TRANSACTION
DECLARE @ReturnCode INT SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 10/29/2011 00:24:06 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Backup FULL.Subplan_1', @enabled=1, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0,
@description=N'No description available.', @category_name=N'Database Maintenance',
@owner_login_name=N'DAYSE-72E78CBF4\Dayse', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Subplan_1] Script Date: 10/29/2011 00:24:06 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Subplan_1', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'SSIS',
@command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\Backup FULL" /set "\Package\Subplan_1.Disable;false"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Backup FULL.Subplan_1', @enabled=1, @freq_type=8, @freq_interval=32, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20111028, @active_end_date=99991231, @active_start_time=233000, @active_end_time=235959, @schedule_uid=N'9eedb51f-c77f-4c54-91ee-0259b8554657' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
DIFERENCIAL
USE [msdb] GO
/****** Object: Job [Backup Diferencial.Subplan_1] Script Date: 10/29/2011 00:24:45 ******/ BEGIN TRANSACTION
DECLARE @ReturnCode INT SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 10/29/2011 00:24:45 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Backup Diferencial.Subplan_1', @enabled=1, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0,
@description=N'No description available.', @category_name=N'Database Maintenance',
@owner_login_name=N'DAYSE-72E78CBF4\Dayse', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Subplan_1] Script Date: 10/29/2011 00:24:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Subplan_1', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'SSIS',
@command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\Backup Diferencial" /set "\Package\Subplan_1.Disable;false"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Backup Diferencial.Subplan_1', @enabled=1, @freq_type=8, @freq_interval=126, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20111028, @active_end_date=99991231, @active_start_time=200000,
@active_end_time=235959,
@schedule_uid=N'8cac7d6c-055e-4163-8e1f-f84ddadd4624' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
GO
LOG
USE [msdb] GO
/****** Object: Job [Backup LOG.Subplan_1] Script Date: 10/29/2011 00:25:19 ******/ BEGIN TRANSACTION
DECLARE @ReturnCode INT SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 10/29/2011 00:25:19 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Backup LOG.Subplan_1', @enabled=1, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0,
@description=N'No description available.', @category_name=N'Database Maintenance',
@owner_login_name=N'DAYSE-72E78CBF4\Dayse', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Subplan_1] Script Date: 10/29/2011 00:25:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Subplan_1', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'SSIS',
@command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\Backup LOG" /set "\Package\Subplan_1.Disable;false"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Backup LOG.Subplan_1', @enabled=1, @freq_type=8, @freq_interval=126, @freq_subday_type=8, @freq_subday_interval=3, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20111028, @active_end_date=99991231, @active_start_time=80000, @active_end_time=190000, @schedule_uid=N'429d46fa-9fcf-479f-87c4-cbacabe90b93' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
