All our databases server using SAN (Storage Area Network) for the disk storage. Last time we used DELL AX100 for the storage. After that we upgraded to DELL MD3000. DBA was asked to move the databases to the new disk.
We have hundred of databases on one server. The best way to do this is using detach and attach method. In this case, we want to move all of user databases, so what we did is just detach all databases, stop the SQL Server, move the data file to the new disk, start the SQL Server and attach all the data files. We need downtime quite long to copy the files to new storage. If you does not allow to downtime quite long, just detach and attach the databases one by one. In my case, it is fine to downtime about 1 hour 
.
What I want to share is a script to generate T-SQL for detaching and attaching the database. Can you imagine if we use Enterprise Manager to detach and attach thousand or hundred databases ?
Step by step to move the data file
1. Before you stop the SQL Server, run below script to generate attach script for all user databases.
SET NOCOUNT ON
DECLARE @dbname SYSNAME
DECLARE @sqlstr VARCHAR(4000)
DECLARE @filename VARCHAR(4000)
DECLARE @result VARCHAR(8000)
DECLARE @Counter TINYINT
DECLARE @NFiles TINYINT
IF OBJECT_ID('tempdb..#db') IS NOT NULL
DROP TABLE #db
--exclude system databases
SELECT name INTO #db
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','tempdb','msdb')
ORDER BY name
IF OBJECT_ID('tempdb..#files') IS NOT NULL
DROP TABLE #files
CREATE TABLE #files
(
DbName SYSNAME,
LogicalName SYSNAME,
FileId INT,
FileName VARCHAR(1000),
)
IF OBJECT_ID('tempdb..#result') IS NOT NULL
DROP TABLE #result
CREATE TABLE #result
(
sql VARCHAR(8000)
)
--get all files
WHILE (1 = 1)
BEGIN
SET @dbname = NULL
SELECT TOP 1 @dbname = name FROM #db
IF @dbname IS NULL
GOTO _FINISH1
SET @sqlstr = ' USE ' + @dbname
+ ' INSERT INTO #files (DbName, LogicalName, FileId, FileName)'
+ ' SELECT db_name(), name, fileid, filename '
+ ' FROM sysfiles '
+ ' ORDER BY fileid '
EXEC (@sqlstr)
DELETE FROM #db WHERE name = @dbname
END
_FINISH1:
--generate attach script
WHILE (1 = 1)
BEGIN
SET @dbname = NULL
SELECT TOP 1 @dbname = DbName
FROM #files
ORDER BY DbName
IF @dbname IS NULL
GOTO _FINISH2
SET @Counter = 1
SELECT @NFiles = COUNT(*) FROM #files WHERE DbName = @dbname
SET @result = 'exec sp_attach_db N''' + @dbname + ''''
WHILE (@Counter <= @NFiles)
BEGIN
SELECT @Filename = FileName
FROM #files
WHERE DbName = @DbName AND FileId = @Counter
SET @result = @result + ',@filename' + CAST(@counter as VARCHAR)
+ ' = N''' + RTRIM(@FileName) + ''''
SET @Counter = @Counter + 1
END
INSERT INTO #result
SELECT @result
DELETE FROM #files WHERE DbName = @dbname
END
_FINISH2:
SELECT * FROM #result
DROP TABLE #db
SET NOCOUNT OFF
|
This is an example of the result of above script for AdventureWorks and AdventureWorksDW.
exec sp_attach_db N'AdventureWorks',@filename1 = N'D:DataAdventureWorks_Data.mdf'
,@filename2 = N'E:LogAdventureWorks_Log.ldf'
exec sp_attach_db N'AdventureWorksDW',@filename1 = N'D:DataAdventureWorksDW_Data.mdf'
,@filename2 = N'E:LogAdventureWorksDW_Log.ldf'
|
 In my local PC, I put all data files (*.mdf, *.ndf) in D:DATA and log files (*.ndf) in E:LOG |
Keep this result to another text file
2. Detach All User Databases
Run below query than run the result of this query to detach all user databases. To remainder, detach process will fail if there is an application that using this database. Just make sure there is no process hold this database if detach process is failed. If you detach database using Enterprise Manager, you will see how many connection that holding specified the database. Just clear the connection if you want to detach that database immediately without want to wait the process is done.
--exclude system databases
SELECT replace('exec sp_detach_db ''@dbname'',''true''','@dbname', name) AS result
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model')
|
3. Stop SQL Server Database Engine
4. Move all data files and log files to the new disk
5. Start SQL Server Database Engine
6. From result of step 1, change old path with new path e.g.: D:DATA be F:SQLDATA than run this script to attach back all databases.
Last time I faced problem with Sharepoint 2003 search. It is related to SQL Server 2000 Full Text Catalog. When I tried to get support from Microsoft, they do not support SQL Server 2000 SP3 anymore. Because of this cause, finally we upgraded our SQL Server 2000 to SP4. Yes, there are some problems after upgrading but my Sharepoint problem resolved by this upgrading and I am able to get support from Microsoft again if there is an issue on my database server in the future.
Before apply Service Pack, do not do it directly on production environment. Do it on development and testing environment first if possible. Find out other DBA issues from forum and prepare the solution. Prepare the worst case to downgrade the Service Pack ( usually the SQL Server need to reinstall)
