Attach and Detach..Again
-
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,
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
-
Good, will be nice to get someone else to test besides me!
Andy
-
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.
-
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
-
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
-
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
-
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\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.'
-
Nice set of articles. If you get inspired, here are two situations I have run into moving databases around.
1. Using detach/attach, you lose the dbo information. I've now gotten good at remembering to print off all kinds of properties, but I've been too lazy to code it up.
2. A broad, general routine that set the dbo right could also ensure the colation is maintained.
3. This one's annoying: since by default the log file gets attached (not that it needs to, since if you detached, it should be clean), if you've moved it to a disk with a different block size, it refuses to attach! (The solution is just to not attach the log and let it build a new one - that assumes it was a perfectly clean detach and you're not salvaging files off a burnt server.)
Always happy to suggest work for other people to do!
Roger L Reid
-
Not sure about the error, but you might try this:
create
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'
-
I didnt know that about the block size. Is the error message helpful when it happens? Nothing wrong with suggestions:-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply