3

Using Sql Server 2012, could somebody be so kind and show me a simple powershell 3 script that generates my entire database (schema and data)?

I tried this one:

$Filepath='d:\b\t' # local directory to save build-scripts to
$DataSource='HH' # server name and instance
$Database='HMovies'# 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.ScriptData = $true;
$transfer.Options.Filename = "$($FilePath)\$($Database)_Build.sql";
$transfer.ScriptTransfer()
"All done"

But I get an error

Exception calling "ScriptTransfer" with "0" argument(s): "This method does not support scripting data."

If I disable the ScriptData option it works but then I get the schema only of course. I have googled for hours but I just can't find a simple script (no bells and whistles for stored procedures, etc) that performs this simple task.

My time is running out. Please help.

  • 2
    Why don't you just take a backup? – Aaron Bertrand Jan 13 '14 at 1:27
  • I checked out the backup option and found a powershell script that does the trick. Thanks to both of you. – Harald Jan 13 '14 at 2:46
5

I wanted to use Powershell to script a small database so that I can track changes in git. I found the same script you did to use as a starting point: Automated Script Generation with Powershell and SMO

You can find all of the options for the ScriptingOptions class here: ScriptingOptions Class

You're getting the error because of the now deprecated ScriptTransfer method.

Replace this line

$transfer.ScriptTransfer()

with this

$transfer.EnumScriptTransfer()

and your script should run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.