Il primo percorso e' la nuova posizione del database, il secondo del file di log, il terzo parametro e' l'elenco dei database da spostare, dei quali viene fatto automaticamente il detach e l'attach.
Ecco il codice da lanciare preliminarmente per la generazione della stored:
IF ( OBJECT_ID('dbo.p_MoveDatabase') IS NOT NULL )
begin
DROP PROCEDURE dbo.p_MoveDatabase
end
GO
create procedure p_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
set @sql= 'ALTER DATABASE [' + @DbName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
print ''
print 'Executing line -' + @sql
exec sp_executesql @sql
set @sql = 'ALTER DATABASE [' + @DbName + '] SET OFFLINE;'
print ''
print 'Executing line - ' + @sql
exec sp_executesql @sql
select * from @FileTable
while @@rowcount > 0
begin
select top 1 @OldPath = physical_name, @Type = [type], @LogicalName = [name] from @FileTable
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 'Executing line -' + @sql
exec sp_executesql @sql
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
set @sql = 'ALTER DATABASE [' + @DbName + '] SET ONLINE;'
print ''
print 'Executing line -' + @sql
exec sp_executesql @sql
SET @RowNum = @RowNum + 1
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
Come sappiamo per migliorare le prestazioni di lettura di una tabella contenente una grossa mole di dati, applichiamo degli indici sui campi per i quali vengono effettuate maggiormente le queries.