SQLServerCentral Article

Attach and Detach..Again


Attaching and detaching databases has got to be one of the handiest features

in SQL7/2K. It worked well enough in SQL7, but SQL2K added a nice interface in

Enterprise Manager to let you browse the file structure and pick the files you

needed - a great time saver compared to typing in the file names into Query

Analyzer. We've discussed this feature a couple of times here on the site



Databases - Some Code AND a Contest!




Databases - Some Code AND a Contest - Follow Up!




a Database from Server to Server




and Attaching a Database




Moving Databases



Has to be pretty interesting to merit five articles and a ton of discussion

posts. Well, make that six articles now! I'll assume that you've read or or more

of those already and are familiar with the attach/detach process. In this

article I'd like to talk about a couple points we haven't yet addressed on this


The first is the well known limitation of sp_attach_db to only handle up to

16 file names. In general I think if you have more than 16 files you have too

many anyway, but still it would be nice to have some flexibility. In fact we do,

since the limitation is based only on the number of parameters coded in the

procedure (16 of course) and if you read the fine print in BOL you'll see that

you can attach more than 16 files using Create Database with the For

Attach option. Possibly not the biggest issue in the SQL universe, but one worth

knowing I think.

The second is much lesser known and potentially disastrous. As mentioned in a

couple of the articles listed above a common reason to detach a database is so

that you can delete the log file, which is really just a very fast way of

truncating a log file that has grown to a huge size rather than doing dbcc

shrinkfile. This method works fine and we even have sp_attach_single_file_db to

help us easily reattach a db with no log file. Most of us I'll bet have one log

file per database. What happens if you try this when you have two log files? As

long as you have both files when you reattach, everything works fine. But if you

delete one or both, you'll see this:

Pretty depressing if you were doing this to get yourself out of a jam! Even

if you try using sp_attach_db or sp_attach_single_file_db, you get the same

error message. My guess is that there is some info coded into the mdf

that tells the attach process to look for multiple log files. Now if you'll

think back to the first item I mentioned, we know that sp_attach_db is really

just a wrapper that calls Create Database. That got me to thinking - maybe if I

call Create Database directly? I get the same fun error:

create database detachtest

on primary (FileName='C:\program files\microsoft sql server\mssql\data\DetachTest_Data.MDF')

for attach

Server: Msg 1813, Level 16, State 2, Line 1

Could not open new database 'detachtest'. CREATE DATABASE is aborted.

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\DetachTest_Log.LDF' may be incorrect.

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\DetachTest_log2_Log.LDF' may be incorrect.

Time to restore from backup and call it a day? Maybe not. I decided to try to

recreate what Create Database does. After all, everything is stored in a table

right? Basically you need one line in sysdatabases with all the proper values,

plus one line per file in sysaltfiles. That means you have to run sp_configure

to allow updates to system tables. 

Please, please, make a backup of the master database before proceeding!

The code below is set up to handle the scenario where you had two log files

but now only have one. If you know have two instead of three or whatever, you'll

just need to repeat the insert in sysaltfiles for each file you do have,

remembering to increment the @Alt_FileID variable by one each time. I haven't

tested anything other than the two log scenario but I believe it will work for

all other cases with a little tweaking.

There are a bunch of fixed values that get inserted - I arrived at most of

these by creating a database and seeing what I got. Some are documented, some

are not. All of my testing was done on SQL2K, but again, I see no reason why

this shouldn't work on SQL7 server.

Of course nothing is every THAT easy. Sysdatabases has a column called

version that must be set, but it's a computed column. Inserting a row doesn't

trigger the value to update. The only way I could get it to work was to run the

procedure below, then stop and start the service. That updated the version and

everything worked normally after that.

alter proc usp_AttachDBWithMissingLogFile @DBName sysname, @DBFileName nvarchar(250), @DBCompletePath varchar(250), @AltFile1Name varchar(50), @AltFile1CompletePath varchar(250) as

--3/25/02 Andy Warren

--SP_Attach_DB and SP_Attach_Single_File_DB will both fail if the detached mdf had more

--than one log file and any of them is missing - which can commonly happen when the user

--deletes the file on purpose as a way to quickly reset the log file size.

/* Example syntax

usp_AttachDBWithMissingLogFile 'Detachtest','DetachTest_Data','c:\program files\microsoft sql server\mssql\data\DetachTest_Data.MDF','DetachTest_Log','c:\program files\microsoft sql



declare @DB_dbid smallint

declare @DB_SID varbinary(85)

declare @DB_Mode smallint

declare @DB_Status int

declare @DB_Status2 int

declare @DB_CrDate datetime

declare @DB_Reserved datetime

declare @DB_Category int

declare @DB_Cmptlevel tinyint

declare @DB_Version smallint

set nocount on

--make sure we can update system tables

if not exists(select * from sysconfigures where config=102 and status=1)


print 'Please use sp_configure to allow updates to system tables'

print 'Please make a backup of the master database!'



--This part gets the database set up, start by getting the next DBID to be used

select @DB_dbid=max(dbid)+1 from master.dbo.sysdatabases

--fixed values

set @DB_SID=0x01--SA

set @DB_Mode=0 

set @DB_Status=16 

set @DB_Status2=1090519040--fixed value

set @DB_CrDate=getdate() 

set @DB_Category=0 

set @DB_Cmptlevel=80--SQL2K

set @DB_Reserved=0

INSERT INTO [master].[dbo].[sysdatabases]([name], [dbid], [sid], [mode], [status], [status2], [crdate], [Reserved],[category], [cmptlevel], [filename])

values(@DBname, @DB_dbid, @DB_sid, @DB_mode, @DB_status, @DB_status2, @DB_crdate, @DB_Reserved, @DB_category, @DB_cmptlevel, @DBCompletePath)

--then also add the info to sysaltfiles

declare @Alt_fileid smallint

declare@Alt_groupid smallint

declare@Alt_size int

declare@Alt_maxsize int

declare@Alt_growth int

declare@Alt_status int

declare@Alt_perf int

declare@Alt_name nchar (128)

declare@Alt_filename nchar (260)

declare@Alt_createlsn binary (10)

declare@Alt_droplsn binary(10)

--set defaults

set @Alt_FileID=1

set @Alt_GroupID=1

set @Alt_Size=10

set @Alt_MaxSize=-1--unlimited

set @Alt_Growth=10--10%

set @Alt_Status=32770--constant

set @Alt_Perf=0--constant

set @Alt_Name=@DBFileName

set @Alt_FileName=@DBCompletePath

set @Alt_CreateLSN=0x00000000000000000000--constant

set @Alt_DropLSN=0x00000000000000000000--constant

insert into sysaltfiles([fileid], [groupid], , [maxsize], [growth], [status], [perf], [dbid], [name], [filename], [createlsn], [droplsn])

values(@Alt_FileID, @Alt_GroupID, @Alt_Size, @Alt_MaxSize, @Alt_growth, @Alt_status, @Alt_perf, @DB_dbid, @Alt_Name, @Alt_filename, @Alt_createlsn, @Alt_droplsn)

--for each log file add an additional row to sysaltfiles, remember to increment the fileid

--each time

set @Alt_FileID=2

set @Alt_GroupID=0

set @Alt_Size=10

set @Alt_MaxSize=-1--unlimited

set @Alt_Growth=10--10%

set @Alt_Status=32834--constant

set @Alt_Perf=0--constant

set @Alt_Name=@AltFile1Name

set @Alt_FileName=@AltFile1CompletePath

set @Alt_CreateLSN=0x00000000000000000000--constant

set @Alt_DropLSN=0x00000000000000000000--constant

insert into sysaltfiles([fileid], [groupid], , [maxsize], [growth], [status], [perf], [dbid], [name], [filename], [createlsn], [droplsn])

values(@Alt_FileID, @Alt_GroupID, @Alt_Size, @Alt_MaxSize, @Alt_growth, @Alt_status, @Alt_perf, @DB_dbid, @Alt_Name, @Alt_filename, @Alt_createlsn, @Alt_droplsn)


print 'Run sp_configure to disallow system updates'

print 'Stop and start the service to get the attached database working correctly'

print 'Run a full backup of the master database once you can access the attached database.'

I can't stress strongly enough the danger inherent in updating system tables

directly. It's ok to do it, just be prepared to restore from backup if something

goes wrong. Make sure you know how to restore the master database as well. One

hint on that - stop the service before you try this procedure and make a

copy of master.mdf. If you end up needing to restore, stop the service,

overwrite the existing master.mdf with the copy you made earlier and then

restart. Nothing to it! Still a good idea to have a real backup copy...just in


I'm definitely looking forward to your comments on this one. Maybe someone

has found an easier way that this? Thanks for reading this and thanks for

visiting SQLServerCentral.com!


5 (1)




5 (1)