Author Posts

July 4, 2017 at 9:45 am

Hello experts!
I want to script my remote MS SQL database to local .sql files. I found correct powershell script which resolved my conception: link to GitHub). I have only one problem – the script works fine only on local machine with local credentials. To database can be connect both options username or local windows credentials.
As below is part of this script:

function GenerateDBScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
  $srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
  $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
  $db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
  $db = $srv.Databases[$dbname]
  $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
  $deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
...

Could you please help me to use script with username and password?

Thank's!

BR
Krzysztof

July 4, 2017 at 1:47 pm

Well, you've got a bit happening. For starters, LoadWithPartialName() is deprecated; you really should be using Add-Type to load assemblies. Other than that, this isn't really a PowerShell question – you're using "raw" .NET. I mention that only because a developer site like StackOverflow might get you a better answer, but I'll give it a shot.

The constructor you're using accepts a server name and assumes your logon credentials have access. An alternate constructor, https://msdn.microsoft.com/en-us/library/ms193784.aspx, accepts a connection object. So you'd need to first create one of those objects. That class' last constructor, https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.aspx, accepts a server, username, and password.