February 29, 2012 at 7:30 am
Hello
I am using Sql Server 2008 r2..
I know how to generate script of database with data manually...
But there is any way where i can generate database script in every 2 day ..
and store anywhere in my local system automatically.
i want to generate script from live server.
if there is any process where i can schedule this so please Guide me..
Thanks..
February 29, 2012 at 7:32 am
look in the powershell forum here on SSC;
there's a large number of posts showing how to script out a whole database; then with powershell you can schedule it to run at your convenience.
here's just one example i saved in my snippets:
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
}
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply