Must be an easier way (move logs)
-
Enterprise SQL Server, 8 instances installed.
Boss wants me to move 64 log files to another drive on the server
Any suggestions?
-
I'd probably go the detach, copy, attach route, but it all depends doesn't it?
edit: and you can script all this using tsql...
-
Script section?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I went through this with 20 DBs a few weeks ago. The following script is adapted from attach all the datababses.
if exists (SELECT * from dbo.sysobjects where id = object_id(N'[tempsysfiles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP table [tempsysfiles]
END
if NOT exists (SELECT * from dbo.sysobjects where id = object_id(N'[tempsysfiles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [tempsysfiles] (
[DBID] [INT],
[fileid] [smallint] NULL ,
[groupid] [smallint] NULL ,
[int] NULL ,
[maxsize] [int] NULL ,
[growth] [int] NULL ,
[status] [int] NULL ,
[perf] [int] NULL ,
[name] [nchar] (128) ,
[filename] [nchar] (260)
) ON [PRIMARY]
END
DECLARE @dbid int
DECLARE TabCursor CURSOR FOR SELECT dbid from sysdatabases where dbid > 4
ORDER BY name
OPEN TabCursor
FETCH NEXT FROM TabCursor into @dbid
WHILE @@fetch_status = 0
BEGIN
DECLARE @Sql varchar(500)
SELECT @Sql = ( 'SELECT ' + CAST(@dbid AS VARCHAR) + ' AS DBID, * from ' + db_name(@dbid) + '.DBO.SysFiles')
INSERT tempsysfiles
EXEC (@Sql)
FETCH NEXT FROM TabCursor into @dbid
END
CLOSE TabCursor
DEALLOCATE TabCursor
--SELECT * FROM tempsysfiles
--DECLARE @dbid int
DECLARE curname cursor for SELECT dbid from sysdatabases where dbid > 4
ORDER BY name
open curname
fetch next from curname into @dbid
while @@fetch_status = 0
begin
--DECLARE @Sql varchar(500)
DECLARE @datafile varchar(100)
DECLARE @logfile varchar(100)
--SELECT @Sql = ('SELECT filename from ' + db_name(@dbid) + '.DBO.SysFiles where groupid = 1')
SELECT @datafile = filename from tempsysfiles where groupid = 1 AND DBID = @dbid
SELECT @logfile = filename from tempsysfiles where groupid = 0 AND DBID = @dbid
SELECT @Sql = ('SELECT filename from ' + db_name(@dbid) + '.DBO.SysFiles where groupid = 0')
--PRINT 'DATAFILE NAME: ' + @datafile
--print 'LOG FILE NAME: ' + @logfile
You'll have to play around in here to adjust for drives and paths. And this script only accounts for mdf and ldf, no ndf's.
IF LEFT(ltrim(rtrim(@logfile)),1) = 'D' OR LEFT(ltrim(rtrim(@datafile)),1) = 'D'
BEGIN
PRINT '-- NEXT DATABASE: ' + + db_name(@dbid) + '--------------------------------- '
set @Sql = 'ALTER DATABASE ' + db_name(@dbid) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' + CHAR(13) + 'GO'
PRINT @Sql
set @Sql = 'sp_detach_db ' + CHAR(39) + db_name(@dbid) + CHAR(39) + ', ' + CHAR(39) + 'True' + CHAR(39) + CHAR(13) + 'GO'
PRINT @Sql
IF LEFT(ltrim(rtrim(@logfile)),1) = 'D'
BEGIN
SET @Sql ='XP_CMDSHELL ' + CHAR(39) + 'XCOPY ' + ltrim(rtrim(@logfile)) + ' ' + REPLACE(ltrim(rtrim(@logfile)),'D:\DATA\MSSQL\data\','E:\DATA\MSSQL\data\') + '* /Y' + CHAR(39) + CHAR(13) + 'GO'
PRINT @Sql
END
IF LEFT(ltrim(rtrim(@datafile)),1) = 'D'
BEGIN
SET @Sql ='XP_CMDSHELL ' + CHAR(39) + 'XCOPY ' + ltrim(rtrim(@datafile)) + ' ' + REPLACE(ltrim(rtrim(@datafile)),'D:\DATA\MSSQL\data\','E:\DATA\MSSQL\data\') + '* /Y' + CHAR(39) + CHAR(13) + 'GO'
PRINT @Sql
END
set @Sql = 'sp_attach_db ' + CHAR(39) + db_name(@dbid) + CHAR(39) + ',' + CHAR(39) + REPLACE(ltrim(rtrim(@datafile)),'D:\DATA\MSSQL\data\','E:\DATA\MSSQL\data\') + CHAR(39) + ',' + CHAR(39) + REPLACE(ltrim(rtrim(@logfile)),'D:\DATA\MSSQL\data\','E:\DATA\MSSQL\data\') + CHAR(39) + CHAR(13) + 'GO'
print @Sql
END
fetch next from curname into @dbid
end
close curname
deallocate curname
if exists (SELECT * from dbo.sysobjects where id = object_id(N'[tempsysfiles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP table [tempsysfiles]
END
Hope this helps.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Thank you Jim P.!!
That is a thing of beauty!!
-
It took me a day or two work through what sa24 posted, and somehow my sysaltfiles didn't agree with the sysfiiles.
But it made my life easier, and sounds like it did yours as well.
Glad you enjoyed it.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply