Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Why don't you just take a backup? –  Aaron Bertrand Jan 13 '14 at 1:27
    
@AaronBertrand I think this question is related to SQL Server 2012: Generate Scripts from command line. Harald - Do you have correct version of SMO installed - C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\ directory. Check Installing SMO –  Kin Jan 13 '14 at 2:12
    
The smo.dll in that folder has version 11.0.3000.0. I believe that is the correct version. –  Harald Jan 13 '14 at 2:16
    
@Harald - Try using EnumScript method. ScriptTransfer is removed in sql 2012 as it was announced deprecated in sql 2008 R2. Check BOL –  Kin Jan 13 '14 at 2:17
1  
@Kin okay, but I think my question is still valid. The output of such a script as a .sql file may end up being far too large for tools like SSMS to handle... –  Aaron Bertrand Jan 13 '14 at 2:18

1 Answer 1

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

As @Kin points out, 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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