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


Attach and Detach..Again


Attach and Detach..Again

Author
Message
Andy Warren
Andy Warren
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: Moderators
Points: 14857 Visits: 2730
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/attachanddetachagain.asp>http://www.sqlservercentral.com/columnists/awarren/attachanddetachagain.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
David Benoit
David Benoit
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4350 Visits: 3650
Andy,

I haven't tested this yet but I will be. I had thought that the "two log file, missing one" scenario was one of those issues that you just had to accept that a restore from backup was necessary. Very cool work.

I will let you know if I encounter any stumbling blocks.

David

David

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Andy Warren
Andy Warren
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: Moderators
Points: 14857 Visits: 2730
Good, will be nice to get someone else to test besides me!


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
neder
neder
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 61
I dont think this will work in 7.0 though I have not tested it yet. sysaltfiles in 7.0 doesnt contain user db info.



Andy Warren
Andy Warren
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: Moderators
Points: 14857 Visits: 2730
Have to admit I didnt try it, mainly because I use SQL2K exclusively these days. Could be that the code would have to be tweaked, but I think the basic idea would still work.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
mlsmith@wilmer.com
mlsmith@wilmer.com
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 1

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





Gary Andrews
Gary Andrews
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 1

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


Derek Wallace
Derek Wallace
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 1
I found your article excellent. I suppose to a degree it has taken away my 'no go' attitude towards the master database. Now having said that - your comments on taking a backup and copy of the master.mdf have been well noted !! Just a quick query to people on the thread, does anyone have a good resource in relation to the startup parameters within EM, ie -T3608 etc. Thanks again. Derek
Andy Warren
Andy Warren
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: Moderators
Points: 14857 Visits: 2730

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.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Ray Cox
Ray Cox
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

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], [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\data\northwndLOG.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.'


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search