Scripting SQL Server databases with SMO using EnforceScriptingOptions

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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.

  • 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! 🙂

    Brandon_Forest@calquake.com

  • 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! 🙂

    Brandon_Forest@calquake.com

    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

  • 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

  • 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?

  • 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