Monday, August 15, 2011

SMO - Working with databases

Enumerating databases, filegroups, and files
The Database property of the Server object represents a collection of Database objects. Using this collection, you can enumerate the databases on SQL Server.
Server srv = new Server(conn);
foreach (Database db in srv.Databases)
{
    Console.WriteLine(db.Name);
    foreach (FileGroup fg in db.FileGroups)
    {
        Console.WriteLine("   " + fg.Name);
        foreach (DataFile df in fg.Files)
        {
            Console.WriteLine("      " + df.Name + " " + df.FileName);
        }
    }
}
Enumerating database properties
Database properties are represented by the Properties property of a Database object. Properties is a collection of Property objects. The following sample demonstrates how to get database properties:
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
foreach (Property prop in database.Properties)
{
    Console.WriteLine(prop.Name + " " + prop.Value);
}
Creating databases
With SMO, you can create databases. When you want to create a database, you must create the Database object. This example demonstrates how to create a database named MyNewDatabase and create a data file called MyNewDatabase.mdf (in the primary filegroup) and a log file named MyNewDatabase.log.
Database database = new Database(srv, "MyNewDatabase");
database.FileGroups.Add(new FileGroup(database, "PRIMARY"));
DataFile dtPrimary = new DataFile(database.FileGroups["PRIMARY"], 
         "PriValue", @"E:\Data\MyNewDatabase\MyNewDatabase.mdf");
dtPrimary.Size = 77.0 * 1024.0;
dtPrimary.GrowthType = FileGrowthType.KB;
dtPrimary.Growth = 1.0 * 1024.0;
database.FileGroups["PRIMARY"].Files.Add(dtPrimary);

LogFile logFile = new LogFile(database, "Log", 
        @"E:\Data\MyNewDatabase\MyNewDatabase.ldf");
logFile.Size = 7.0 * 1024.0;
logFile.GrowthType = FileGrowthType.Percent;
logFile.Growth = 10.0;
 
database.LogFiles.Add(logFile);
database.Create();
database.Refresh();
SMO allows you to set the Growth of a database and other properties. More about properties can be found on MSDN. When you want to drop a database, just call the Drop() method of the Database object.

Database backup
SMO allows you to backup databases very easily. For backup operations, you must create an instance of the Backup class and then assign the Action property to BackupActionType.Database. Now you have to add a device you want to backup to. In many cases, it is file. You can backup not only to file, but to tape, logical drive, pipe, and virtual device.
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = database.Name;
backup.Devices.AddDevice(@"E:\Data\Backup\AW.bak", DeviceType.File);
backup.PercentCompleteNotification = 10;
backup.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
backup.SqlBackup(srv);
SMO allows you to monitor the progress of a backup operation being performed. You can easily implement this feature. The first thing you must do is create an event handler with the PercentCompleteEventArgs parameter. This parameter includes the Percent property that contains the percent complete value. This value is an integer between 0 and 100.
static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
{
    Console.WriteLine(e.Percent);
}
Performing a log backup operation is similar to a database backup. Just set the Action property to Log instead of Database.

Database restore
SMO allows you to perform database restore easily. A database restore operation is performed by the Restore class which is in the Microsoft.SqlServer.Management.Smo.Restore namespace. Before running any restore, you must provide a database name and a valid backup file. Then you must set the Action property. To restore a database, set it to RestoreActionType.Database. To restore a log, just set it to RestoreActionType.Log. During restore, you can monitor the progress of the restoring operation. This could be done the same way as in the case of database backup.
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
Backup backup = new Backup();
Restore restore = new Restore();
restore.Action = RestoreActionType.Database;
restore.Devices.AddDevice(@"E:\Data\Backup\AW.bak", DeviceType.File);
restore.Database = database.Name;
restore.PercentCompleteNotification = 10;
restore.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
restore.SqlRestore(srv);

47 comments:

  1. I wish to thank you for your informative post....ACC 561 Entire Course

    ReplyDelete
  2. Thank you for sharing valuable information. Nice post. I enjoyed reading this post. The whole blog is very nice found some good stuff and good information here Thanks..Also visit my page. Affordable Link building service WebOptimiA is a 21st Century's Web Optimization provider company with a pioneer of providing WEB enabled services form last three year.

    ReplyDelete
  3. Marketing is the main factor which attract more clients to your business.
    Social Media Ads in Indore

    ReplyDelete
  4. Hi,
    everything here is right, but too simple. We presume that the user has rights I/O on the target directory, to backup/restore the database.
    What append when it's not the case?
    I have to backup/restore db from a distant server, and also delete a specific .bak when more than 3 backups are done.

    An idea ??

    Thanks !

    ReplyDelete
  5. That's really nice post thanks for the sharing with us. love this post. Social Media Optimization in India

    ReplyDelete
  6. I simply couldn’t depart your site before suggesting that I really enjoyed the usual information an individual supply in your visitors? Is going to be again steadily to check out new posts.


    SEO Company in Chennai | Digital Marketing Company in Chennai

    ReplyDelete
  7. After looking into a handful of the blog articles on your site, I really like your technique of writing a blog. I book marked it to my bookmark site list and will be checking back in the near future. Take a look at my website as well and let me know your opinion.
    SEO Company in Chennai

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. I simply want to tell you that I’m all new to blogs and truly liked you’re blog site. Very likely I’m likely to bookmark your site .You surely come with remarkable articles. Cheers for sharing your website page.

    SEO Company in Chennai

    ReplyDelete
  16. Great and really helpful article! Adding to the conversation, providing more information, or expressing a new point of view...Nice information and updates. Really i like it and everyday am visiting your site..

    Hadoop Training in Chennai

    Java Training in Chennai

    ReplyDelete
  17. wow really nice. It will be helpful for the people those who are ready to crack the interview and please also for remind what they have learned throughout concept.

    SEO Company in India

    Digital Marketing Company in India

    ReplyDelete
  18. I really like your technique of writing a blog. I book marked it to my bookmark site list and will be checking back in the near future. Take a look at my website as well and let me know your opinion.
    best seo services in lucknow

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
  21. Very nice blog.I am very inspired to read your post. Thanks for sharing.
    Website Development Company in Lucknow 

    ReplyDelete
  22. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.


    Java training in Chennai

    Java training in Bangalore

    Java training in Hyderabad

    Java Training in Coimbatore

    Java Online Training

    ReplyDelete
  23. Thanks for any other wonderful post. Where else may just anyone get that type of info in such a perfect means of writing? I’ve a presentation next week, and I am on the look for such information.

    hardware and networking training in chennai

    hardware and networking training in velachery

    xamarin training in chennai

    xamarin training in velachery

    ios training in chennai

    ios training in velachery

    iot training in chennai

    iot training in velachery

    ReplyDelete
  24. I like the helpful info you supply in your articles. I’ll bookmark your weblog and take a look at once more here regularly. I am relatively certain I will learn a lot of new stuff right here! Good luck for the following!
    angular js training in chennai

    angular js training in tambaram

    full stack training in chennai

    full stack training in tambaram

    php training in chennai

    php training in tambaram

    photoshop training in chennai

    photoshop training in tambaram

    ReplyDelete
  25. After looking into a handful of the blog articles on your site, I really like your technique of writing a blog. I book marked it to my bookmark site list and will be checking back in the near future. Take a look at my website as well and let me know your opinion.
    web designing training in chennai

    web designing training in omr

    digital marketing training in chennai

    digital marketing training in omr

    rpa training in chennai

    rpa training in omr

    tally training in chennai

    tally training in omr

    ReplyDelete
  26. You may unlock all kinds of soldiers and most of assignments by minding it with Lucky Patcher. Trust meYou may like it more than with the added patch from Lucky Patcher.


    Lucky Patcher Apk

    ReplyDelete
  27. Accounting & billing software’s brings transparency in transactions. So, Contact Signature IT best Accounting, billing Software Company in lucknow.
    Billing software development Company
    accounting software development company

    ReplyDelete
  28. for providing a great informatic and looking beautiful blog, really nice required information & the things I never imagined and I would request, wright more blog and blog post like that for us.

    ReplyDelete
  29. Nice Post.https://tamizhakarthic.com/social-media-marketing-training-courses-online/

    ReplyDelete