SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Attach and Detach..Again

By Andy Warren, (first published: 2002/04/05)

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 already:

Reattaching Databases - Some Code AND a Contest! Andy Warren
Reattaching Databases - Some Code AND a Contest - Follow Up! Andy Warren
Copying a Database from Server to Server Rahul Sharma
Detaching and Attaching a Database Brian Knight
Quickly Moving Databases Chris Kempster

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 subject.

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 server\mssql\data\DetachTest_Log.LDF'


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], [size], [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], [size], [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 case.

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!

Total article views: 24220 | Views in the last 30 days: 8
Related Articles

Database is in recovery status.

Database is in recovery status.


Attach a suspect database

Attach a suspect database


Attaching Database

Problem in attaching database


Database Status SUSPECT

Help me: Database Status SUSPECT


how to attach pdf file database to sqlserver 2005

database attachment

sql server 7    
stored procedures