April 23, 2008 at 6:01 am
I'm trying to export setting using the SQL Server Surface Area Configuration (sac) tool can someone help me. I'm trying to create a matrix of all default setting and can't figure out how to export all the settings
April 23, 2008 at 6:24 am
The following will list all the settings, works in 2000 and 2005
SET NOCOUNT ON
DECLARE @DatabaseName sysname, @Servername varchar(Max), @Counter int
SET @DatabaseName = db_name()
SET @Servername = @@Servername
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#temp]'))
DROP TABLE #temp
CREATE TABLE #Temp
(ID Int IDENTITY (1,1), DatabaseName VARCHAR (250))
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#database]'))
DROP TABLE #database
CREATE TABLE #database
(Server Varchar (50)
,DBName Varchar (150)
,Owner Varchar (150)
,crdate Varchar (150)
,LicenseType Varchar (150)
,cmptlevel Varchar (150)
,Collation Varchar (150)
,UserAccess Varchar (150)
,Status Varchar (150)
,Recovery Varchar (150)
,Instance Varchar (150)
,CurrentDateTime Varchar (150)
,UserName Varchar (150)
,NumberOfConnections Varchar (150)
,Language Varchar (150)
,LanguageId Varchar (150)
,LockTimeout Varchar (150)
,MaximumOfConnections Varchar (150)
,CPUBusy Varchar (150)
,CPUIdle Varchar (150)
,IOBusy Varchar (150)
,PacketsReceived Varchar (150)
,PacketsSent Varchar (150)
,PacketsErrors Varchar (150)
,TimeTicks Varchar (150)
,IOErrors Varchar (150)
,TotalRead Varchar (150)
,TotalWrite Varchar (150)
)
IF SUBSTRING(@@version,23,4)='2000'
BEGIN
SET @Counter = 0
INSERT INTO #Temp
SELECT [name] AS DatabaseName
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('AdventureWorks', 'Pubs', 'Northwind')
WHILE @Counter <= (Select Count(*) FROM #Temp)
BEGIN
SET @DatabaseName = (SELECT DatabaseName
from #Temp
WHERE ID = @Counter)
INSERT INTO #database
select @Servername as 'Server'
,d.name as 'DBName'
, suser_sname(d.sid) as [Owner]
,d.crdate
,convert(varchar(150),serverproperty('LicenseType')) as LicenseType
,d.cmptlevel
,s.DEFAULT_CHARACTER_SET_NAME as [Collation]
,(SELECT convert(sysname,DatabasePropertyEx(@DatabaseName,'UserAccess'))) as [UserAccess]
,(SELECT convert(sysname,DatabasePropertyEx(@DatabaseName,'Status'))) as [Status]
,(SELECT convert(sysname,DatabasePropertyEx(@DatabaseName,'Recovery'))) as Recovery
,convert(varchar(30),@@SERVICENAME) as 'Instance'
,convert(varchar(30),getdate(),103) as 'CurrentDateTime'
,USER_NAME() as 'UserName'
,convert(varchar(30),@@connections) as 'NumberOfConnections'
,convert(varchar(30),@@language) as 'Language'
,convert(varchar(30),@@langid) as 'LanguageId'
,convert(varchar(30),@@LOCK_TIMEOUT) as 'LockTimeout'
,convert(varchar(30),@@MAX_CONNECTIONS) as 'MaximumOfConnections'
,convert(varchar(30),@@CPU_BUSY/1000) as 'CPUBusy'
,convert(varchar(30),@@IDLE/1000) as 'CPUIdle'
,convert(varchar(30),@@IO_BUSY/1000) as 'IOBusy'
,convert(varchar(30),@@PACK_RECEIVED) as 'PacketsReceived'
,convert(varchar(30),@@PACK_SENT) as 'PacketsSent'
,convert(varchar(30),@@PACKET_ERRORS) as 'PacketsErrors'
,convert(varchar(30),@@TIMETICKS) as 'TimeTicks'
,convert(varchar(30),@@TOTAL_ERRORS) as 'IOErrors'
,convert(varchar(30),@@TOTAL_READ) as 'TotalRead'
,convert(varchar(30),@@TOTAL_WRITE) as 'TotalWrite'
from master.dbo.sysdatabases d
inner join INFORMATION_SCHEMA.SCHEMATA s
on d.name =s.CATALOG_NAME
WHERE d.name =@DatabaseName
COLLATE DATABASE_DEFAULT
SET @Counter = @Counter + 1
END
END
ELSE --2005
BEGIN
INSERT INTO #Temp
SELECT [name] AS DatabaseName
FROM master.sys.sysdatabases
WHERE [name] NOT IN ('AdventureWorks', 'Pubs', 'Northwind')
SET @Counter = 0
WHILE @Counter <= (Select Count(*) FROM #Temp)
BEGIN
SET @DatabaseName = (SELECT DatabaseName
from #Temp
WHERE ID = @Counter)
INSERT INTO #database
select @@Servername as 'Server'
,[name] as DBName
, suser_sname(d.owner_sid) as [Owner]
,d.create_date as [crdate]
,convert(varchar(150),serverproperty('LicenseType')) as LicenseType
,d.compatibility_level as [cmptlevel]
,d.collation_name as [Collation]
,d.user_access_desc as [UserAccess]
,d.state_desc as [Status]
,d.recovery_model_desc as [Recovery]
,convert(varchar(50),@@SERVICENAME) as 'Instance'
,convert(varchar(30),getdate(),103) as 'CurrentDateTime'
,USER_NAME() as 'UserName'
,convert(varchar(30),@@connections) as 'NumberOfConnections'
,convert(varchar(30),@@language) as 'Language'
,convert(varchar(30),@@langid) as 'LanguageId'
,convert(varchar(30),@@LOCK_TIMEOUT) as 'LockTimeout'
,convert(varchar(30),@@MAX_CONNECTIONS) as 'MaximumOfConnections'
,convert(varchar(30),@@CPU_BUSY/1000) as 'CPUBusy'
,convert(varchar(30),@@IDLE/1000) as 'CPUIdle'
,convert(varchar(30),@@IO_BUSY/1000) as 'IOBusy'
,convert(varchar(30),@@PACK_RECEIVED) as 'PacketsReceived'
,convert(varchar(30),@@PACK_SENT) as 'PacketsSent'
,convert(varchar(30),@@PACKET_ERRORS) as 'PacketsErrors'
,convert(varchar(30),@@TIMETICKS) as 'TimeTicks'
,convert(varchar(30),@@TOTAL_ERRORS) as 'IOErrors'
,convert(varchar(30),@@TOTAL_READ) as 'TotalRead'
,convert(varchar(30),@@TOTAL_WRITE) as 'TotalWrite'
from sys.databases d
WHERE name =@DatabaseName COLLATE DATABASE_DEFAULT
SET @Counter = @Counter + 1
END
END
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#Results]'))
DROP TABLE #Results
CREATE TABLE #Results (SERVER VARCHAR(200), DatabaseName VARCHAR(200), NAME VARCHAR(200), filename VARCHAR(300))
EXEC sp_MSForEachDB
'INSERT INTO #Results(Server, DatabaseName, Name, filename)
SELECT CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS Server,
''?'' as DatabaseName,
[?]..sysfiles.name,
[?]..sysfiles.filename
From [?]..sysfiles'
SELECT
d.Server
,d.DBName
,d.Owner
,d.crdate
,d.LicenseType
,d.cmptlevel
,d.Collation
,d.UserAccess
,d.Status
,d.Recovery
,d.Instance
,d.CurrentDateTime
,d.UserName
,d.NumberOfConnections
,d.Language
,d.LanguageId
,d.LockTimeout
,d.MaximumOfConnections
,d.CPUBusy
,d.CPUIdle
,d.IOBusy
,d.PacketsReceived
,d.PacketsSent
,d.PacketsErrors
,d.TimeTicks
,d.IOErrors
,d.TotalRead
,d.TotalWrite
,r.Name as FileName
,r.FileName as FullPath
FROM #database d
INNER JOIN #Results r ON
d.Server = r.Server
AND d.DBName = r.DatabaseName
April 23, 2008 at 8:30 am
Is there a easier way using the sac tool. I'm a newbie. I read that you can export the settings and import them into another server like a template.
April 24, 2008 at 2:25 am
There is a command line version of the Surface Area Config Tool, sac.exe
It's documented in Books Online. It allows you to export your settings to a flat file and the import them on another server using a variation of the same command.
No messy SQL 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply