Você já se deparou com uma situação em que precisa anexar um banco de dados com um ou mais arquivos .ndf ausentes? É um cenário comum que muitos administradores de banco de dados enfrentam. Digamos que você tenha criado um novo grupo de arquivos com arquivos para hospedar dados temporários para correção ou testes de dados. No entanto, você esqueceu de remover esses arquivos após concluir as tarefas. Meses depois, o banco de dados é desanexado e copiado para outro local, mas os discos rígidos originais foram reatribuídos e os backups estão armazenados em um local seguro longe da cidade. Agora, você percebe que um ou mais arquivos ndf pequenos, que não contêm nada importante, não foram copiados. Embora não seja um banco de dados de produção, ainda é crucial para outras equipes, como desenvolvimento e QA. Recuperar o backup levará mais de uma semana e você precisa de uma solução para montar rapidamente o banco de dados com os arquivos NDF ausentes.
Aqui está uma solução que permite montar rapidamente o banco de dados com arquivos NDF ausentes:
use master
if db_id('TestDB') is not null
drop database TestDB
go
create database TestDB on PRIMARY ( name = 'TestDB', filename = 'C:\Temp\TestDB.mdf'),
filegroup FG1 ( name = 'TestDB_File1', filename = 'C:\Temp\TestDB_File1.ndf')
log on ( name = 'TestDB_log', filename = 'C:\Temp\TestDB_log.ldf')
GO
create table TestDB.dbo.a (id int) on [PRIMARY]
insert into TestDB.dbo.a values(1)
create table TestDB.dbo.b (id int) on [FG1]
insert into TestDB.dbo.b values(2)
go
select * from TestDB.dbo.a
select * from TestDB.dbo.b
go
use master
go
exec sp_detach_db 'TestDB'
--- delete TestDB_File1.ndf
--- Copy TestDB.mdf and TestDB_log.ldf to elsewhere
use master
if db_id('TestDB') is not null
drop database TestDB
go
create database TestDB on PRIMARY ( name = 'TestDB', filename = 'C:\Temp\TestDB.mdf'),
filegroup FG1 ( name = 'TestDB_File1', filename = 'C:\Temp\TestDB_File1.ndf')
log on ( name = 'TestDB_log', filename = 'C:\Temp\TestDB_log.ldf')
go
alter database TestDB modify file(name = 'TestDB_File1', offline)
go
alter database TestDB set offline
-- remove all database files
-- copy TestDB.mdf and TestDB_log.ldf back to C:\Temp
go
alter database TestDB set online
/*
you will receive message below. but it's fine
The Service Broker in database "TestDB" will be disabled because the Service Broker GUID in the database (7DE06CC2-F709-4353-BC17-30A8D141EEFE) does not match the one in sys.databases (9E1BD254-BE20-483A-9E95-ACA98E9009A2).
*/select * from TestDB.dbo.a
/*
id
-----------
1
(1 row(s) affected)
*/select * from TestDB.dbo.b
/*
Msg 8653, Level 16, State 1, Line 1
The query processor is unable to produce a plan for the table or view 'b' because the table resides in a filegroup which is not online.
*/A ideia por trás dessa solução é:
- Criar um banco de dados vazio com a mesma estrutura de arquivos, incluindo nomes, grupos de arquivos e locais, mas sem nenhum dado.
- Colocar os arquivos ausentes offline.
- Colocar o banco de dados offline.
- Usar os arquivos de dados para substituir os arquivos de dados vazios.
- Colocar o banco de dados online.
Após seguir essas etapas, o banco de dados estará operacional. Executar o DBCC CHECKDB não retornará erros. No entanto, as tabelas que residem nos arquivos ausentes não serão acessíveis ou removíveis, mas você pode renomeá-las, se necessário. É importante observar que essa solução é normalmente usada em ambientes não produtivos, onde a remoção dessas tabelas inutilizáveis não é extremamente crítica. Se você deseja deixar o banco de dados limpo, o SQL Server permite modificar os metadados do sistema, o que explicarei em futuros posts.
Obrigado por ler! Se você tiver alguma dúvida ou sugestão, sinta-se à vontade para deixar um comentário abaixo.
John Huang – SQL MCM & MVP
Website: http://www.sqlnotes.info