Advertisements
Home > Powershell, SQL Server > PowerShell for Altering Database Properties

PowerShell for Altering Database Properties

This article will help to understand how to alter databases properties using PowerShell V3.0 and SMO. In previous article we seen PowerShell for Creating a Database. Basically we can alter database options from Automatic, Containment, Cursor, File Stream, Miscellaneous, Recovery, Service Broker and State Categories.

Following PowerShell Script will help you to understand what I’m talking about.

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName

#provide your database name where you want to change database properties
$DatabaseName = "PSDB"

#create SMO handle to your database
$DBObject = $Server.Databases[$DatabaseName]

#Database Options to change ANSI PADDING, NULLS and Warnings
$DBObject.DatabaseOptions.AnsiPaddingEnabled = $false
$DBObject.DatabaseOptions.AnsiNullsEnabled = $false
$DBObject.DatabaseOptions.AnsiWarningsEnabled = $false

#Database Options to change Database User Access
#DatabaseUserAccess enum :: single, multiple, restricted
$DBObject.DatabaseOptions.UserAccess = [Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]:: Restricted
$DBObject.Alter()

#In some cases you cannot use DatabaseOptions
#where you can update your SMO handle directly
$DBObject.CompatibilityLevel = [Microsoft.SqlServer.Management.Smo.CompatibilityLevel]::Version90
$DBObject.AutoUpdateStatisticsEnabled =$true
$DBObject.Alter()

#Set your database ReadOnly with DatabaseOptions
$DBObject.DatabaseOptions.ReadOnly = $true
$DBObject.Alter()

Following screen show is talking about the Updated Database Options during the execution of above PowerShell Script. You can browse this screen by selecting database properties and Options tab where you can notice updated Database Options during PowerShell Execution.

Hope this Helps

Sandip

Advertisements
  1. March 9, 2013 at 10:14 am

    This is going to be very useful for me thank you very much for posting

  2. vps
    March 14, 2013 at 10:11 pm

    Your blog is very interesting. You write about very interesting things. Thanks for all your tips and information.

  3. March 19, 2013 at 2:23 pm

    It’s an amazing piece of writing designed for all the online viewers; they will get advantage from it I am sure.

  4. March 20, 2013 at 5:40 am

    Good post. I learn something totally new and challenging on sites I stumbleupon on a daily basis.

    It will always be useful to read through articles from other writers
    and practice a little something from other web sites.
    does beer cause cellulite

  5. March 21, 2013 at 1:54 am

    Can I just say what a relief to find a person that actually understands what
    they are talking about on the net. You certainly realize how to bring
    a problem to light and make it important. A lot more people should read this and understand this side of your story.
    I was surprised you aren’t more popular because you certainly possess the gift.astrologer ithaca ny

  6. March 21, 2013 at 6:12 pm

    Thanks on your marvelous posting! I quite enjoyed reading it, you can be a great author.
    I will ensure that I bookmark your blog and
    definitely will come back in the future. I want to encourage that you continue your great writing,
    have a nice holiday weekend!motorbike vin check

  7. March 22, 2013 at 11:16 am

    Your style is unique compared to other people I’ve read stuff from. Thank you for posting when you’ve got the opportunity, Guess I’ll just book mark this blog.premature ventricular contractions tumblr

  8. March 22, 2013 at 9:33 pm

    I am sure this paragraph has touched all the internet visitors, its really really
    fastidious post on building up new web site.

  9. March 23, 2013 at 1:56 am

    Hello! I’m at work surfing around your blog from my new iphone! Just wanted to say I love reading your blog and look forward to all your posts! Keep up the outstanding work!become taller free

  10. March 25, 2013 at 8:03 am

    Amazing! Its truly amazing paragraph, I have got much clear idea regarding from this article.

  11. March 25, 2013 at 8:05 am

    I was recommended this web site by my cousin.
    I am not sure whether this post is written by him as nobody else know such detailed about my difficulty.
    You are wonderful! Thanks!

  12. March 26, 2013 at 1:54 am

    Does your website have a contact page? I’m having a tough time locating it but, I’d like
    to send you an e-mail. I’ve got some recommendations for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it develop over time.

  13. April 1, 2013 at 3:41 am

    Very nice article, totally what I was looking for.

  14. April 1, 2013 at 6:07 pm

    There’s definately a lot to find out about this issue. I like all the points you have made.

  15. April 1, 2013 at 8:48 pm

    If some one desires to be updated with newest technologies then he
    must be pay a visit this web page and be up to date every day.

  16. April 1, 2013 at 9:17 pm

    My brother suggested I may like this web site. He was totally
    right. This submit actually made my day. You can not imagine just how a lot time I had spent for this information!
    Thank you!

  17. April 1, 2013 at 10:53 pm

    Excellent goods from you, man. I’ve understand your stuff previous to and you are just extremely fantastic. I really like what you’ve acquired here, certainly like what you are saying and
    the way in which you say it. You make it enjoyable and you still take care of to keep it wise.
    I cant wait to read much more from you. This is really a tremendous web site.

  18. April 2, 2013 at 12:04 am

    Howdy! This is my first visit to your blog!
    We are a team of volunteers and starting a new initiative in a community in the
    same niche. Your blog provided us valuable information to work
    on. You have done a marvellous job!

  19. April 2, 2013 at 1:03 am

    What’s up, I check your blog regularly. Your story-telling style is awesome, keep up the good work!

  20. April 2, 2013 at 2:24 am

    Hi! This is kind of off topic but I need some help from
    an established blog. Is it very difficult to set up your own blog?
    I’m not very techincal but I can figure things out pretty fast. I’m thinking about making
    my own but I’m not sure where to begin. Do you have any points or suggestions? Cheers

  21. April 2, 2013 at 4:07 am

    Have you ever thought about creating an ebook or guest authoring on
    other websites? I have a blog based on the same information you
    discuss and would love to have you share some stories/information.
    I know my audience would appreciate your work. If you’re even remotely interested, feel free to shoot me an e mail

  22. April 2, 2013 at 7:37 am

    Hello, Neat post. There is a problem together with your web site
    in web explorer, could check this? IE nonetheless is the marketplace
    leader and a large component of other folks will leave out your wonderful writing because of this problem.

  23. April 2, 2013 at 7:46 am

    Hello, I enjoy reading through your article post.
    I like to write a little comment to support you.

  24. April 2, 2013 at 12:28 pm

    Very good info. Lucky me I discovered your blog by accident (stumbleupon).
    I have book-marked it for later!

  25. April 2, 2013 at 1:08 pm

    I have learn some excellent stuff here. Certainly price bookmarking
    for revisiting. I surprise how so much attempt
    you put to make such a magnificent informative web
    site.

  26. April 2, 2013 at 1:30 pm

    I don’t even know how I ended up here, but I thought this post was great. I don’t know who you are but
    certainly you’re going to a famous blogger if you are not already 😉 Cheers!

  27. April 2, 2013 at 2:34 pm

    Keep this going please, great job! std symptoms the next day

  28. July 27, 2013 at 9:48 pm

    My partner and I stumbled over here by a different page
    and thought I might check things out. I like what I see so now i am following
    you. Look forward to looking at your web page yet again.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: