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:
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) begin print 'Please use sp_configure to allow updates to system tables' print 'Please make a backup of the master database!' return end --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) --done 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!
