Mass Detach and Reatach.. Help required...!!!!

[Skiboy]

Hi,

We are implementing a SAN solution throughout our network.

I am planning on moving the entire contents of an instance to another Server. I need to do this movement with the mininmum of down time as this a production DB for WEB apps and I have to minimize the window of downtime (I think that realistically I have 30mins to 1 hr at my disposal).

I am planning on doing a full backup of the Instance. Detaching all 49 DB's. Cutting over the DB's (but not the log files). reattaching them (with the create log file option). And hopefully this will bring everything back up.
Obviously before the initial detach, I am configuring the new Server to the same IP and domain name settngs, so I don't want any advice on this part....only the fastest way to bring down, move the datafiles, and bring up the databases back online.

As an afterthought. Would it be quicker to restore the backup and process the transaction logs from the time difference??? any ideas.

Oh yeah. I have 60GB of databases to cut across and a 1GB backbone to move them along. This shouldn't be bottleneck should it??

 

[Corran007]

If you really want to minimize downtime, you should proably do them one at at time, or if an app needs multi databases, do them in groups.

prewrite out all your sql statments for each one for detach, copy them , then attach. its up to you if you want to do them togetehr or seperatly.

heck you may want to even thing about using osql and write each databse in its own batch file

detach the database
copy to the new location
attach the database

then all you have to do is run the batch. it will take a bit ot time to write them. but will generate the least downtime per database. If you want, you could have a master batch call each batch one after another to really speed it up.

now dont forget your users wont match up unless you restore the master and msdb datbase from the old one.

 

[Skiboy]

Hi Corran007,

Thanks for the advice.

Previously, using Query Analyser I have encountererd problems with additional connections (to the DB I want to detach) causinf the sp_detach_db script to fail.
Subsequently I have taken to using EM to detach as it has the notify and detach users option.

Do you per chance know of a script to incorporate into the sp_detach/attach DB script to automatically log off extra connections??

 

[Corran007]

sure. i have a script that kills all connections to the datbase. what i would do is kill the conns, then put it in single user mode, then detach, copy, then reatach.

i got this from somewhere


CREATE PROCEDURE uspKillAllProcesses
@pDbName varchar (100)=NULL, /*database where we will kill processes.
If NULL-we will attempt to kill processes in all DBs*/
@pUserName varchar (100)=NULL /*user in a GIVEN database or in all databases where such a user name exists,
whose processes we are going to kill. If NULL-kill all processes. */

/*Purpose: Kills all processes in a given database and/or belonging to a specified user.
If no parameters supplied it will attempt to kill all user processes on the server.
Server: all
Database: DBAservice
Created: Igor Raytsin,Yul Wasserman 2000-10-13
Last modified: Yul Wasserman 2002-02-08
*/
AS
SET NOCOUNT ON

DECLARE @p_id smallint
DECLARE @dbid smallint
DECLARE @dbname varchar(100)
DECLARE @exec_str varchar (255)
DECLARE @error_str varchar (255)

IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or @pDbName is NULL)
BEGIN
Set @error_str='No database '+ltrim(rtrim(@pDbName)) +' found.'
Raiserror(@error_str, 16,1)
RETURN-1
END
Create Table ##DbUsers(dbid smallint,uid smallint)

If @pUserName is not null
BEGIN
--Search for a user in all databases or a given one
DECLARE curDbUsers CURSOR FOR
SELECT dbid,name FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or @pDbName is NULL
OPEN curDbUsers
FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str='Set quoted_identifier off
INSERT ##DbUsers SELECT '+cast(@dbid as char)+', uid FROM '+@dbname+'.dbo.sysusers
WHERE name="'+ltrim(rtrim(@pUserName))+'"'
EXEC (@exec_str)
FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
END
CLOSE curDbUsers
DEALLOCATE curDbUsers
If not exists(Select * from ##DbUsers)
BEGIN
Set @error_str='No user '+ltrim(rtrim(@pUserName)) +' found.'
DROP TABLE ##DbUsers
Raiserror(@error_str, 16,1)
RETURN-1
END
END

ELSE --IF @pUserName is null
BEGIN
INSERT ##DbUsers SELECT ISNULL(db_id(ltrim(rtrim(@pDbName))),-911),-911
END

--select * from ##dbUsers

DECLARE curAllProc CURSOR FOR
SELECT spid,sp.dbid FROM master.dbo.sysprocesses sp
INNER JOIN ##DbUsers t ON (sp.dbid = t.dbid or t.dbid=-911) and (sp.uid=t.uid or t.uid=-911)
OPEN curAllProc
FETCH NEXT FROM curAllProc INTO @p_id, @dbid

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str = 'KILL '+ Convert(varchar,@p_id)+ ' checkpoint'
SELECT @error_str = 'Attempting to kill process '+Convert(varchar,@p_id)+' in database '+db_name(@dbid)
RAISERROR (@error_str,10,1)with log
EXEC (@exec_str)
FETCH NEXT FROM curAllProc INTO @p_id, @dbid
END
CLOSE curAllProc
DEALLOCATE curAllProc
DROP TABLE ##DbUsers
SET NOCOUNT OFF

that kills them all.

this can set them to skingle user mode

CREATE PROC uspSetSingleuser
/*******************
Production server: all
Production database: DBAservice
Purpose: sets a database in single user /dbo only mode,
can be used with restore operations
Developed: Yul Wasserman 01/23/02
*****************/
@pDatabase varchar (100), @pDboOnly bit=1, @pAttempts int =15
AS
Declare @i int --counter
Declare @MyError int
set @i=0
IF @pDboOnly =1
Begin
exec sp_dboption @pDatabase,'dbo use only','true'
End
exec dbaService.dbo.uspKillAllProcesses @pDatabase
exec sp_dboption @pDatabase,'Single user', 'true'
Set @MyError=@@Error
While (@MyError <>0 AND @i<@pAttempts)
Begin
exec dbaService.dbo.uspKillAllProcesses @pDatabase
exec sp_dboption @pDatabase,'Single user','true'
Set @MyError=@@Error
set @i=@i+1
End


you can just set it up to run together since both are stored procedures.

 

[Skiboy]

EXCELLENT!!

THANK YOU

I will test this one ASAP (Sorry that I don't have a tidbit useful script to reciprocate with).

FYI I have previously asked some colleagues of mine about such a script and I was told that it is in the 'too hard basket' (whether or not they were fobbing me off at the time is neither here nor there). but your piece of info (hopefully) completes the circle on a problem that has been bugging me for ages, will help me out NO-END..... Take a bow!!

Skiboy

P.S Maybe readers out there from Microsoft-land might consider adding this piece of code to their sp_detach command as it is nigh on useless for a OLTP prod environment where there is almost always more than one connection to a data-source.