I believe you can create a stored procedure, when the connection is set to allow updates, that will keep that setting (you won't have to set server-wide 'allow updates' again when running it).

First, thanks for the article.

I am in agreement with your comment about having more than 16 files.

A possible exception would be where databases can be in the many terabytes range on machines with multiple processors and multiple controllers with many RAID devices (NADs or SANs).  In these few situations there may be good reason to have more than 16 files where one wants to take advantage of striped backups and restores to minimize time of these operations.

GaryA

Glad you found it helpful. You'll have a better chance of a reply on your question if you start a separate post I think.

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\dataorthwnd.MDF','northwndLOG','c:\program files\microsoft sql server\mssql\dataorthwndLOG.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\dataorthwnd.MDF'
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=northwndLOG
set @Alt_FileName='c:\program files\microsoft sql server\mssql\dataorthwndLOG.MDF'
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)

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