Move SharePoint Databases without a DBA

Did you ever get a request to move 198 SharePoint databases? Every time I think about working with SQL Server, I remember a session at The March 2008 SharePoint Conference titled, "SharePoint Admin -- the Reluctant DBA." I live the life of the reluctant DBA every day, so I didn't attend that session, but I remember the title.

I inherited a SharePoint development farm. I didn't build it and up until 7 weeks ago, I didn't even know the server names. Now I'm the contact for making decisions about these servers.

Apparently, our SAN guys gave the database server running SQL a new 650GB LUN to present as a disk where content databases will reside and now they want the old 250GB LUN back. So I get an e-mail from one of the SAN guys in India asking to move the content from the old disk to the new disk. Fair enough. I go to see what is on the disk a see 198 SQL MDF data files and 198 SQL Log files (design side note: don’t put data and log files on the same disk – this is a DEV farm where it’s not as critical to follow this rule – definitely follow the rule in your Stage and Prod environments).

Did I mention I work for a large, global corporation? Well we got DBAs. However, to get the DBAs to work for you, you need to submit a Request For Service (It's a Microsoft Word Form) providing an overview of what needs to be done to a Group Mailbox. The people who maintain the mailbox then assign it to the responsible group (I cross my fingers at that point because I once submitted one of these requests to create a new Service Account and they assigned it to my boss who called me and asked what I needed done -- it eventually got assigned to the AD Design guy who sits 2 aisles away from me). After the request is assigned, you wait in queue. This is where I become the reluctant DBA -- I hate to wait in lines.

Okay, so how do you move 198 databases? Find an account that has Sysadmin rights in SQL -- I got lucky and the guy that built the farm used one of my accounts to install. Kick your developers out -- be nice. Verify the database backup ran.

This will take approximately one hour -- Somebody time me:

1. Log on to the server running SQL Server 2005 (1 minute).

2. Open up SQL Server Management Studio and connect to your SQL Instance where the Databases reside (1 minute).

3. Run the following query to generate T-SQL to set all your DBs OFFLINE (Again got lucky -- I ran a google search for "sql server detach multiple databases" and got this post first hit. Thanks vidhya sagar at SQL Servercentral.com -- This is great approach. With SQL Server 2005's "ALTER DATABASE...MODIFY FILE" capability, I modified to just set databases Offline and then bring back Online after files are copied to new location. (1 minute):

set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity') and filename like 'E:\MSSQL2K\Data%'
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
print 'ALTER DATABASE ' + @dbname + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'
print 'go'
print 'print ''Setting of ' + upper(@dbname) + ' database to OFFLINE successfully completed'''
print 'go'
PRINT ' '
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully set OFFLINE'''

4. Run the following query to generate T-SQL to set the new file location and bring your DBs back Online(modify for your environment) (1 minute):

set nocount on

declare @dbname as varchar(80)

declare @server_name as varchar(20)

select @server_name = @@servername

declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity') and filename like 'E:\MSSQL2K5\Data%'

open rs_cursor

Fetch next from rs_cursor into @dbname

IF @@FETCH_STATUS <> 0

PRINT 'No database to backup...Please check your script!!!'

WHILE @@FETCH_STATUS = 0

BEGIN

print 'USE [master]'

print 'GO'

print 'ALTER DATABASE ' + @dbname

print 'MODIFY FILE'

print '(NAME = '''+ @dbname + ''', FILENAME = ' + '''G:\MSSQL2K5\Data\' + @dbname + '.mdf''' + ')'

print 'GO'

print 'ALTER DATABASE ' + @dbname

print 'MODIFY FILE'

print '(NAME = '''+ @dbname + '_log'''+ ', FILENAME = ' + '''H:\MSSQL2K5\Log\' + @dbname + '_log.ldf''' + ')'

print 'GO'

print 'ALTER DATABASE ' + @dbname + ' SET ONLINE'

print 'GO'

print 'print ''Setting of ' + upper(@dbname) + ' database to ONLINE successfully completed'''

print 'go'

PRINT ' '

FETCH NEXT FROM rs_cursor INTO @dbname

END

CLOSE rs_cursor

deallocate rs_cursor

print ' '

print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully set ONLINE'''

5. Copy the output from Step 3 and execute it. All the content db's will be set Offline!!!! (5 minutes)


6. Copy your files to the new location -- WARNING: don't do a cut and paste. COPY FILES -- I've witnessed attaches through SQL GUI fail with the result of data files magically disappearing (Don't get me started about what it takes to get our off-shore Backup guys to restore files from tape) (10 minutes).


7. Copy the output from Step 4 and execute it. All the content db's will be brought back on-line pointing to new file locations!!!! (5 minutes)


8. Verify databases are accessible. (31 minutes)


9. Delete the Old Files. (5 minutes)