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

By Andy Warren, 2001/10/02

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

Total article views: 5652 | Views in the last 30 days: 0

FORUM

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

to rename a file by attaching datestamp at the end of filename using SSIS File task

Unexpected Result in sp_send_dbmail file attachment

query(dbname,size,recoverymodel)

query(dbname,size,recoverymodel)

in SQL 2008,only MDF,NDF,no Ldf,how to attach

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

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

to rename a file by attaching datestamp at the end of filename using SSIS File task

Unexpected Result in sp_send_dbmail file attachment

query(dbname,size,recoverymodel)

query(dbname,size,recoverymodel)

in SQL 2008,only MDF,NDF,no Ldf,how to attach

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Already a member? Jump in: