January 23, 2012 at 1:54 pm
create a c:\servers.txt file with list of servers
and copy the following into a file .ps1
enjoy
function getwmiinfo ($svr) {
gwmi -query "select * from
Win32_ComputerSystem" -computername $svr | select Name,
Model, Manufacturer, Description, DNSHostName,
Domain, DomainRole, PartOfDomain, NumberOfProcessors,
SystemType, TotalPhysicalMemory, UserName,
Workgroup | export-csv -path .\$svr\BOX_ComputerSystem.csv -noType
gwmi -query "select * from
Win32_OperatingSystem" -computername $svr | select Name,
Version, FreePhysicalMemory, OSLanguage, OSProductSuite,
OSType, ServicePackMajorVersion, ServicePackMinorVersion |
export-csv -path .\$svr\BOX_OperatingSystem.csv -noType
gwmi -query "select * from
Win32_PhysicalMemory" -computername $svr | select Name,
Capacity, DeviceLocator, Tag |
export-csv -path .\$svr\BOX_PhysicalMemory.csv -noType
gwmi -query "select * from Win32_LogicalDisk
where DriveType=3" -computername $svr | select Name, FreeSpace,
Size | export-csv -path .\$svr\BOX_LogicalDisk.csv –noType
}
function get-databasescripts {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Databases | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "Databases.sql")
}
function get-backupdevices {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.BackupDevices | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "BackupDevices.sql")
}
function get-triggers {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Triggers | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "Triggers.sql")
}
function get-endpointscripts {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.EndPoints | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "EndPoints.sql")
}
function get-errorlogs {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.ReadErrorLog() | export-csv -path $($directoryname + "Box_errorlogs.csv") -noType
}
function get-sqlagentscript {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.JobServer | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "sqlagentscript.sql")
}
function get-jobscripts {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.JobServer.Jobs | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "jobs.sql")
}
function get-linkscripts {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.LinkedServers | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "linkedservers.sql")
}
function get-userlogins {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Logins | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "logins.sql")
}
function get-roles {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Roles | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "roles.sql")
}
function get-alerts {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.JobServer.Alerts | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "alerts.sql")
}
function get-operators {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.JobServer.Operators | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "operators.sql")
}
$servers = get-content c:\servers.txt
foreach ($server in $servers){
if (!(Test-Path -path .\$server)) {
New-Item .\$server\ -type directory
}
$directoryname = "c:\downloads\DRTest\" + '\' + $server + '\'
$sqlserver = $server
$serverfilename = $server
getwmiinfo $server
get-databasescripts
get-errorlogs
get-triggers
get-backupdevices
get-endpointscripts
get-sqlagentscript
get-jobscripts
get-linkscripts
get-userlogins
get-operators
get-alerts
}
January 23, 2012 at 2:21 pm
thanks for posting, this will get a wider audience if you submit it to the scripts section.
---------------------------------------------------------------------
January 26, 2012 at 6:20 am
OK I have the file created and the servers.txt file created with 1 server name in it, but it doesn't seem to do anything. Any ideas
January 26, 2012 at 7:13 am
in the servers.txt file you can not have any spaces or it wont read properly, try setting the servername manually and see if that runs.
January 26, 2012 at 7:14 am
It works fine now. Thx
Can you tell me how to take this function that you have and put the linked servers into seperate files instead of all in one file?
December 9, 2013 at 4:45 pm
Thanks! This script has been very helpful in getting us started in automating our server configurations for DR scenarios. The one object we have not been able to figure out is Extended Events. Any ideas on how to script those with Powershell? I have searched high and low and have not been able to figure out how to do it.
January 15, 2015 at 7:31 am
I know this is an older post, but just wanted to say thanks and great script. You helped me out big time!
July 23, 2015 at 12:25 pm
Great One.
Thanks.
August 19, 2015 at 6:49 am
Hi - this script is awesome!!
I had written similar kind of script to script some objects. however recently I am facing issue while running it on SQL server 2012.
It is not generating desired scripts of db objects. Do you see any issue. of assemblies or anything else.
If i run that in powershell session it runs very fine but problem is from SQL Server.
March 2, 2016 at 2:05 pm
thanks help me a lot! have a nice day 😀
March 30, 2016 at 9:55 pm
December 13, 2016 at 12:34 pm
hi
im new to PS.. I have created server.txt with single server and saved file
also created below folder structure 'C:\downloads\DRTest
PS D:\ps1> .\Scriptout.ps1
Directory: D:\ps1
Mode LastWriteTime Length Name
---- ------------- ------ ----
d---- 12/14/2016 1:29 AM servername
Out-File : Could not find a part of the path 'C:\downloads\DRTest\servername\servernameDatabases.sql'.
February 21, 2017 at 9:13 am
I'm having some trouble with this running it against a SQL2016 server. just limiting it to one specific issue for now, scripting the definitions for the databases themselves, and not the objects inside them.
i wrapped the script below to now have a try-catch so i could bubble up the details of the error.
When i run it now, i get a pair of errors that seem to alternate; casting DbNull to string, and another saying a column already exists.
can anyone see an issue with what i'm doing? it worked file previously on a 2008R2 server, so it's somethign different in SMO v13 i'm thinking:
no errors when running due to the try catch:
GAC Version Location
--- ------- --------
True v2.0.50727 C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.S...
VERBOSE: directory C:\Data\DatabaseSchemas\SFCCNSQLSERV01\
VERBOSE: scripting SFCCNSQLSERV01
the error in the file for duplicate column:Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Database 'Sandbox'. ---> System.Data.DuplicateNameException: A column named 'DatabaseName2' already belongs to this DataTable.
at System.Data.DataColumnCollection.RegisterColumnName(String name, DataColumn column)
at System.Data.DataColumnCollection.BaseAdd(DataColumn column)
at System.Data.DataColumnCollection.AddAt(Int32 index, DataColumn column)
at Microsoft.SqlServer.Management.Smo.DataProvider.InitSchemaTable(ArrayList parentProperties)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.get_Count()
at Microsoft.SqlServer.Management.Smo.FileGroup.ScriptPrimaryFileGroup(ScriptingPreferences sp, StringBuilder ddl, Boolean databaseIsView)
at Microsoft.SqlServer.Management.Smo.FileGroup.ScriptDdl(ScriptingPreferences sp, StringBuilder ddl, Boolean databaseIsView)
at Microsoft.SqlServer.Management.Smo.Database.GetFileGroupsScript(StringBuilder query, Boolean databaseIsView, StringCollection emptyfgs, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Database.ScriptCreate(StringCollection createQuery, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.EnumScriptImplWorker(ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.EnumScriptImpl(ScriptingPreferences sp)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.EnumScriptImpl(ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptImpl(ScriptingPreferences sp)
at CallSite.Target(Closure , CallSite , Object )
the error in the file due to invalid cast:Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Database 'PRODUCTION'. ---> System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetDbCollation(String dbname)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetDbComparer(Boolean inServer)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitializeStringComparer()
at Microsoft.SqlServer.Management.Smo.AbstractCollectionBase.get_StringComparer()
at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.InitInnerCollection()
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.get_Count()
at Microsoft.SqlServer.Management.Smo.Database.ScriptCreate(StringCollection createQuery, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.EnumScriptImplWorker(ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.EnumScriptImpl(ScriptingPreferences sp)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.EnumScriptImpl(ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptImpl(ScriptingPreferences sp)
at CallSite.Target(Closure , CallSite , Object )
The script:#Create Database Scripts with all properties
function get-databasescripts {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
#$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Databases | foreach {
try{
$_.Script() + "GO"
}
catch [Exception] {$_.Exception.ToString() }
} | Out-File $($directoryname + $serverfilename + "_Databases.sql") -Append
}
###################################################################################################
# Core Workspace
###################################################################################################
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
#$servers = get-content C:\Data\PowerShell\computers.txt
##$servers = get-content C:\Data\PowerShell\computers.txt # from a file
#$servers = @("SQLAlpha","SQLBeta","SQLDelta","SQLOmega") # multiple for testing
$servers = @("SQLAlpha") # just one for testing
##$basedirectory = "C:\_TFS\CCPMain\Main\DatabaseSchemas"
$basedirectory = "C:\Data\DatabaseSchemas"
$directoryname = $basedirectory
#make sure our prime directory exists
if (!(Test-Path -path $directoryname)) {
New-Item $directoryname -type directory
}
foreach ($sqlserver in $servers){
if ($sqlserver.Trim() -ne "") {
if($sqlserver.IndexOf("\") -gt 0) {
$directoryname = $basedirectory + "\" + $sqlserver.Replace("\","(") + ")\"
$position =$sqlserver.IndexOf("\")
$server = $sqlserver.SubString(0,$position)
$serverfilename = $sqlserver.Replace("\","(") + ")"
}
else {
$directoryname = $basedirectory + "\" + $sqlserver + "\"
$server = $sqlserver
$serverfilename = $server
}
if (!(Test-Path -path $directoryname)) {
New-Item $directoryname -type directory
}
$database = ""
write-verbose -Message "directory $directoryname" -verbose
write-verbose -Message "scripting $server" -verbose
getwmiinfo $server
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
if($svr.NetName -ne '') {
$srv.SetDefaultInitFields($true)
get-databasescripts
#get-errorlogs
#get-triggers
#get-backupdevices
#get-sqlagentscript
#get-jobscripts
#get-linkscripts
#get-userlogins
#get-operators
#get-alerts
#get-databaseobjectscripts
#get-singlescriptdatabaseobjects
#$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ($server)
#$Scripter.Options.DriAll=$False
#$Scripter.Options.IncludeHeaders=$False
#$Scripter.Options.ToFileOnly=$True
#$Scripter.Options.WithDependencies=$False
#foreach $database in $Scripter.Databases
#{
##create a directory for each database.
#if (!(Test-Path -path $directoryname\$server\$database)) {
#New-Item $directoryname\$server\$database -type directory
#}
#}
} #-ne $null
} #-ne ""
} #foreach
Lowell
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply