You can move SQL Server databases a few ways: backup the database and restore it in a new location; detach it and reattach it in the new location or use the ALTER command. This example uses the latter.
First create 3 databases named db1, db2, db3 using SQL Server Management Studio
Create database db1
Create database db2
Create database db3
Next, create a stored procedure named p_MoveDatabase compliments of James DiMauro (http://www.sqlservercentral.com/articles/ALTER+DATABASE+MODIFY+FILE/67527/)
*/
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
To call this script, you must pass the names of the new database and log file folders as well as the names of the database(s) that you want to move.
exec p_MoveDatabase @NewDataFolder = 'c:\sqldata\', @NewLogFolder= 'c:\sqllog\',@DbList = 'db1, db2, db3'