THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Scripting Database Attach for SAN based LUN migration

Recently I have been involved in some cross server database migrations to balance load in my environment, and as apart of this, I have needed to provide some automation scripting.  To help minimize overall downtime the use of multiple SAN LUNs was used over time to migrate databases one at a time during scheduled downtimes onto a new SAN LUN.  When it came time to perform the actual migration the new SAN LUN was removed from the original server and added to the new server.  This required attaching each of the databases to the new server once the LUN was available.  I have written code similar to this in the past so to document it so that I don't have to write it again I decided to blog about it.

EXECUTE sp_msforeachdb 'USE [?];
DECLARE @script varchar(max)
SELECT @script = ''CREATE DATABASE ['' + db_name() + ''] ON 
''; 

SELECT @script = @script + ''( FILENAME = N''''''+ physical_name + ''''''),
''
FROM sys.master_files
WHERE database_id = db_id() 

SELECT @script = left(@script, len(@script)-3) + ''
FOR ATTACH
GO
'' 

SELECT @script = @script + ''EXEC ['' +db_name()+''].dbo.sp_changedbowner @loginame=N''''''+SUSER_SNAME(owner_sid)+'''''', @map=false
GO''
FROM sys.databases
WHERE database_id = db_id() 

PRINT @script'

Essentially, all this code does is enumerate the databases on the original server, and then script out the necessary script to attach the database on the new server.  As long as the drive and path names don't change, this script will work as provided.

Published Wednesday, August 05, 2009 2:45 AM by Jonathan Kehayias

Comments

 

RowlandG said:

Nice! Thanks for your work on this

August 5, 2009 8:54 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement