SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Auto dump out database schema


Auto dump out database schema

Author
Message
mr.neil.bryan
mr.neil.bryan
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 324
Comments posted to this topic are about the item Auto dump out database schema
bgrossnickle
bgrossnickle
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 344
What version Powershell do you need to have for TRY CATCH? I have version 1 and it would not run this.
JohnNash
JohnNash
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 93
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: (Smile [], ExtendedTypeSystemExceptio
n
+ FullyQualifiedErrorId : ExceptionInGetEnumerator
mr.neil.bryan
mr.neil.bryan
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 324
Hi,

I am using V2 of PowerShell...


============================================================================
C:\Users\7319>powershell.exe
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

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.neil.bryan
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 324
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
JohnNash
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 93
Yeah, Thanks. it's worked,we should change the server name.


Thanks for sharing.
omegadev21
omegadev21
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 20
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: (Smile [], 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
omegadev21
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 20
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search