Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Attach and Detach..Again Expand / Collapse
Author
Message
Posted Saturday, March 30, 2002 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 11:14 AM
Points: 6,705, Visits: 1,679
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
Post #3320
Posted Monday, April 08, 2002 8:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:20 PM
Points: 2,105, Visits: 3,560
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #30835
Posted Monday, April 08, 2002 10:39 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 11:14 AM
Points: 6,705, Visits: 1,679
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
Post #30836
Posted Wednesday, August 07, 2002 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 14, 2013 11:41 AM
Points: 120, 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.




Post #30837
Posted Wednesday, August 07, 2002 9:56 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 11:14 AM
Points: 6,705, Visits: 1,679
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
Post #30838
Posted Monday, April 05, 2004 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 07, 2005 12:07 PM
Points: 13, 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).




Post #109869
Posted Monday, April 05, 2004 6:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 22, 2006 7:41 AM
Points: 131, 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

 

Post #109939
Posted Friday, June 23, 2006 3:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 23, 2007 4:05 AM
Points: 97, 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
Post #289628
Posted Friday, June 23, 2006 5:32 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 11:14 AM
Points: 6,705, Visits: 1,679

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
Post #289652
Posted Sunday, July 09, 2006 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 28, 2006 12:31 AM
Points: 4, 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\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.'

 

Post #293000
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse