Hi all,

I want to automate generate scripts in SSMS per database. Usually i follow right click on database, tasks->generate scripts Which takes time. So i explored in google,got some powershell scripts. since i am new to it. Kindly suggest me to do enhance the same.

Ref:

https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-an

Hi Visakh16,

I never worked with powershell, just modified below and ran it as bat file but no luck

$DirectoryToSaveTo='G:\AutomateSB\' # local directory to save build-scripts to
$servername='*****' # server name and instance
$Database='P' # the database to copy from 

do i need follow any other steps?

Hi Visakh16,

I never worked with powershell, just modified below and ran it as bat file but no luck

$DirectoryToSaveTo='G:\AutomateSB\' # local directory to save build-scripts to
$servername='*****' # server name and instance
$Database='P' # the database to copy from 

do i need follow any other

i posted what i have changed in that script. moreover i included that script too while running a

no. I just made the following script as bat file and ran it.

$Filepath='G:\AutomateSB' # local directory to save build-scripts to
$DataSource='Myservername' # server name and instance
$Database='Mydatabase'# the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database] 
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$transfer.Options.ScriptBatchTerminator = $true # this only goes to the file
$transfer.Options.ToFileOnly = $true # this only goes to the file
$transfer.Options.Filename = "$($FilePath)\$($Database)_Build.sql"; 
$transfer.ScriptTransfer() 
"All done"

Is this correct?

Nope. What is given above is a powershell script

For running it from a bat you need to firstt save it as powershell script (*.ps1) and then call it from bat file as below

http://blog.danskingdom.com/allow-others-to-run-your-powershell-scripts-from-a-batch-file-they-will-love-you-for-it/

Hi Visakh,

I tried through Powershell but it throws following

File G:\PS\Automate.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.
At line:0 char:0

I made "Set-ExecutionPolicy RemoteSigned" but still same error

http://stackoverflow.com/questions/4037939/powershell-says-execution-of-scripts-is-disabled-on-this-system

attached image fyr

Hi Visakh,

I tried through Powershell but it throws following

File G:\PS\Automate.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.
At line:0 char:0

I made "Set-ExecutionPolicy RemoteSigned" but still same error

http://stackoverflow.com/questions/4037939/powershell-says-execution-of-scripts-is-disabled-on-this-