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 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 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 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 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 at 2:18

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.