This script assumes the system database is named DYNAMICS.
It then handles DYNAMICS, all company databases, resco, report server, smartconnect, and nodebuilder.
--script to move all DYNAMICS databases
--set the new paths and growth rates before running
DECLARE @DATABASE_NAME VARCHAR(255),
@NEWLOGPATH VARCHAR(255),
@NEWDATAPATH VARCHAR(255),
@DATAGROWTHRATE VARCHAR(20),
@LOGGROWTHRATE VARCHAR(20)
SELECT @NEWLOGPATH='C:\SQL\LOG\',
@NEWDATAPATH='C:\SQL\DATA\',
@DATAGROWTHRATE='1024MB',
@LOGGROWTHRATE='512MB'
DECLARE @SQL VARCHAR(MAX),
@DATAFILELOGICAL VARCHAR(255),
@NEWDATAFILEPATH VARCHAR(255),
@OLDDATAFILEPATH VARCHAR(255),
@LOGFILELOGICAL VARCHAR(255),
@NEWLOGFILEPATH VARCHAR(255),
@OLDLOGFILEPATH VARCHAR(255)
DECLARE C_INTERID CURSOR FOR
SELECT RTRIM(INTERID)
FROM DYNAMICS.dbo.SY01500 D
INNER JOIN sys.sysdatabases S
ON D.INTERID=S.name
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME ='DYNAMICS'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'resco%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'ReportServer%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'SmartConnect%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'NodeBuilder%'
OPEN C_INTERID
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @DATAFILELOGICAL='[' + RTRIM(name) + ']',
@OLDDATAFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='ROWS'
SELECT @LOGFILELOGICAL='[' + RTRIM(name) + ']',
@OLDLOGFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='LOG'
SELECT @NEWDATAFILEPATH=@NEWDATAPATH + reverse(left(reverse(@OLDDATAFILEPATH),charindex('\',reverse(@OLDDATAFILEPATH), 1) - 1))
SELECT @NEWLOGFILEPATH=@NEWLOGPATH + reverse(left(reverse(@OLDLOGFILEPATH),charindex('\',reverse(@OLDLOGFILEPATH), 1) - 1))
--SELECT @DATAFILELOGICAL,@DATAFILEPATH,@LOGFILELOGICAL,@LOGFILEPATH
SET NOCOUNT ON
SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET offline
'
PRINT @SQL
EXEC(@SQL)
IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH OR @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC sp_configure ''show advanced options'', 1;
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
EXEC sp_configure ''xp_cmdshell'', 1;
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)
END
IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDDATAFILEPATH + '" "' + @NEWDATAFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', FILENAME = "' + @NEWDATAFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END
IF @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDLOGFILEPATH + '" "' + @NEWLOGFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', FILENAME = "' + @NEWLOGFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END
SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET online
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ',
FILEGROWTH = ' + @DATAGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ',
FILEGROWTH = ' + @LOGGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + ' SET AUTO_SHRINK OFF
'
PRINT @SQL
EXEC(@SQL)
SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME
END
CLOSE C_INTERID
DEALLOCATE C_INTERID
No comments:
Post a Comment