Generate Full Script from server to my local system automatic

  • 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..

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply