December 7, 2014 at 9:58 pm
Comments posted to this topic are about the item Scripting SQL Server databases with SMO using EnforceScriptingOptions
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 8, 2014 at 12:56 am
Apparently the link to the script disappeared.
So here is the link to my script at OneDrive
Scripting SQL Server databases with SMO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 8, 2014 at 2:16 am
I'd always wondered what that script option did. I'm still not really sure, even after you've so kindly explained it. Does it mean' over-ride all the settings you've laboriously made and enforce the default settings? (Why would you need to do that?) The descriptions on MSDN for all these switches seem to have been automatically generated. I can't see that a human was involved. If humans did write them, I'd hate to meet them. I've spent many hours doing much as you have done, experimenting and trying to puzzle out the scripting of SMO.
Best wishes,
Phil Factor
December 8, 2014 at 2:35 am
My shrink already told me I wouldn't be the only one puzzled with such documentation.
Thank you for the confirmation 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 8, 2014 at 7:09 am
Thanks for the info. Do you know if a message is outputted when an encrypted object is encountered? I currently check for IsEncrypted and write a message to the output file.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
December 8, 2014 at 7:20 am
MG-148046 (12/8/2014)
Thanks for the info. Do you know if a message is outputted when an encrypted object is encountered? I currently check for IsEncrypted and write a message to the output file.
To overcome it reporting an error ( and stopping the scripting process ) due to an encrypted object, I use $CreationScriptOptions.ContinueScriptingOnError = $true
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 8, 2014 at 10:00 am
If I remember rightly, I believe that there is an error message event in SMO that you can attach a listener to in order to get scripting errors.
Best wishes,
Phil Factor
December 8, 2014 at 11:46 am
Thank you both, I'll look into it.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
December 8, 2014 at 1:54 pm
Thanks for the article. This is something I've been meaning to do for awhile, but have been too wrapped up in other work. Now if someone could show me a script to BCP out and BCP in my data in proper constraint order, aka parent-child order, then my migration plan would be set.;-) I have corruption in my SQL 2005 production database that I am migrating to a new SQL 2012 environment. Scripting both schema objects and data out and back in again using PowerShell, would help me tremendously.
Cheers! 🙂
December 9, 2014 at 12:32 am
Brandon Forest (12/8/2014)
Thanks for the article. This is something I've been meaning to do for awhile, but have been too wrapped up in other work. Now if someone could show me a script to BCP out and BCP in my data in proper constraint order, aka parent-child order, then my migration plan would be set.;-) I have corruption in my SQL 2005 production database that I am migrating to a new SQL 2012 environment. Scripting both schema objects and data out and back in again using PowerShell, would help me tremendously.Cheers! 🙂
Hi Brandon,
Best is to start a new thread with your question on bcp recovering your db.
Chances are this SMO object can get you started
$depWalker = New-Object ('Microsoft.SqlServer.Management.Smo.DependencyWalker') $db.Parent
$depTree = $depWalker.DiscoverDependencies($objList, $parents)
$orderedUrns = $depWalker.WalkDependencies($depTree)
$OrderedUrns2BScripted = $orderedUrns | Where-Object { $urn.Urn.Type -ne 'UnresolvedEntity'}
$cnt = 0
foreach($urn in $orderedUrns) {
...
}
The Powershell script to get the databases DDL can be found in the link I posted here.
Using the ScriptTransfer way you can indeed script out as well DDL as the data (scriptingoptions), but I think doing it the bcp way is faster when done the properly.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 9, 2014 at 2:01 am
I covered this topic in an article on Simple-Talk. You can actually calculate the order using TSQL.
https://www.simple-talk.com/sql/t-sql-programming/database-deployment-the-bits---getting-data-in/
Best wishes,
Phil Factor
December 9, 2014 at 1:42 pm
Early in the script (about line 5) you set ToFileOnly = True. Then about 3 lines from the bottom you set the option to False.
is there a reason for this or was it just an oversight?
December 10, 2014 at 12:56 am
Ray Herring (12/9/2014)
Early in the script (about line 5) you set ToFileOnly = True. Then about 3 lines from the bottom you set the option to False.is there a reason for this or was it just an oversight?
Thank you for bringing this up.
the
$CreationScriptOptions.ToFileOnly = $false
should be removed.
In my actual script ( see link here ) it isn't set to $false at all.
I'll try to alter the article, removing that line.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply