Sometimes there’s a need to move the underlying files for
the databases on a SQL instance.
Scenario
You have all your data files on the “E drive” which is really
a RAID. It has 500 Gbytes available which
is plenty. Months later, the E drive
becomes full. You request more
disk. Some time later your storage
engineer gives you a new dedicated RAID
which has been assigned a new drive letter, “S “. This S volume now has 2 Terabytes of space.
The task at hand is to move some of the databases from the E
drive to the S drive. This is a
production server so you want to minimize downtime.
Strategy
1. Verify
original file locations.
2.
Make a database backup for safety.
3.
Kill any active spids.
4.
Detach files.
5.
Copy files to new location.
6.
Attach files.
7.
Verify database is backed up.
8.
Delete files in original location.
This proc will create T-SQL and some command code for doing
all the above.
To create calls for all databases.
SELECT 'Exec MoveDB ''' +
name + ''',
''S:\Sql\Data'', ''S:\Sql\Data'', ''S:\Sql\Bak'''
FROM sys.databases
ORDER BY name
I put his proc in the Admin database with my other code
snippets.
USE Admin
If OBJECT_ID('dbo.MoveDB') Is Not Null
DROP PROC dbo.MoveDB
go
CREATE PROC [dbo].[MoveDB]
@DB varchar(100),
@DestDataPath
varchar(300),
@DestLogPath
varchar(300),
@BackupPath
varchar(300) = 'BackupPathHere'
AS
SET NOCOUNT ON
/* DATE AUTHOR REMARKS
11/1/14 PPaiva Initial
creation.
DESCRIPTION
Produces scripts for
moving a database's files via
sp_detach and
CREATE DATABASE FOR ATTACH.
Also produces clean-up
scripts for deleting
the original files, for use after database
has been
successfully moved.
USAGE
MoveDB 'Admin',
'E:\Sql\Data', 'F:\Sql\Logs'
MoveDB 'Sopa',
'E:\Sql\Data', 'F:\Sql\Logs'
DEBUG
-- Location of database
files
SELECT
DB_NAME(database_id), file_id, type_desc, physical_name, database_id
FROM
master.sys.master_files
ORDER BY
DB_NAME(database_id), file_id
-- Generate Exec scripts
for all databases
SELECT 'Exec MoveDB ''' +
name + ''', ''S:\Sql\Data'', ''S:\Sql\Data'', ''S:\Sql\Bak'''
FROM sys.databases
ORDER BY name
*/
DECLARE @s varchar(max),
@sAttach varchar(max) = '',
@sCmdLine varchar(max) = '',
@sCmdLineDel varchar(max) = '',
@sTSql varchar(max) = '',
@sTSqlDel varchar(max) = '',
@crlf varchar(2) = Char(13) + Char(10)
SET @s = '--
*******************************************
-- Script for moving database ' + @DB + '
--
*******************************************'
Print @s
Print ''
SET @s = 'SELECT file_id, type_desc,
physical_name, *
FROM
master.sys.master_files
WHERE
DB_NAME(database_id) = ''' + @DB + ''''
Print '-- Verify original file locations'
Print @s
Print ''
Print '-- Make a backup for safety'
SET @s = 'Exec Admin.dbo.BackupDB
''' + @DB +
''', ''' +
@BackupPath + ''',
''DT'', 1'
Print @s
Print ''
-- Kill any active connections
Print '-- Kill any active spids'
SET @s = 'ALTER DATABASE [' + @DB + ']
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER
DATABASE [' + @DB + ']
SET ONLINE'
Print @s
Print ''
Print '-- Detach'
SET @s = 'Exec
master.dbo.sp_detach_db @dbname = [' +
@DB + ']'
Print @s
Print ''
-- Copy files
SELECT file_id, type_desc, physical_name
INTO #Files
FROM master.sys.master_files
WHERE DB_NAME(database_id) = @DB
DECLARE @MaxFileID
int,
@FileID int
= 0,
@ExistingPath varchar(300),
@Usage
varchar(20),
@FileNameOnly
varchar(100),
@NumFiles int,
@i int
= 0
SELECT @MaxFileID = MAX(file_id),
@NumFiles =
COUNT(*)
FROM #Files
SET @sAttach = 'CREATE DATABASE [' + @DB + '] ON '
WHILE @FileID < @MaxFileID
BEGIN
SET
@i += 1
-- Get
next file
SELECT
@FileID = Min(FILE_ID)
FROM
#Files
WHERE
FILE_ID >
@FileID
-- Get
other attributes
SELECT @ExistingPath =
physical_name,
@Usage = type_desc,
@FileNameOnly = dbo.udfGetFilenameAndExtOnly(physical_name)
FROM
#Files
WHERE
FILE_ID =
@FileID
SET
@sCmdLine += 'Copy
"' + @ExistingPath + '" "'
SET
@sTSql += 'Exec
xp_cmdshell ''Copy "' +
@ExistingPath + '"
"'
SET
@sCmdLineDel += 'Del
"' + @ExistingPath + '"'
SET
@sTSqlDel += 'Exec
xp_cmdshell ''Del "' +
@ExistingPath + '"'''
IF
@Usage = 'Rows'
BEGIN
SET
@sCmdLine += @DestDataPath + '"'
SET
@sTSql += @DestDataPath + '"'''
END
ELSE
BEGIN
SET
@sCmdLine += @DestLogPath + '"'
SET
@sTSql += @DestLogPath +
'"'''
END
SET
@sCmdLine += @crlf
SET
@sCmdLineDel += @crlf
SET
@sTSql += @crlf
SET
@sTSqlDel += @crlf
-- Attach
IF
@i > 1
SET
@sAttach += ', '
IF
@Usage = 'Rows'
BEGIN
SET
@sAttach = @sAttach +
'
(FILENAME = ''' + dbo.udfAddBackslashIfNec(@DestDataPath) + @FileNameOnly +
''')'
END
ELSE
SET
@sAttach = @sAttach +
'
(FILENAME = ''' + dbo.udfAddBackslashIfNec(@DestLogPath) + @FileNameOnly +
''')'
END
SET
@sAttach += '
FOR ATTACH'
Print '-- Command line copy'
Print @sCmdLine
Print ''
Print '-- T-SQL code'
Print @sTSql
Print ''
Print '-- Attach'
Print @sAttach
Print ''
Print '-- Command line DELETE original files'
Print '-- **** Only run this after database has been
attached!! ****'
Print
@sCmdLineDel
Print ''
Print '-- T-SQL DELETE original files'
Print '-- **** Only run this after database has been
attached!! ****'
Print @sTSqlDel
Print '-- DONE'
Print ''
Print ''
