DEVE-SE FAZER BACKUP DO BANCO DE DADOS PARA RODAR OS COMANDOS ABAIXO !
Listar erros
USE etrade
GO
ALTER DATABASE etrade SET SINGLE_USER;
/* Coloca o banco em modo single user para executar os comandos abaixo */
DBCC CHECKDB ('etrade') WITH TABLOCK, ALL_ERRORMSGS, NO_INFOMSGS;
ALTER DATABASE etrade SET MULTI_USER;
/* Volta o banco em modo multi user */
Depois de listar, execute este comando (informe o código do erro no lugar do 9999999)
USE etrade
GO
ALTER DATABASE etrade SET SINGLE_USER;
DBCC CHECKTABLE(9999999,REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE etrade SET MULTI_USER;
Recupera o banco com status "SINGLE USER" (Usuário Único)
ALTER DATABASE etrade SET MULTI_USER;
Recupera o banco com status "SUSPECT" (Suspeito)
Caso o banco esteja em "Suspect", execute os comandos abaixo.
Mais informações em Repair A Suspect Database
EXEC sp_resetstatus etrade
ALTER DATABASE etrade SET EMERGENCY
DBCC checkdb(etrade)
ALTER DATABASE etrade SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (etrade, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE etrade SET MULTI_USER
Recupera o banco com status "EMERGENCY" (Emergencia)
ALTER DATABASE etrade SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (etrade, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE etrade SET MULTI_USER
Recupera o banco com status "RESTAURAR" (Recuperação pendente / Recovery pending)
ALTER DATABASE Etrade SET EMERGENCY;
GO
ALTER DATABASE Etrade set single_user
GO
DBCC CHECKDB (Etrade, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE Etrade set multi_user
GO
Recupera o banco com status "SOMENTE LEITURA" Read-Only
- Basta ir nas PROPRIEDADES do banco, em OPÇÕES e mudar o BANCO DE DADOS SOMENTE LEITURA para
False
- Ou rodar o comando abaixo
USE etrade
GO
ALTER DATABASE etrade SET READ_WRITE WITH NO_WAIT
GO
Recupera o banco com status "Usuário Restrito" Restricted_User
- Basta ir nas PROPRIEDADES do banco, em OPÇÕES e mudar o ACESSO RESTRITO para
MULTI_USER
- Ou rodar o comando abaixo
ALTER DATABASE etrade SET MULTI_USER;
Tirar banco do modo Offline
- Com o botão direito sobre o banco, selecione TAREFAS e clique em
colocar online
Recuperar o banco todo
Não é muito aconselhável rodar isso sem antes dar um checkdb pra ver os erros, pois é importante que o banco conheça os erros antes.
Veja mais aqui: SQL SERVER CENTRAL
DBCC CHECKDB(etrade, REPAIR_ALLOW_DATA_LOSS);
Query para ver quais os backups estão salvos no arquivo .bak
- Nessa query ele mostra os snapshots de backups no log que fica dentro do .bak
SELECT
database_name, name, backup_start_date, backup_finish_date, datediff(mi, backup_start_date, backup_finish_date) [tempo (min)],
position, first_lsn, last_lsn, server_name, recovery_model,
type, cast(backup_size/1024/1024 as numeric(15,2)) [Tamanho (MB)], B.is_copy_only
FROM msdb.dbo.backupset B
- Query para restaurar o .bak pelo management
SET NOCOUNT ON
DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0
DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor tinyint SET @fill_factor = 80
DECLARE @report_only bit SET @report_only = 0
DECLARE @page_count_thresh smallint SET @page_count_thresh = 1000
DECLARE @objectid int
DECLARE @indexid int
DECLARE @partitioncount bigint
DECLARE @schemaname nvarchar(130)
DECLARE @objectname nvarchar(130)
DECLARE @indexname nvarchar(130)
DECLARE @partitionnum bigint
DECLARE @partitions bigint
DECLARE @frag float
DECLARE @page_count int
DECLARE @command nvarchar(4000)
DECLARE @intentions nvarchar(4000)
DECLARE @table_var TABLE(
objectid int,
indexid int,
partitionnum int,
frag float,
page_count int
)
INSERT INTO
@table_var
SELECT
[object_id] AS objectid,
[index_id] AS indexid,
[partition_number] AS partitionnum,
[avg_fragmentation_in_percent] AS frag,
[page_count] AS page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
[avg_fragmentation_in_percent] > @reorg_frag_thresh
AND
page_count > @page_count_thresh
AND
index_id > 0
DECLARE partitions CURSOR FOR
SELECT * FROM @table_var
OPEN partitions
WHILE (1=1) BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @page_count
IF @@FETCH_STATUS < 0 BREAK
SELECT
@objectname = QUOTENAME(o.[name]),
@schemaname = QUOTENAME(s.[name])
FROM
sys.objects AS o WITH (NOLOCK)
JOIN sys.schemas as s WITH (NOLOCK)
ON s.[schema_id] = o.[schema_id]
WHERE
o.[object_id] = @objectid
SELECT
@indexname = QUOTENAME([name])
FROM
sys.indexes WITH (NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
SELECT
@partitioncount = count (*)
FROM
sys.partitions WITH (NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
SET @intentions =
@schemaname + N'.' +
@objectname + N'.' +
@indexname + N':' + CHAR(13) + CHAR(10)
SET @intentions =
REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
@intentions
SET @intentions = @intentions +
N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
N' PAGE COUNT: ' + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
IF @frag < @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER INDEX ' + @indexname +
N' ON ' + @schemaname + N'.' + @objectname +
N' REORGANIZE; ' +
N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname +
N' ' + @indexname + ';'
END
IF @frag >= @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER INDEX ' + @indexname +
N' ON ' + @schemaname + N'.' + @objectname +
N' REBUILD'
END
IF @partitioncount > 1 BEGIN
SET @intentions = @intentions +
N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
SET @command = @command +
N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
END
IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
SET @intentions = @intentions +
N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
SET @command = @command +
N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
END
IF @report_only = 0 BEGIN
SET @intentions = @intentions + N' EXECUTING: ' + @command
PRINT @intentions
EXEC (@command)
END ELSE BEGIN
PRINT @intentions
END
END;
CLOSE partitions;
DEALLOCATE partitions;
