Attach and Detach..Again

  • Andy Warren

    SSC Guru

    Points: 119675

  • David Benoit

    SSC-Dedicated

    Points: 34562

    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

    SSC Guru

    Points: 119675

    Good, will be nice to get someone else to test besides me!

    Andy

  • neder

    SSC Eights!

    Points: 888

    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

    SSC Guru

    Points: 119675

    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/

  • mlsmith@wilmer.com

    SSC Rookie

    Points: 31

    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

    SSCommitted

    Points: 1639

    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

    Ten Centuries

    Points: 1069

    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

    SSC Guru

    Points: 119675

    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.

  • Ray Cox

    SSC Rookie

    Points: 40

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

     

  • Roger L Reid

    SSCommitted

    Points: 1984

    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

  • Andy Warren

    SSC Guru

    Points: 119675

    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'

  • Andy Warren

    SSC Guru

    Points: 119675

    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 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply