Scripting SQL Server databases with SMO using EnforceScriptingOptions

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Comments posted to this topic are about the item Scripting SQL Server databases with SMO using EnforceScriptingOptions

    Johan


    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[/url] :alien: but most of the time this is me :hehe:

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Apparently the link to the script disappeared.

    So here is the link to my script at OneDrive

    Scripting SQL Server databases with SMO

    Johan


    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[/url] :alien: but most of the time this is me :hehe:

  • Phil Factor

    SSCoach

    Points: 19858

    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
    Simple Talk

  • Johan Bijnens

    SSC Guru

    Points: 134265

    My shrink already told me I wouldn't be the only one puzzled with such documentation.

    Thank you for the confirmation 😀

    Johan


    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[/url] :alien: but most of the time this is me :hehe:

  • MG-148046

    SSCrazy Eights

    Points: 9942

    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.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    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


    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[/url] :alien: but most of the time this is me :hehe:

  • Phil Factor

    SSCoach

    Points: 19858

    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
    Simple Talk

  • MG-148046

    SSCrazy Eights

    Points: 9942

    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.

  • Brandon Forest

    SSCommitted

    Points: 1764

    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

  • Johan Bijnens

    SSC Guru

    Points: 134265

    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


    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[/url] :alien: but most of the time this is me :hehe:

  • Phil Factor

    SSCoach

    Points: 19858

    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
    Simple Talk

  • Ray Herring

    SSCertifiable

    Points: 5308

    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?

  • Johan Bijnens

    SSC Guru

    Points: 134265

    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


    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[/url] :alien: but most of the time this is me :hehe:

Viewing 13 posts - 1 through 13 (of 13 total)

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