Detach Move Db & Log File Reattach

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Detach Move Db & Log File Reattach

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-11-25 : 00:28:12

I am moving my db's and logs to separate drives that are RAID 10 devices, I would like feedback on this process or suggestions/areas of improvement for my script.
I have tested this on my dev/qa servers and seems to work fine.
Is their a better way to move dbs and logs to separate drives, what have I missed here before I do this against my production boxes.

/*
Location of User Databases
For best performance, user database files (MDBs) should be located on their own array (RAID 1, 5, or 10),
separate from all other data files, including log files.
If you have multiple large databases on the same SQL Server,
consider locating each separate database file(s) on its own array for less I/O contention.

Location of Log Files
Ideally, each log file should reside on its own separate array
(RAID 1 or 10, RAID 5 will slow down transaction log writes more than you would like).
The reason for this is because most of the time, transaction logs experience sequential writes,
and if the array can write the data sequentially
(not having to interrupt itself to perform other reads and writes),
then sequential writes are very fast. But if the array can't write sequentially because it
has to random perform other reads and writes, sequential writes can't be performed,
and performance suffers.
*/

USE MASTER

Set NoCount ON
Declare
@Execute as bit, @DbName as sysname,
@xcopy as varchar(1000), @sql as Nvarchar(2000), @cmd as NVarchar(500),

@FilePrimary as varchar(255), @FileLog as varchar(255),
@FileFrom as varchar(255), @DbMdfFile as varchar(255), @LogLdfFile as varchar(255),
@TargetLogLocation as Varchar(255), @TargetDbLocation as Varchar(255),

@sValidSource as Varchar(255), @sValidTarget as Varchar(255),
@FileCount as int, @StringLength as int, @MyChar as Char(1),
@FileName as Varchar(50), @iX as int, @iL as int, @iFile as int,
@RelocateDATAFiles as bit, @RelocateLOGFiles as bit

--******************************************************************************************
--******************************************************************************************

Set @Execute = 0 -- 0 = DO NOT Execute 1 = EXECUTE
Set @RelocateDATAFiles = 0 -- 0 = DO NOT copy 1 = COPY
Set @RelocateLOGFiles = 1 -- 0 = DO NOT copy 1 = COPY

--******************************************************************************************
--******************************************************************************************
-- 'C:\Program Files\Microsoft SQL Server\MSSQL\data\'
-- 'C:\MSSQL\Data\'
-- SERVERS s/b
-- F = DATA
-- G = LOGS
-- H = BACKUPS

Set @TargetDbLocation = 'F:\Program Files\Microsoft SQL Server\MSSQL\DATA\'
Set @TargetLogLocation = 'G:\Program Files\Microsoft SQL Server\MSSQL\LOG\'
-- DB NAMES WITH NO OPEN CONNECTIONS
SELECT DISTINCT d.name AS DbName, d.dbid AS DbID INTO #MyDbs
FROM sysdatabases d LEFT OUTER JOIN
sysprocesses p ON d.dbid = p.dbid
WHERE (d.name NOT IN ('master', 'msdb', 'tempdb', 'model', 'distribution')) and
d.Name Not in(SELECT DISTINCT RTRIM(d.[name]) AS dbname --NO OPEN CONNECTIONS
FROM
master.dbo.sysdatabases d INNER JOIN
master.dbo.sysprocesses p ON d.dbid = p.dbid)

While Exists(Select * from #MyDbs)
BEGIN
Select Top 1 @DbName = DbName from #MyDbs
Delete from #MyDbs Where DbName = @DbName

-- FILE NAMES
Set @iFile = 0
Set @cmd =
'Select Rtrim(Name) as FileName, Rtrim(filename) as SourceFile, ''usage'' = (case status & 0x40 when 0x40 then ''log only'' else ''data only'' end) INTO MyFiles from ' + @DbName +'..sysfiles'
--PRINT '-- GENERATE FILE INFORMATION '
--PRINT @cmd
exec sp_executesql @cmd

Select @FileCount = Count(*) from MyFiles
--Print @FileCount
If @FileCount = 2
Begin
--FILE NAMES AND PATH
Select Top 1 @DbMdfFile = SourceFile From MyFiles Where usage = 'data only'
Select Top 1 @LogLdfFile = SourceFile From MyFiles Where usage <> 'data only'
--Print '-- @DbMdfFile = ' + @DbMdfFile
--Print '-- @LogLdfFile = ' + @LogLdfFile

--PARCE FILE FROM FULL PATH
While @iFile < 2
BEGIN
IF @iFile = 0
Begin
Set @FileFrom = @DbMdfFile -- DATA FILE .MDF
--Print 'DATA FILE .MDF ' + @FileFrom
End
ELSE
Begin
Set @FileFrom = @LogLdfFile -- LOG FILE .LDF
--Print 'LOG FILE .LDF ' + @FileFrom
End

Set @StringLength = Len(@FileFrom)
Set @iX = 1

--Print '@FileFrom = ' + @FileFrom + ' @StringLength = ' + Cast(@StringLength as VarChar(10)) + ' @iX = ' +Cast(@iX as VarChar(10))
While @iX < @StringLength
Begin

SELECT @MyChar = SUBSTRING(@FileFrom, @iX, 1)
If @MyChar = '\'
Begin
Set @iL = @iX + 1
--Print @iL
End
Set @iX = @iX + 1
--Print ' @iX = ' +Cast(@iX as VarChar(10))
End -- @StringLength
Set @StringLength = 0

IF @iFile = 0
Begin
Set @FilePrimary = @TargetDbLocation + SUBSTRING(@FileFrom, @iL, Len(@FileFrom)) -- DATA FILE .MDF
--Print '@FilePrimary ' + @FilePrimary
End
ELSE
Begin
Set @FileLog = @TargetLogLocation + SUBSTRING(@FileFrom, @iL, Len(@FileFrom)) -- LOG FILE .MDF
--Print '@FileLog ' + @FileLog
End

Set @iFile = @iFile + 1
END --While @iFile

Set @sValidSource = Upper(Rtrim(@DbMdfFile))
Set @sValidTarget = Upper(Rtrim(@TargetDbLocation + @DbName + '.mdf'))
--Print '--@sValidSource ' + @sValidSource
--Print '--@sValidTarget ' + @sValidTarget
--if @sValidSource <> @sValidTarget --COPY
--Begin
Print '-- *********************************************************************************** '
--PRINT Upper(Rtrim(@DbMdfFile)) + ' ' + Upper(Rtrim(@TargetDbLocation + @DbName + '.mdf'))
--DETACH DATABASE
Print ''
Print '-- DETACH DATABASE'
Set @cmd = 'EXEC sp_detach_db ''' + @DbName + ''', ''true'''
Print @cmd
If @Execute = 1
Begin
Exec(@cmd)
End

--COPY DATA FILE
If @RelocateDATAFiles = 1
Begin
Print ''
Print '-- COPY DATA FILES '
Set @xcopy = 'EXEC master.dbo.xp_cmdshell ''xcopy /Y /D "' + @DbMdfFile + '" "' + @TargetDbLocation + '"'''
Print @xcopy
If @Execute = 1
Begin
Exec(@cmd)
End
End

--COPY LOG FILE
If @RelocateLOGFiles = 1
Begin
Print ''
Print '-- COPY LOG FILES '
Set @xcopy = 'EXEC master.dbo.xp_cmdshell ''xcopy /Y /D "' + @LogLdfFile + '" "' + @TargetLogLocation + '"'''
Print @xcopy
If @Execute = 1
Begin
Exec(@cmd)
End
End --Copy

-- ATTACH DATABASE
Print ' '
Print '-- ATTACH DATABASE'
Set @cmd = 'EXEC sp_attach_db @dbname = N''' + @DbName + ''',
@filename1 = N''' + @FilePrimary + ''',
@filename2 = N''' + @FileLog + ''''
Print @cmd
If @Execute = 1
Begin
Exec(@cmd)
End

--Print '--CONFIRM NEW FILE LOCATION '
--EXEC('Select Rtrim(Name) as FileName, Rtrim(filename) as SourceFile, ''usage'' = (case status & 0x40 when 0x40 then ''log only'' else ''data only'' end) from ' + @DbName +'..sysfiles')

Print ' '
Print ' '
Print ' '
--End -- MOVE DB

--Set @xcopy = ' ''xcopy ''' + @FileFrom + '' '' + @FilePrimary
--Print @iFile
End -- @iFile

Drop Table MyFiles

END --While DBS

Drop Table #MyDbs

/*
--VALIDATE MOVE
Declare @db as sysname, @UnionCmd as NVarChar(4000)
Set NoCount On
Set @UnionCmd = ''
Select [Name] as Nm INTO #MyDatabases from sysdatabases Where [Name] Not in ('master', 'msdb', 'tempdb', 'model', 'distribution') --= 'MBA'

While Exists(Select * from #MyDatabases)
BEGIN
Select Top 1 @db = Nm from #MyDatabases
Delete from #MyDatabases Where Nm = @db

If Len(@UnionCmd) > 1
Begin
Set @UnionCmd = @UnionCmd +
'
UNION
'
End

Set @UnionCmd = @UnionCmd +
'Select filename from ' + @db +'..sysfiles '
End
Drop Table #MyDatabases
Print @UnionCmd
exec sp_executesql @UnionCmd
*/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-29 : 13:24:14

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k

Tara

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-11-29 : 16:34:10

Documentation... I'll note it in my procedure thanks

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-11-30 : 08:03:52

Clark,

1) Naturally, you are going to backup the databases before doing this

.

2) You might want to error check before moving onto next steps - an example (bold code is new):



declare @error int
declare @ErrorPos varchar(50)

Delete from #MyDbs Where DbName = @DbName
select @error = @@error
if @Error <>0
 begin
 set @errorpos = 'delete DB from list'
 goto ErrorExitPoint
end

....your other code

ErrorExitPoint:
Print 'Error occurred at step: '+@ErrorPos+'!  Review and ensure resolution'.

What if a DB has more than 2 file's?


Select @FileCount = Count(*) from MyFiles
--Print @FileCount
If @FileCount = 2

3) Comment: It may be more readable if you continued consistently with the practice you use in some places of commenting the "begin" and "end" ... was trying to find the "end" for the "begin" from the "if @FileCount = 2" ... wasn't too easy (especially since you couldn't indent the code

4) Comment: you might want to add /V (verify) for the XCopy command to the production version... I am always rather safe than sorry in production. I might even go so far as to do something like a xp_cmdshell dir of the specific files to ensure that they are in the location, before the attaches.

5) I would think of having some kind of success indicator's, and only on confirmed success, progress to the next step. Having said that, I haven't (yet) found a method to get a successful execution return code from an XP_Cmdshell, hence my "checking" of file copy comment above, before attempting to progress.

Good Luck in production!

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-11-30 : 23:05:21

Thank you for the input...
I wanted to handle multiple files but I only have one db that has 4 files... this db is rather large and I found that my partitions for the new schema would not handle the size of the files all on one drive. It may have been split for performance reasons...

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-12-01 : 05:02:30

Clive,

Do you know if the files are split for things like indexes. At my previous site we planned and looked into have indexes in their own file group, on their own drive, to enhance performance. Unfortunately, I left before it went to production (it was quite a big exercise, since it entails moving 1000+ indexes on 700+ tables to a new filegroup). My opinion is that this is a good thing, if you have the hardware to support it. The same could hold true for "hot spot" table, imho.

Handling multiple files might not otherwise be a requirement right now, but it is always nice, when possible, to design for that capability, especially if, in an emeregency, you have to add another log file to a production database, on another drive, because the drive that the DB and log was on ran out of space (a text file grew from 1MB to 5GB in one night - damned report writer's).

Anyway, let me know if you find anyway to get a decent return code from the XP_CMDSHELL

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2006-03-17 : 12:58:49

"I haven't (yet) found a method to get a successful execution return code from an XP_Cmdshell"

IF ERRORLEVEL 1 ECHO ***FAILED***

might be something you could "catch" from XP_Cmdshell

Kristen

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-17 : 15:00:38

wow - blast from the past!

Almost a year and a half ago!

I'll see if that works - always useful to have these little tchniques tucked away. What made you respond to this one, Kristen?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-17 : 17:12:32

EXEC @result = master.dbo.xp_cmdshell '...'

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-03-19 : 02:15:45

"What made you respond to this one, Kristen?"

Elwoos suggested I add it to the FAQ ... I read it, responded, and have NOW seen the date!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources