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)
    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;

LogFile logFile = new LogFile(database, "Log", 
logFile.Size = 7.0 * 1024.0;
logFile.GrowthType = FileGrowthType.Percent;
logFile.Growth = 10.0;
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);
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)
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);


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

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

  3. 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 !

  4. I was working and suddenly I visits your site frequently and recommended it to me to read also. The writing style is superior and the content is relevant. Thanks for the insight you provide the readers!
    download free descargar whatsapp and download baixar whatsapp online and descargar whatsapp gratis , baixar whatsapp gratis

    1. An important building block of SEO is to develop your website which can be easily understandable for both Online Visitors (most important) and search engine robots. In the current digital age, seo is important for your online success means to generate leads.

      SEO Services in IndiaSEO Company in India SEO Company in India SEO Services in India

      SEO Company in India SEO Services in India

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

  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

  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

  8. Hey Gyss Check out This..

    Creative Kalakar is best SEO company in Lucknow. We are Best Digital Marketing Agency specializing in helping businesses make a profitable income from the Internet.
    Our services span into every facet of Digital Marketing,
    including Search Engine Optimization (SEO), Pay Per Click Management (PPC),
    Web Design, Graphics Design and Online Reputation Management (ORM).