Reattaching Databases - Some Code AND a Contest - Follow Up!

,

Back on September 4th I posted an article

about using DMO to attach a LOT of databases very quickly and easily. If you

haven't read it yet, take a couple minutes to do so before continuing. I also

challenged our readers to find a way to implement the solution using T-SQL.

Whether you use DMO or T-SQL to solve a problem usually depends on your

background. I use VB quite often, so being able to set a reference to the DMO

library and keep working in the same environment is a productive way for me to

work. So my thought was - how would someone really comfortable with T-SQL solve

the problem?

I'm pleased to announce Wynn Muse as the winner of the contest. I'm including

the entire script as he forwarded to me, you can download the file here,

and it will also be added to our script

library as well. Wynn will be receiving a copy of JMS Messaging, published

by Wrox (yes, an off topic book, but is any technology book really off topic?).

It's an excellent piece of code - even if you don't need to accomplish this

particular task, I think you'll benefit from working through the solution. Never

know what you'll find when you read code -- I make the point because I was

pleasantly surprised to find an answer to a question I've been asked several

times -- how do you get a list of file names from a folder into a table?

Solutions I've seen or thought of include using the file system object (hard

from T-SQL, easy from DTS) to using xp_cmdshell to execute the dir command and

redirect it's output to a file, then import the file (using DTS). Stop and think

for a minute - how would you do it?

Long pause while you think.

Now take a look at this excerpt from the script:

CREATE TABLE #dir_result (filename varchar (255), dbname varchar(255))

EXEC master..xp_sprintf @strExec OUTPUT, 'dir /b %s*.mdf', @MDFpathFromServer

INSERT #dir_result (filename)

EXEC master..xp_cmdshell @strExec

The real workhorse is still xp_cmdshell - but the trick is to remember it

returns a result set just like any other stored procedure - and in this case

it's the list of files we need! Wynn uses the xp_sprintf procedure to do the

work of building the final string to get the exact directory string he needs - I

think a cleaner solution than using Replace.

Thanks and congratulations again to Wynn - a great job! As always, post your

comments or ideas in the attached discussion forum, I look forward to hearing

from you.

 

IF EXISTS (SELECT name FROM sysobjects WHERE name = N'sp_attach_db_from_dir' AND type = 'P')

DROP PROCEDURE sp_attach_db_from_dir

GO

CREATE PROCEDURE sp_attach_db_from_dir 

@MDFpath varchar (255), 

@serverMDFpath varchar (255) = NULL,

@sExclude varchar (8000) = 'msdbdata, northwnd'

/*

Procedure sp_attach_db_from_dir

Copies mdf files located in a directory on a client and attaches them to the connected server

Usage

sp_attach_db_from_dir @MDFpath=directory which contains the mdf files for attaching

[,@serverMDFpath=path to the server's main db folder as seen from the server (default, NULL)]

[,@sExclude=mask specifying which existing MDFpath files to exclude (default, 'msdbdata, northwnd']

Notes

* Most of the time the @MDFpath will not be the path that the server can use to access the MDF files 

These three things can happen -

1) The @MDFpath is used and modifed to UNC using the admin share) (ex. '\\machinename\C$\Temp\db') 

2) If the sp is run locally, the @MDFpath is used without modifications.

3) If the @MDFpath is entered in UNC notation to begin with, then the @MDFpath is used without modifications.

* Since the admin share is used, your MSSQLSERVER service must be started by an account with admin rights (the LocalSystem won't work)

You can, if you are using the LocalSystem account, just run the sp locally on the server (the MDFpath must also be accessible to LocalSystem)

Or you can use UNC to access a share that has "everyone" access (accessible to LocalSystem).

* When @serverMDFpath is NULL the DATA path entered during the SQL Server installation is used (ex. 'C:\Program Files\Microsoft SQL Server\MSSQL\Data')

* Enter @sExclude values as a comma separated string (ex. 'mydb1, mydb2, mydb3')

* There is no need to exclude existing dbs, these are automagically removed from the attach process - except for the defaults ('msdbdata, northwnd')

* This procedure exploits the fact that SQL 2K can attach a db without the existence of the LDF - No LDF are used in the copy and attach process

* This procedure assumes that all user dbs are named with the "databasename_data.mdf" convention.

Contact

wynn.muse@routematch.com 

*/

AS

-- For the populating the temptable & the cursor

DECLARE @strExec varchar (8000)

-- For the cursor

DECLARE @filename varchar(255)

DECLARE @logfilename varchar(255)

DECLARE @dbname varchar(255)

DECLARE @res int

-- For the Exclusions

DECLARE @value varchar (8000)

DECLARE @iSnag smallint

-- For resolving the MDFpath for the server

DECLARE @MDFpathFromServer varchar (255)

SET NOCOUNT ON

IF @MDFpath IS NULL OR @MDFpath = ''

BEGIN

RAISERROR ('The MDFpath entered does not exist.',16,1) 

RETURN 

END 

-- =============================================

-- Resolve the MDFpathFromServer

-- =============================================

IF HOST_NAME() <> SERVERPROPERTY('MachineName')

BEGIN

IF LEFT(@MDFpath, 2) = '\\'

SET @MDFpathFromServer = @MDFpath

ELSE

SET @MDFpathFromServer ='\\'+(SELECT HOST_NAME())+'\'+STUFF(@MDFpath,2,1,'$')

END

ELSE

SET @MDFpathFromServer = @MDFpath

IF RIGHT(@MDFpathFromServer,1) <> '\'

SET @MDFpathFromServer = @MDFpathFromServer+'\'

EXEC master..sp_MSget_file_existence @MDFpathFromServer, @res out

IF (@res = 0)

BEGIN

RAISERROR ('Either the MDFPath <%s> does not exist or the MSSQLSERVER Service Account can''t access it.',16,1, @MDFpathFromServer) 

RETURN 

END

-- =============================================

-- Resolve the serverpath 

-- =============================================

IF @serverMDFpath IS NULL OR @serverMDFpath = ''

BEGIN

EXEC sp_MSget_setup_paths '', @serverMDFpath OUT

SET @serverMDFpath = @serverMDFpath+'\Data'

END 

IF RIGHT(@serverMDFpath,1) <> '\'

SET @serverMDFpath = @serverMDFpath+'\' 

-- =============================================

-- Manage the exclusions

-- Take the array and throw it into a table

-- =============================================

IF CHARINDEX('msdbdata',@sExclude)=0

SET @sExclude = @sExclude +', msdbdata'

IF CHARINDEX('northwnd',@sExclude)=0

SET @sExclude = @sExclude +', Northwnd'

IF OBJECT_ID('tempdb..#tDbName') IS NOT NULL

DROP TABLE tempdb.#tDbName

CREATE TABLE #tDbName (dbname varchar(8000))

WHILE CHARINDEX(',', @sExclude)>0

BEGIN

SET @value = SUBSTRING(@sExclude,1, CHARINDEX(',',@sExclude)-1)

INSERT #tDbName VALUES(@value)

SET @iSnag = DATALENGTH(@value) + 1

SET @sExclude = LTrim(Right(@sExclude,DATALENGTH(@sExclude) - @iSnag))

END

INSERT #tDbName VALUES (@sExclude)

-- =================================================

-- Create and populate a temp table with the MDFpath filenames

-- =================================================

IF OBJECT_ID('tempdb..#dir_result') IS NOT NULL

DROP TABLE tempdb.#dir_result

CREATE TABLE #dir_result (filename varchar (255), dbname varchar(255))

EXEC master..xp_sprintf @strExec OUTPUT, 'dir /b %s*.mdf', @MDFpathFromServer

INSERT #dir_result (filename)

EXEC master..xp_cmdshell @strExec

IF NOT EXISTS(SELECT * FROM #dir_result) 

BEGIN

RAISERROR ('No MDFs to attach: Check your directory.',16,1) 

RETURN

END 

IF EXISTS(SELECT * FROM #dir_result WHERE filename LIKE '%d.') 

BEGIN

RAISERROR ('No MDFs to attach: Check your directory.',16,1) 

RETURN

END

-- =============================================

-- Clean-up the temp table:

-- Remove the server's existing dbnames

-- Remove the null field carried over from the dir

-- Remove any exclusions

-- =============================================

UPDATE #dir_result 

SET dbname = LEFT(filename,CHARINDEX('.MDF',filename)-1)

WHERE CHARINDEX('_', filename)=0

UPDATE #dir_result

SET dbname = LEFT(filename,CHARINDEX('_',filename,LEN(filename)-9)-1)

-- We have some dbnames that include the underscore, hence the third argument in the CHARINDEX

WHERE CHARINDEX('_', filename)>0

DELETE #dir_result 

WHERE dbname IN

(SELECT CATALOG_NAME

FROM INFORMATION_SCHEMA.SCHEMATA) 

OR filename IS NULL

OR dbname IN

(SELECT dbname from #tDbName)

IF NOT EXISTS(SELECT * FROM #dir_result) 

BEGIN

RAISERROR ('No MDFs to attach: Either your exclusions are filtering them all out or these dbs have already been attached.',16,1) 

RETURN

END 

-- =============================================

-- Copy the database(s) form client to server

-- Attach the database(s) 

-- =============================================

IF OBJECT_ID('tempdb..#copy_result') IS NOT NULL

DROP TABLE tempdb.#copy_result

CREATE TABLE #copy_result (filename varchar (255), dbname varchar(255))

DECLARE cAttach CURSOR

READ_ONLY

FOR SELECT filename, dbname FROM #dir_result

OPEN cAttach

FETCH NEXT FROM cAttach INTO @filename, @dbname

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

-- xcopy the files to the server and ensure that it was successful

EXEC master..xp_sprintf @strExec OUTPUT, 'xcopy "%s%s" "%s"', @MDFpathFromServer, @filename, @serverMDFpath

DELETE #copy_result

INSERT #copy_result (filename)

EXEC master..xp_cmdshell @strExec 

IF NOT EXISTS (SELECT * FROM #copy_result WHERE filename LIKE '%file(s) copied%')

RAISERROR('xcopy not successful, file may be in use, check permissions, etc.',16,1)

SET @filename = @serverMDFpath+@filename

--insurance against any lingering log files

SET @logfilename = stuff(@filename,datalength(@filename)-7,10,'log.ldf')

EXEC master..xp_sprintf @strExec OUTPUT, 'del "%s"', @logfilename

EXEC master..xp_cmdshell @strExec , NO_OUTPUT

EXEC @res=sp_attach_db @dbname, @filename

IF (@res <> 0)

RAISERROR ('Error attaching %s database.',16,1,@dbname) 

ELSE

PRINT Char(13) + 'Attach operation of ' + @dbname + ' successful'

END

FETCH NEXT FROM cAttach INTO @filename, @dbname

END

CLOSE cAttach

DEALLOCATE cAttach

GO

Rate

Share

Share

Rate