Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Poweshell script to script out jobs, databases, users, linked servers, logins, roles, alerts, etc from a list of servers Expand / Collapse
Author
Message
Posted Monday, January 23, 2012 1:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 24, 2013 6:42 AM
Points: 17, Visits: 180
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
}
Post #1240480
Posted Monday, January 23, 2012 2:21 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 5,849, Visits: 12,584
thanks for posting, this will get a wider audience if you submit it to the scripts section.

---------------------------------------------------------------------

Post #1240498
Posted Thursday, January 26, 2012 6:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:42 PM
Points: 6, Visits: 117
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


Post #1242114
Posted Thursday, January 26, 2012 7:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 24, 2013 6:42 AM
Points: 17, Visits: 180
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.
Post #1242148
Posted Thursday, January 26, 2012 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:42 PM
Points: 6, Visits: 117
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?



Post #1242152
Posted Monday, December 09, 2013 4:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:27 PM
Points: 71, Visits: 88
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.
Post #1521352
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse