Monthly Archives: August 2009

Scripts, SQL

Movimentar arquivos de Bancos de Dados

Leave a comment

Nesse post vou falar como movimentar os arquivos de um banco de dados de uma unidade/diretório para outra unidade/diretório.

Ai você pensa:

“Pra que? Já sei como faz isso. Não tem nenhuma novidade.” ou

“Aaahh,,, isso é facil, uso o bom e velho backup/restore ou o detach/attach…”

Isso é verdade, não tem nenhuma grande novidade em fazer a movimentação mas, ai vem a parte divertida, pra que fazer do método fácil se podemos fazer do método interessante?

1.- Backup/Restore

Se executarmos o bom e velho backup/restore, no momento do restore podemos alterar o caminho onde os arquivos irão ser gravados, isso é simples.

Apenas precisaremos executar o processo de backup que, dependendo do tamanho da base, pode demorar alguns minutos ou até umas horas. Além de ter que ter um espaço para armazenar o backup ou executá-lo através de uma unidade de backup.

2.- Detach/Attach

Usando o Detach ganhamos algum tempo, você vai apenas mover os arquivos de log e dados para outro lugar e anexar a base novamente.

O problema disso é se você usar o Service Broker ou até o Mirror, você terá que refazer o processo.

3.- Alter Database (o “método interessante”)

Esse “método” a seguir cria uma procedure para executar a movimentação do banco para outro local usando o Alter database, mas é possível executá-lo em diversos bancos como vou demonstrar:

Vamos criar 2 bases de dados para testar:

create database banco1

create database banco2

Agora execute o scritp abaixo para criar a procedure que irá executar a movimentação dos arquivos:

IF ( OBJECT_ID(‘dbo.sp_MoveDatabase’) IS NOT NULL )
begin
DROP PROCEDURE dbo.sp_MoveDatabase
end
GO

create procedure sp_MoveDatabase

@NewDataFolder nvarchar(1000),
@NewLogFolder nvarchar(1000),
@DbList nvarchar(4000)

as
Begin
declare @DbTable table (lkey int identity (1,1) primary key, dbname nvarchar(100))
declare @FileTable table (lkey int identity (1,1) primary key, [name]nvarchar(100), physical_name nvarchar(1000), [type] int )
declare @sql nvarchar(4000)
declare @count int, @RowNum int
declare @DbName nvarchar(100)
declare @OldPath nvarchar(1000)
declare @Type int
declare @LogicalName nvarchar(100)
declare @ParmDefinition nvarchar(1000)
declare @FileName nvarchar(100)
declare @NewPath nvarchar(1000)
declare @ShowAdvOpt int
declare @XPCMD int

set nocount on;

if right(@DbList,1) = ‘,’

Begin
print ‘DbList must NOT end with “””‘
return

End
declare @MyString NVARCHAR(100)
declare @Pos INT
declare @NextPos INT
declare @String NVARCHAR(4000)
declare @Delimiter NVARCHAR(1)

set @String = @DbList
set @Delimiter = ‘,’
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)
WHILE (@pos <> 0)

BEGIN
SET @MyString = substring(@String,1,@Pos – 1)
insert into @DbTable (dbname) values (LTRIM(RTRIM(@MyString)))
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)

END
set @ShowAdvOpt = cast(( select [value] from sys.configurations where [name] = ‘show advanced options’) as int)
set @XPCMD = cast(( select [value] from sys.configurations where [name] = ‘xp_cmdshell’) as int)
if right(@NewDataFolder,1)<> ‘\’ or right(@NewLogFolder,1)<>’\’

Begin
print ‘new path”s must end with \’
return
end
EXEC sp_configure ‘show advanced option’, ‘1’
RECONFIGURE

exec sp_configure ‘xp_cmdshell’ , ‘1’
RECONFIGURE

print ‘NewMdfFolder is ‘ + @NewDataFolder
print ‘NewLdfFolder is ‘ + @NewLogFolder

SET @RowNum = 1
SET @count = (select count(*) from @DbTable)
while @RowNum <= @count

Begin
select @DbName = DBName from @DbTable
where lKey = @RowNum
set @sql = ‘select name, physical_name, type from ‘ + @DbName + ‘.sys.database_files’
insert into @FileTable
exec sp_executesql @sql

— Derruba todas as conexoes configurando como single user with immediate
set @sql= ‘ALTER DATABASE [‘ + @DbName + ‘] SET SINGLE_USER WITH ROLLBACK IMMEDIATE’
print ”
print ‘Executando linha -‘ + @sql
exec sp_executesql @sql

— configura db off line
set @sql = ‘ALTER DATABASE [‘ + @DbName + ‘] SET OFFLINE;’
print ”
print ‘Executando linha – ‘ + @sql
exec sp_executesql @sql
select * from @FileTable
while @@rowcount > 0

begin
select top 1 @OldPath = physical_name, @Type = [type], @LogicalName = [name] from @FileTable

–move arquivos files
set @FileName = (SELECT REVERSE(SUBSTRING(REVERSE(@OldPath), 0, CHARINDEX(‘\’, REVERSE(@OldPath), 1))))
if @type = 0

begin
set @NewPath = @NewDataFolder + @FileName
end
else
begin

set @NewPath = @NewLogFolder + @FileName
end
set @Sql = ‘EXEC master..xp_cmdshell ”MOVE “‘ + @OldPath + ‘” “‘ + @NewPath +'””’
print ”
print ‘Executando linha -‘ + @sql
exec sp_executesql @sql

— altera caminho dos arquivos
set @sql = ‘ALTER DATABASE ‘ + @DbName + ‘ MODIFY FILE (NAME = ‘ + @LogicalName + ‘, FILENAME = “‘ + @NewPath + ‘”)’
exec sp_executesql @sql
delete from @FileTable where [name] = @LogicalName
select * from @FileTable
end –while

set @sql = ‘ALTER DATABASE [‘ + @DbName + ‘] SET ONLINE;’
print ”
print ‘Executing line -‘ + @sql
exec sp_executesql @sql
SET @RowNum = @RowNum + 1

— aceita multi user novamente.
set @sql= ‘ALTER DATABASE [‘ + @DbName + ‘] SET MULTI_USER’
print ”
print ‘Executing line -‘ + @sql
exec sp_executesql @sql
end
exec sp_configure ‘xp_cmdshell’ , @XPCMD

reconfigure
EXEC sp_configure ‘show advanced option’, @ShowAdvOpt

RECONFIGURE
End –procedure

** ATENÇÃO **

Os diretórios de destino já devem estar criados, caso você execute a procedure sem criar o destino o script vai alterar o caminho da base e não vai movimentar os arquivos e quando ela foi trazida online ela vai apresentar erro.

** ATENÇÃO 2 **

Esse script é do tipo “Vai filhão”, quando ele for executado todos os usuários conectados serão desconectados sem dó nem piedade, todas as transações que eles estiverem executando serão dropadas. As conexões apenas poderão ser reestabelecidas após o termino da movimentação.

Para executar a procedure execute-a da seguinte forma:

exec sp_MoveDatabase @NewDataFolder = ‘c:\teste\’, @NewLogFolder = ‘d:\teste\’, @sDbList = ‘banco1, banco2’

Se tudo der certo, as bases banco1 e banco2 estarão seus novos destinos.