Come spostare un database sql server altrove da codice con una stored procedure

Questa stored procedure di James DiMauro, sposta il file di dati e di log di una lista di database da codice. Presuppone che le directoried passate come parametri esistano e che l'account di sql che esegue la stored abbia i premessi per scrivere
nelle cartelle.
Si utilizza scrivendo in una qualsiasi finestra di query analyzer la seguente riga di codice:

exec p_MoveDatabase 'c:\sqlmovetest\','c:\sqlmovetest\','db1, db2, db3'

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;

--insert into @DbTable (dbname) values ('testdb1')

--insert into @DbTable (dbname) values ('testdb2')

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

-- kill all user connections by setting to single user with immediate

set @sql= 'ALTER DATABASE [' + @DbName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

print ''

print 'Executing line -' + @sql

exec sp_executesql @sql

-- set db off line

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

--move physical 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 'Executing line -' + @sql

exec sp_executesql @sql

--alter file paths

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

-- allow multi user again.

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