SQL-Articles
On Premise and Cloud Database Knowledge Base

Attach all user databases

I was doing an upgrade of one of our development servers to SQL Server 2008. To minimize the downtime I decided to attach all the user databases using script. Basically, I am attaching the db from the same location there were present earlier to being detached.
Consider the following example, If I have a database named “Test” in SQL 2005 that is currently residing in the following locations, C:databaseTest.mdf & C:databaseTest_log.ldf and another database named “Test_2008” in C:temptest_2k8.mdf & C:temptest2k8_log.ldf then the script I wrote would prepare the script to attach the database test from the same location.

Following are the steps I did prior to upgrading:

1. Run the attach all user database script. The o/p of which will give a script to attach all the databases which are in C:database and C:temp etc.
2. Run the detach all user database script, which will detach all the user databases.
3. Install SQL Server 2008 and related services.
4. Run the o/p of step1, which will eventually attach all the user databases from their respective positions they were earlier prior to getting detached.

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'AttachAllUserDatabases')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[AttachAllUserDatabases] AS RETURN')
END
GO
ALTER PROCEDURE dbo.AttachAllUserDatabases
AS
BEGIN
DECLARE @dbname varchar(200)
DECLARE @sql varchar(max)
DECLARE @server_name varchar(200)
DECLARE @currentdate varchar(200)
DECLARE @dypart1 varchar(2)
DECLARE @dypart2 varchar(3)
DECLARE @dypart3 varchar(4)
SET @server_name=@@SERVERNAME
SET @dypart1 = datepart(dd,getdate())
SET @dypart2 = datename(mm,getdate())
SET @dypart3 = datepart(yy,getdate())
SET @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT 'Print ''#####################################################################'''
PRINT 'Print ''# SERVERNAME : ' + @server_name + ' DATE : ' + @currentdate +' #'' '
PRINT 'Print ''#####################################################################'''
DECLARE dbname CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master','msdb','model','tempdb') -- excluding the system databases
ORDER BY name ASC
OPEN dbname
FETCH NEXT FROM dbname INTO @dbname
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT ''
PRINT 'GO'
SET @SQL='sp_attach_db ' + @dbname + ',' + (SELECT STUFF((SELECT '' + ', ' + '''' + Physical_name + ''''
FROM sys.master_files WHERE database_id=db_id(@dbname) FOR XML PATH('')),1,1,''))
PRINT @sql
PRINT 'GO'
PRINT 'Print ''The database ' + @dbname + ' has been attached successfully'''
PRINT 'GO'
FETCH NEXT FROM dbname INTO @dbname
END
CLOSE dbname
DEALLOCATE dbname
END

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*