Auto dump out database schema

Question

Post reply

Auto dump out database schema

mr.neil.bryan

Mr or Mrs. 500

Points: 531
More actions
January 22, 2014 at 1:11 pm

#282716

Comments posted to this topic are about the item Auto dump out database schema

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bgrossnickle Ten Centuries Points: 1332 More actions · Answer 1

What version Powershell do you need to have for TRY CATCH? I have version 1 and it would not run this.

JohnNash SSC Eights! Points: 894 More actions · Answer 2

Hi

I am trying to auto dump the database, as i was ended up with the below error, can you please advise on this.

The following exception was thrown when trying to enumerate the collection: "Fa

iled to connect to server localhost\sqlexpress.".

At C:\PowerShell\Output_Schema.ps1:123 char:10

+ foreach <<<< ($login in $server.Logins)

+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemExceptio

n

+ FullyQualifiedErrorId : ExceptionInGetEnumerator

mr.neil.bryan Mr or Mrs. 500 Points: 531 More actions · Answer 3

Hi,

I am using V2 of PowerShell...

============================================================================

C:\Users\7319>powershell.exe

Windows PowerShell

PS C:\Users\7319> $Host

Name : ConsoleHost

Version : 2.0

InstanceId : 0d10a998-5015-463a-9645-0d487d3b9f4b

UI : System.Management.Automation.Internal.Host.InternalHostUserI

nterface

CurrentCulture : en-GB

CurrentUICulture : en-US

PrivateData : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy

IsRunspacePushed : False

Runspace : System.Management.Automation.Runspaces.LocalRunspace

PS C:\Users\7319>

============================================================================

From doing some reading, the TRY CATCH was not integrated into version 1. Sorry, should have stated this in the notes. Poweshell 2.0 is available from http://support.microsoft.com/kb/968929.

Regards,

mr.neil.bryan Mr or Mrs. 500 Points: 531 More actions · Answer 4

Belts and braces, can you please check that you have the servername correct? I have just tried it with my localhost\sqlexpress and it works OK. Here is the command that I used...

Powershell.exe -File "C:\Output_Schema.ps1" - $SQLInstance localhost\sqlexpress - $Output_Folder_Name "\\uk.xxx.com\xx\xx\xxxxx\xxxx\xxxx\xx\xxxx\Reports\Schema Dumps"

I also tried it with using an invalid server name and got the error that you have reported.

Also check that you are using Powershell V2 or greater..

thanks for the interest.

Regards,

Neil

JohnNash SSC Eights! Points: 894 More actions · Answer 5

Yeah, Thanks. it's worked,we should change the server name.

Thanks for sharing.

omegadev21 Old Hand Points: 335 More actions · Answer 6

Great script. Have a quick question, I'm trying to use this script to generate schema dumps of a remote database instance. I do not have and it will take too long to get admin rights to allow executing powershell scripts on the machine where the databases lie. I can connect to the database server just fine from my remote machine, and I tried modifying your script to allow using mixed-mode authentication, but I keep getting the following error:

The following exception was thrown when trying to enumerate the collection: "Failed to connect to server 192.168.45.91\DYNSQLEX.".

At F:\Projects\paxomas_wlds\branches\Projects\Scripts\powershell\auto-dump.ps1:123 char:10

+ foreach <<<< ($login in $server.Logins)

+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException

+ FullyQualifiedErrorId : ExceptionInGetEnumerator

The lines I added:

#This sets the connection to mixed-mode authentication

$server.ConnectionContext.LoginSecure=$false;

#This sets the login username

$server.ConnectionContext.set_Login("dynamic_gwy")

#This sets the login password

$server.ConnectionContext.set_Password("VchK6Yd7V6YPN#k")

This is my first attempt at using PowerShell. What if anything am I missing, and does this script generate insert statements for data for all the tables in all the databases?

omegadev21 Old Hand Points: 335 More actions · Answer 7

Nevermind, I figured out what the issue was. However, once I got it working, I realized, that this script does not create insert statements for the data. Can someone point me in the right direction on how I can achieve this by tweaking the script?