Hi Andy, your article is very goood and As I cuurently find myself in a situation where I now need to use it I have a small problem. First testing on Northwind DB to see that this will work and for purposes of you to know the defaults so that you can point me in the right direction. Below is what I am trying to run in Query Analyser with out suscees as you will see from the error...

Server: Msg 170, Level 15, State 1, Procedure usp_AttachDBWithMissingLogFile, Line 3

Line 3: Incorrect syntax near 'usp_AttachDBWithMissingLogFile'.

Please help- what am I doing wrong? I know it is somthing stupid and small but figured it best to just ask...

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

usp_AttachDBWithMissingLogFile 'Northwind','northwnd','c:\program files\microsoft sql server\mssql\data\northwnd.MDF','northwndLOG','c:\program files\microsoft sql server\mssql\data\northwndLOG.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=northwnd

set @Alt_FileName='c:\program files\microsoft sql server\mssql\data\northwnd.MDF'

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=northwndLOG

set @Alt_FileName='c:\program files\microsoft sql server\mssql\data\northwndLOG.MDF'

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