One of my tasks recently involved moving the physical files from a set of MSSQL databases from one partition to another. These databases were part of a Sharepoint Farm.
There were a number of databases so I created the following script in order to detach, move and re-attach the databases.
This script was used to move databases on the same box. But I believe with the use of a Linked Server, a mapped drive and a nice OPENQUERY on the linked server you could adapt this script to move this to another server as well. I don't have that itch at the moment, but if it does come up I will make another post with the updated script (otherwise if you make the change please share!)
001.
DECLARE
@
name
VARCHAR
(50)
-- database name
002.
@nameDB
003.
@nameLog
-- database log name
004.
@path
(256)
-- path for backup files
005.
@fileNameDB
-- filename for backup
006.
@fileNameLog
007.
@destinationPath
-- new path for db
008.
@dbid
bigint
-- db id
009.
@enableCMDPermanently
bit
010.
-- ============USER CONFIGURABLE VARIABLES START=============
011.
SET
@path =
'D:\Databases\' -- Destination Path for all Databases
012.
SET @enableCMDPermanently = 1
013.
-- ============USER CONFIGURABLE VARIABLES FINISH============
014.
015.
-- =====Please do not edit variables below this line=========
016.
017.
-- Enabled CMD from T-SQL Script
018.
EXEC master.dbo.sp_configure '
show advanced options
', 1
019.
RECONFIGURE
020.
xp_cmdshell
', 1 -- enable CMD
021.
022.
023.
-- Table variable for name/id key/values
024.
025.
DECLARE @sysdb TABLE
026.
(
027.
dbname nvarchar(max),
028.
database_id bigint
029.
)
030.
031.
INSERT INTO @sysdb (dbname,database_id) SELECT [name],[dbid] FROM master.dbo.sysdatabases WHERE name NOT IN ('
master
','
model
msdb
tempdb
')
032.
033.
-- Table variable for db file details
034.
DECLARE @sysfiles TABLE
035.
036.
037.
physical_name nvarchar(max),
038.
database_id bigint,
039.
type_desc nvarchar(max)
040.
041.
042.
INSERT INTO @sysfiles (dbname,physical_name,database_id,type_desc) SELECT [name], [physical_name],[database_id],type_desc FROM sys.master_files
043.
044.
-- Start CURSOR to iterate through database ids
045.
DECLARE db_cursor CURSOR FOR SELECT [database_id] FROM @sysdb
046.
OPEN db_cursor
047.
FETCH NEXT FROM db_cursor INTO @dbid
048.
049.
-- BEGIN LOOP
050.
051.
WHILE @@FETCH_STATUS = 0
052.
BEGIN
053.
--Read DB Details & File Locations
054.
PRINT @dbid
055.
SET @name = (SELECT [dbname] FROM @sysdb WHERE database_id = @dbid)
056.
PRINT @name
057.
SET @nameDB = (SELECT [dbname] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = '
ROWS
058.
SET @fileNameDB = (SELECT [physical_name] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = '
059.
PRINT @fileNameDB
060.
SET @nameLog = (SELECT [dbname] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = '
LOG
061.
SET @fileNameLog = (SELECT [physical_name] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = '
062.
063.
DECLARE @cmd nvarchar(500) -- temp string for dynamic sql queries
064.
065.
-- Force Disconnect Active Connections to Database
066.
SET @cmd = '
ALTER
DATABASE
' + quotename(@name) + '
SINGLE_USER
WITH
ROLLBACK
IMMEDIATE
'
067.
068.
PRINT @cmd
069.
070.
EXECUTE sp_executesql @cmd
071.
072.
OFFLINE
073.
074.
075.
076.
077.
078.
-- Point DB to new file location
079.
080.
'+ quotename(@name) +'
MODIFY
FILE (
NAME
=
'+quotename(@nameDB)+'
, FILENAME =
'+quotename(@path + @nameDB + '
.mdf
')+ '
081.
082.
083.
084.
'+quotename(@nameLog)+'
'+quotename(@path + @nameLog + '
.LDF
085.
086.
087.
088.
-- Detach DB
089.
090.
sp_detach_db
' + quotename(@name)
091.
092.
093.
094.
095.
096.
097.
-- Execute move command on shell to move physical file to new directory
098.
099.
PRINT @fileNameLog
100.
101.
PRINT '
cp
' + @fileNameDB + '
' + @path + @nameDB + '
102.
103.
PRINT @path + @nameDB + '
104.
105.
' + quotename('
move
' + '
"'+@fileNameDB+'"
"'+@path + @nameDB + '.mdf'+'"
106.
107.
108.
109.
110.
111.
112.
PRINT @path + @nameLog + '
113.
114.
' + @fileNameLog + '
' + @path + @nameLog + '
115.
116.
"'+@fileNameLog+'"
"'+@path + @nameLog + '.LDF'+'"
117.
118.
119.
120.
121.
-- Reattach Database to SQL Instance
122.
123.
sp_attach_db
,
' + quotename( @path + @nameDB + '
') + '
' + quotename(@path + @nameLog + '
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
END
134.
135.
136.
137.
--Close Cursor
138.
139.
CLOSE db_cursor
140.
DEALLOCATE db_cursor
141.
142.
143.
--OPTIONAL
144.
145.
IF @enableCMDPermanently = 0
146.
147.
148.
149.
150.
151.
152.
153.
xp_cmdshell', 0
--disable CMD
154.
155.
156.
157.
Richard Mueller edited Revision 5. Comment: Change tag "SQL 2008" to "SQL Server 2008"
Richard Mueller edited Revision 3. Comment: Replaced RGB values with color names in HTML to restore colors
Richard Mueller edited Revision 2. Comment: Removed (en-US) from title, added tags
Craig Lussier edited Revision 1. Comment: added en-US to tags and title
Ed Price MSFT edited Original. Comment: Updated title per style standards.
My updated/branched version: sbrickey.com/.../Move_database_files