Backup and Restore SQL Server databases programmatically with SMO

By: Arshad Ali   |   Read Comments (10)   |   Related Tips: 1 | 2 | 3 | 4 | More > Backup

Problem
In my last set of tips, I discussed SMO at a basic level.  In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO.  I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO.

Solution
As I discussed in my last tip, SMO provides utility classes for specific tasks. For backup and restore, it provides two main utility classes (Backup and Restore) which are available in Microsoft.SqlServer.Management.Smo namespace.

Before you start writing SMO code, you need to reference several assemblies which contain the SMO namespaces. For more details on these assemblies and how properly to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO).

Examples

C# Code Block 1 - Full Backups - This example shows how to issue full database backups with SMO. First, create an instance of the Backup class and set the associated properties. With the Action property you can specify the type of backup such as full, files or log backup. With the Database property specify the name of the database being backed up.  The device is the backup media type such as disk or tape, so you need to add a device (one or more) to the Devices collection of backup instance. With the BackupSetName and BackupSetDescription properties you can specify the name and description for the backup set.  The Backup class also has a property called ExpirationDate which indicates how long backup data is considered valid and to expire the backup after that date. The backup object instance generates several events during the backup operation, we can write event-handlers for these events and wire them up with events. This is what I am doing for progress monitoring.  I am wiring up CompletionStatusInPercent and Backup_Completed methods (event-handlers) with PercentComplete and Complete events of backup object instance.  Finally, I am calling the SqlBackup method for starting up the backup operation, SMO provides a variant of this method called SqlBackupAsync if you want to start the backup operation asynchronously.

C# Code Block 2 Differential Backups - The process of issuing differential backups is not much different from issuing full backups. To issue a differential backup, set the property Incremental = true. If you set this property the incremental/differential backup will be taken since last full backup.

C# Code Block 2 - Differential Database Backup

Backup bkpDBDifferential = new Backup(); /* Specify whether you want to backup database, files or log */ bkpDBDifferential.Action = BackupActionType.Database; /* Specify the name of the database to backup */ bkpDBDifferential.Database = myDatabase.Name; /* You can issue backups on several media types (disk or tape), here I am * using the File type and storing the backup on the file system */ bkpDBDifferential.Devices.AddDevice(@"D:\AdventureWorksDifferential.bak", DeviceType.File); bkpDBDifferential.BackupSetName = "Adventureworks database Backup"; bkpDBDifferential.BackupSetDescription = "Adventureworks database - Differential Backup"; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBDifferential.ExpirationDate = DateTime.Today.AddDays(10);   /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media.  * You can specify Initialize = true to make the backup as the first set  * on the medium and to overwrite any other existing backup sets if the  * backup sets have expired and specified backup set name matches  * with the name on the medium */ bkpDBDifferential.Initialize = false;   /* You can specify Incremental = false (default) to perform full backup  * or Incremental = true to perform differential backup since most recent  * full backup */ bkpDBDifferential.Incremental = true;   /* Wiring up events for progress monitoring */ bkpDBDifferential.PercentComplete += CompletionStatusInPercent; bkpDBDifferential.Complete += Backup_Completed;   /* SqlBackup method starts to take back up  * You cab also use SqlBackupAsync method to perform backup  * operation asynchronously */ bkpDBDifferential.SqlBackup(myServer);

Result:

C# Code Block 3 Transaction Log Backups - Again the process of issuing transactional log backup is not much different from issuing full backups. To issue transactional log backups, set the property Action = BackupActionType.Log instead of BackupActionType.Database as in the case of a full backup.

C# Code Block 3 - Transaction Log Backup

Backup bkpDBLog = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBLog.Action = BackupActionType.Log; /* Specify the name of the database to back up */ bkpDBLog.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am  * using File type and storing backup on the file system */ bkpDBLog.Devices.AddDevice(@"D:\AdventureWorksLog.bak", DeviceType.File); bkpDBLog.BackupSetName = "Adventureworks database Backup"; bkpDBLog.BackupSetDescription = "Adventureworks database - Log Backup"; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBLog.ExpirationDate = DateTime.Today.AddDays(10);   /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media. You  * can specify Initialize = true to make the backup as first set on the  * medium and to overwrite any other existing backup sets if the all the  * backup sets have expired and specified backup set name matches with  * the name on the medium */ bkpDBLog.Initialize = false;   /* Wiring up events for progress monitoring */ bkpDBLog.PercentComplete += CompletionStatusInPercent; bkpDBLog.Complete += Backup_Completed;   /* SqlBackup method starts to take back up  * You cab also use SqlBackupAsync method to perform backup  * operation asynchronously */ bkpDBLog.SqlBackup(myServer);

Result:

C# Code Block 4 Backup with Compression - SQL Server 2008 introduces a new feature to issues backups in a compressed form.  As such, SMO for SQL Server 2008 has been enhanced to support this feature. If you look at the image below you will notice the compressed backup size is almost 25% of full backup, though the level of compression depends on the several factors.

C# Code Block 4 - Backup with Compression (SQL Server 2008)

Backup bkpDBFullWithCompression = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFullWithCompression.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFullWithCompression.Database = myDatabase.Name; /* You can use back up compression technique of SQL Server 2008,  * specify CompressionOption property to On for compressed backup */ bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.On; bkpDBFullWithCompression.Devices.AddDevice(@"D:\AdventureWorksFullWithCompression.bak", DeviceType.File); bkpDBFullWithCompression.BackupSetName = "Adventureworks database Backup - Compressed"; bkpDBFullWithCompression.BackupSetDescription = "Adventureworks database - Full Backup with Compressin - only in SQL Server 2008"; bkpDBFullWithCompression.SqlBackup(myServer);

Result:

C# Code Block 5 Full or Differential Restores - Thus far we have worked through SMO backup examples. Now let's change gears to restore with SMO.  SMO provides a Restore class to restore a database, similar to the Backup class.  With these classes it is necessary to specify the Action property to indicate the type of restore i.e. database, files or log.  In a scenario where if you have additional differential or log backups to be restored after it is necessary to specify the NoRecovery = true except for the final restore.  In this example, I am wiring up events of the Restore object instance to event-handlers for progress monitoring. Finally the SqlRestore method is called to start the restoration. If you want to start the restore operation asynchronously you would need to call SqlRestoreAsync method instead.

C# Code Block 5 - Database Restore - Full or Differential

Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name; /* Specify whether you want to restore database, files or log */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File);   /* You can specify ReplaceDatabase = false (default) to not create a new  * database, the specified database must exist on SQL Server  * instance. If you can specify ReplaceDatabase = true to create new  * database image regardless of the existence of specified database with  * the same name */ restoreDB.ReplaceDatabase = true;    /* If you have a differential or log restore after the current restore,  * you would need to specify NoRecovery = true, this will ensure no  * recovery performed and subsequent restores are allowed. It means it  * the database will be in a restoring state. */ restoreDB.NoRecovery = true;   /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed;   /* SqlRestore method starts to restore the database  * You can also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDB.SqlRestore(myServer);

Result:

To restore a database SQL Server needs to acquire exclusive lock on the database being restored.  If you try to restore a database which is in use, SQL Server will throw the following exception:

C# Code Block 6 Transaction Log Restore - The process of restoring a transactional log is similar to restoring a full or differential backup. While restoring a transactional log, it is necessary to set the property Action = RestoreActionType.Log instead of RestoreActionType.Database as in case of full/differential restore.  Here is an example:

 

C# Code Block 6 - Database Restore - Log

Restore restoreDBLog = new Restore(); restoreDBLog.Database = myDatabase.Name; restoreDBLog.Action = RestoreActionType.Log; restoreDBLog.Devices.AddDevice(@"D:\AdventureWorksLog.bak", DeviceType.File);   /* You can specify NoRecovery = false (default) so that transactions are  * rolled forward and recovered. */ restoreDBLog.NoRecovery = false;   /* Wiring up events for progress monitoring */ restoreDBLog.PercentComplete += CompletionStatusInPercent; restoreDBLog.Complete += Restore_Completed;   /* SqlRestore method starts to restore database  * You cab also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDBLog.SqlRestore(myServer);

Result:

C# Code Block 7 Database Restore to a new location - At times you need to create a new database and restore to a new physical location which differs from the original database. For that purpose, the Restore class has the RelocateFiles collection which can be completed for each file with the new location as shown in the code below.

 

C# Code Block 7 Database Restore - Different location

Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name + "New"; /* Specify whether you want to restore database or files or log etc */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File);   /* You can specify ReplaceDatabase = false (default) to not create a new  * database, the specified database must exist on SQL Server instance.  * You can specify ReplaceDatabase = true to create new database  * regardless of the existence of specified database */ restoreDB.ReplaceDatabase = true;   /* If you have a differential or log restore to be followed, you would  * specify NoRecovery = true, this will ensure no recovery is done  * after the restore and subsequent restores are completed. The database  * would be in a recovered state. */ restoreDB.NoRecovery = false;   /* RelocateFiles collection allows you to specify the logical file names  * and physical file names (new locations) if you want to restore to a  * different location.*/ restoreDB.RelocateFiles.Add(new RelocateFile("AdventureWorks_Data", @"D:\AdventureWorksNew_Data.mdf")); restoreDB.RelocateFiles.Add(new RelocateFile("AdventureWorks_Log", @"D:\AdventureWorksNew_Log.ldf"));   /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed;   /* SqlRestore method starts to restore database  * You can also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDB.SqlRestore(myServer);

Result:

Complete code listing (created on SQL Server 2008 and Visual Studio 2008, though there is not much difference if you are using it on SQL Server 2005 and Visual Studio 2005) can be found in the below text box.

using System; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common; namespace BackupAndRestoreWithSMO2008 { class Program { static void Main(string[] args) { Server myServer = new Server(@"ARSHADALI-LAP\ARSHADALI"); try { //Using windows authentication myServer.ConnectionContext.LoginSecure = true; myServer.ConnectionContext.Connect(); Database myDatabase = myServer.Databases["AdventureWorks"]; BackupDatabaseFull(myServer, myDatabase); //BackupDatabaseDifferential(myServer, myDatabase); //BackupDatabaseLog(myServer, myDatabase); //BackupDatabaseFullWithCompression(myServer, myDatabase); RestoreDatabase(myServer, myDatabase); //RestoreDatabaseLog(myServer, myDatabase); //RestoreDatabaseWithDifferentNameAndLocation(myServer, myDatabase); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.InnerException.Message); } finally { if (myServer.ConnectionContext.IsOpen) myServer.ConnectionContext.Disconnect(); Console.WriteLine("Press any key to terminate..."); Console.ReadKey(); } } private static void BackupDatabaseFull(Server myServer, Database myDatabase) { Backup bkpDBFull = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFull.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFull.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am using * File type and storing backup on the file system */ bkpDBFull.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File); bkpDBFull.BackupSetName = "Adventureworks database Backup"; bkpDBFull.BackupSetDescription = "Adventureworks database - Full Backup"; /* You can specify the expiration date for your backup data * after that date backup data would not be relevant */ bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10); /* You can specify Initialize = false (default) to create a new * backup set which will be appended as last backup set on the media. You can * specify Initialize = true to make the backup as first set on the mediuam and * to overwrite any other existing backup sets if the all the backup sets have * expired and specified backup set name matches with the name on the medium */ bkpDBFull.Initialize = false; /* Wiring up events for progress monitoring */ bkpDBFull.PercentComplete += CompletionStatusInPercent; bkpDBFull.Complete += Backup_Completed; /* SqlBackup method starts to take back up * You cab also use SqlBackupAsync method to perform backup * operation asynchronously */ bkpDBFull.SqlBackup(myServer); } private static void BackupDatabaseDifferential(Server myServer, Database myDatabase) { Backup bkpDBDifferential = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBDifferential.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBDifferential.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am using * File type and storing backup on the file system */ bkpDBDifferential.Devices.AddDevice(@"D:\AdventureWorksDifferential.bak", DeviceType.File); bkpDBDifferential.BackupSetName = "Adventureworks database Backup"; bkpDBDifferential.BackupSetDescription = "Adventureworks database - Differential Backup"; /* You can specify the expiration date for your backup data * after that date backup data would not be relevant */ bkpDBDifferential.ExpirationDate = DateTime.Today.AddDays(10); /* You can specify Initialize = false (default) to create a new * backup set which will be appended as last backup set on the media. You can * specify Initialize = true to make the backup as first set on the mediuam and * to overwrite any other existing backup sets if the all the backup sets have * expired and specified backup set name matches with the name on the medium */ bkpDBDifferential.Initialize = false; /* You can specify Incremental = false (default) to perform full backup * or Incremental = true to perform differential backup since most recent * full backup */ bkpDBDifferential.Incremental = true; /* Wiring up events for progress monitoring */ bkpDBDifferential.PercentComplete += CompletionStatusInPercent; bkpDBDifferential.Complete += Backup_Completed; /* SqlBackup method starts to take back up * You cab also use SqlBackupAsync method to perform backup * operation asynchronously */ bkpDBDifferential.SqlBackup(myServer); } private static void BackupDatabaseLog(Server myServer, Database myDatabase) { Backup bkpDBLog = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBLog.Action = BackupActionType.Log; /* Specify the name of the database to back up */ bkpDBLog.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am using * File type and storing backup on the file system */ bkpDBLog.Devices.AddDevice(@"D:\AdventureWorksLog.bak", DeviceType.File); bkpDBLog.BackupSetName = "Adventureworks database Backup"; bkpDBLog.BackupSetDescription = "Adventureworks database - Log Backup"; /* You can specify the expiration date for your backup data * after that date backup data would not be relevant */ bkpDBLog.ExpirationDate = DateTime.Today.AddDays(10); /* You can specify Initialize = false (default) to create a new * backup set which will be appended as last backup set on the media. You can * specify Initialize = true to make the backup as first set on the mediuam and * to overwrite any other existing backup sets if the all the backup sets have * expired and specified backup set name matches with the name on the medium */ bkpDBLog.Initialize = false; /* Wiring up events for progress monitoring */ bkpDBLog.PercentComplete += CompletionStatusInPercent; bkpDBLog.Complete += Backup_Completed; /* SqlBackup method starts to take back up * You cab also use SqlBackupAsync method to perform backup * operation asynchronously */ bkpDBLog.SqlBackup(myServer); } private static void BackupDatabaseFullWithCompression(Server myServer, Database myDatabase) { Backup bkpDBFullWithCompression = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFullWithCompression.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFullWithCompression.Database = myDatabase.Name; /* You can use back up compression technique of SQL Server 2008, * specify CompressionOption property to On for compressed backup */ bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.On; bkpDBFullWithCompression.Devices.AddDevice(@"D:\AdventureWorksFullWithCompression.bak", DeviceType.File); bkpDBFullWithCompression.BackupSetName = "Adventureworks database Backup - Compressed"; bkpDBFullWithCompression.BackupSetDescription = "Adventureworks database - Full Backup with Compressin - only in SQL Server 2008"; bkpDBFullWithCompression.SqlBackup(myServer); } private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args) { Console.Clear(); Console.WriteLine("Percent completed: {0}%.", args.Percent); } private static void Backup_Completed(object sender, ServerMessageEventArgs args) { Console.WriteLine("Hurray...Backup completed." ); Console.WriteLine(args.Error.Message); } private static void Restore_Completed(object sender, ServerMessageEventArgs args) { Console.WriteLine("Hurray...Restore completed."); Console.WriteLine(args.Error.Message); } private static void RestoreDatabase(Server myServer, Database myDatabase) { Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name; /* Specify whether you want to restore database or files or log etc */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File); /* You can specify ReplaceDatabase = false (default) to not create a new image * of the database, the specified database must exist on SQL Server instance. * If you can specify ReplaceDatabase = true to create new database image * regardless of the existence of specified database with same name */ restoreDB.ReplaceDatabase = true; /* If you have differential or log restore to be followed, you would need * to specify NoRecovery = true, this will ensure no recovery is done after the * restore and subsequent restores are allowed. It means it will database * in the Restoring state. */ restoreDB.NoRecovery = true; /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed; /* SqlRestore method starts to restore database * You cab also use SqlRestoreAsync method to perform restore * operation asynchronously */ restoreDB.SqlRestore(myServer); } private static void RestoreDatabaseLog(Server myServer, Database myDatabase) { Restore restoreDBLog = new Restore(); restoreDBLog.Database = myDatabase.Name; restoreDBLog.Action = RestoreActionType.Log; restoreDBLog.Devices.AddDevice(@"D:\AdventureWorksLog.bak", DeviceType.File); /* You can specify NoRecovery = false (default) so that transactions are * rolled forward and recovered. */ restoreDBLog.NoRecovery = false; /* Wiring up events for progress monitoring */ restoreDBLog.PercentComplete += CompletionStatusInPercent; restoreDBLog.Complete += Restore_Completed; /* SqlRestore method starts to restore database * You cab also use SqlRestoreAsync method to perform restore * operation asynchronously */ restoreDBLog.SqlRestore(myServer); } private static void RestoreDatabaseWithDifferentNameAndLocation(Server myServer, Database myDatabase) { Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name + "New"; /* Specify whether you want to restore database or files or log etc */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@"D:\AdventureWorksFull.bak", DeviceType.File); /* You can specify ReplaceDatabase = false (default) to not create a new image * of the database, the specified database must exist on SQL Server instance. * You can specify ReplaceDatabase = true to create new database image * regardless of the existence of specified database with same name */ restoreDB.ReplaceDatabase = true; /* If you have differential or log restore to be followed, you would need * to specify NoRecovery = true, this will ensure no recovery is done after the * restore and subsequent restores are allowed. It means it will database * in the Restoring state. */ restoreDB.NoRecovery = false; /* RelocateFiles collection allows you to specify the logical file names and * physical file names (new locations) if you want to restore to a different location.*/ restoreDB.RelocateFiles.Add(new RelocateFile("AdventureWorks_Data", @"D:\AdventureWorksNew_Data.mdf")); restoreDB.RelocateFiles.Add(new RelocateFile("AdventureWorks_Log", @"D:\AdventureWorksNew_Log.ldf")); /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed; /* SqlRestore method starts to restore database * You cab also use SqlRestoreAsync method to perform restore * operation asynchronously */ restoreDB.SqlRestore(myServer); } } }

Notes:

• Location of assemblies in SQL Server 2005 is the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder.

• Location of assemblies in SQL Server 2008 is the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.

• In SQL Server 2005, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.Smo (microsoft.sqlserver.smo.dll) assembly.

• In SQL Server 2008, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.SmoExtended (microsoft.sqlserver.smoextended.dll) assembly.

• If you are restoring a transaction log, you can specify a particular point in time with ToPointInTime property of the Restore class.

• The Restore class methods (SqlVerify, SqlVerifyAsync and SqlVerifyLatest) to verify and validate (backup set is complete and the entire backup is readable) the backup media before restoration.

• The SQL Server service account must have access to the folders where backup or restore operations are executed.

• You need to have sufficient permissions to perform backup and restore operations. For example, for backup you need to be either in sysadmin/db_owner/db_backupoperator role or must have BACKUP DATABASE or BACKUP LOG permission on the database.

• If you try to connect SQL Server 2008 from SMO 2005, you will get an exception "SQL Server <10.0> version is not supported".

Next Steps

• Review the following tips:

  • Getting started with SQL Server Management Objects (SMO)

  • SQL Script Generation Programmatically with SMO

  • Exploring Database Backup Compression in SQL Server 2008

• Review Backup and Restore classes on MSDN.

• Review the MSSQLTips Backup and Recovery category with 40+ tips.

Last Update: 10/1/2009

About the author

Arshad is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips

We Recommend

• Simple script to backup all SQL Server databases...
• Automating Transaction Log Backups for All SQL Ser...
• Differential Database Backups for SQL Server...
• More SQL Server DBA Tips...

Comments and Feedback:

Monday, May 10, 2010 - 5:56:59 AM - Perhentian

Read The Tip

Hi, I want to programatically (c#) restore a database (SQL Server 2008) which was originally from my live environment. My local environment is obviously quite different, with different drive mappings. I have followed this article which has been a great help, but I am struggling to find out how I can restore the full text catalogs to a different location. I actually would like to completely ignore them as this process doesn't require them, but if I can't ignore them, I need to specify a different location for the files. Could someone advise? I have used the following code to specify the location of the data files, and it maybe that I need something similar for the full text catalogs: restoreDb.RelocateFiles.Add(new RelocateFile("SmartT", m_workingDirectory + "SmartT_Data.mdf")); restoreDb.RelocateFiles.Add(new RelocateFile("SmartT_Log", m_workingDirectory + "SmartT_Log.ldf")); InnerException {Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Directory lookup for the file "g:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\ftrow_SmartTEventsFTCat.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15100). File 'ftrow_SmartTEventsFTCat' cannot be restored to 'g:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\ftrow_SmartTEventsFTCat.ndf'. Use WITH MOVE to identify a valid location for the file.

Saturday, April 09, 2011 - 3:23:07 AM - Sai Kalyan kumar	Read The Tip
Hello, Mr.Arshad Ali. I referred to ur code about Database Backup and Restore programmatically, similarly i have written some code to the Database backup Full and Differential in Simple recovery. But i got a problem in taking the restore of a database only for the Date specified. Can u pls help me how to get the Database restore for a particular date from a ".bak" file.  Regards, Kalyan kumar.

Sunday, February 26, 2012 - 3:31:57 AM - Michael

Read The Tip

Hi, I tried to implement the cancellation of the Restore - with no success: the DB stays in the "Restoring" mode. My code looks like this:         private bool _CancelRestore = false;         private Restore _RestoreDB;         private Server _myServer;         private Database _currentDatabase;           public void CancelRestore()         {             _CancelRestore = true;         }           private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args)          {             if (_CancelRestore)             {                 _RestoreDB.Abort();                 // Stop all processes running on the _currentDatabase database                 srv.KillAllProcesses(_currentDatabase.Name);                   // Stop the _currentDatabase database                 // NOTE: it is a temp name DB: I do not restore over my application DB!                 srv.KillDatabase(_currentDatabase.Name);             }             else             {                 Console.Clear();                 Console.WriteLine("Percent completed: {0}%.", args.Percent);             }         } thanks, Michael.

Wednesday, March 14, 2012 - 1:20:44 AM - Ahmad	Read The Tip
Hello Mr. Arshad. Can I use ur code to backup a remote database but the backup file will be saved to my local machine not the remote machine as I don't have access right to the remote machine. Thanks, Ahmad

Wednesday, March 14, 2012 - 9:38:36 AM - Arshad	Read The Tip
Yes Ahmad, I am assuming this should work, please try it out and let me know if you face any issue.

Tuesday, April 03, 2012 - 1:55:26 PM - Tanvir Rahman	Read The Tip
I cant restore. It shows me a problem that the db is running. What to do???

Wednesday, April 04, 2012 - 2:36:33 AM - Arshad	Read The Tip
You cannot restore to a database which is already in use; please check if anyone is connected (open connection or lock) to database you are trying to restore;

Monday, April 30, 2012 - 8:15:52 AM - Mangesh

Read The Tip

Server Myserver = new Server("TSERVER\\SQLEXPRESS2008");            Myserver.ConnectionContext.LoginSecure = false;                        Myserver.ConnectionContext.Login = "sa";             Myserver.ConnectionContext.Password ="password123";             Myserver.ConnectionContext.Connect();             Backup Back_up = new Backup();             Back_up.Action = BackupActionType.Database;             Back_up.Database = "restore";             Back_up.Devices.AddDevice(@"D:\DemoStudent.bak", DeviceType.File);please explain this line i have create text file name  is Demostudent.bak is it right.........tell the solution please             Back_up.BackupSetName = "StudentBackupData";             Back_up.BackupSetDescription = "Studentdetailsinformation";             Back_up.ExpirationDate = DateTime.Now.AddDays(10);             Back_up.Initialize = false;                        Database db = Myserver.Databases["restore"];             Back_up.ContinueAfterError = true;             Back_up.LogTruncation = BackupTruncateLogType.Truncate;             Back_up.FormatMedia = false;            Back_up.SqlBackup(Myserver);//Runtime error occur....... Cannot open backup device 'D:\DemoStudent.bak'. Operating system error 21(The device is not ready.). BACKUP DATABASE is terminating abnormally.

Thursday, May 31, 2012 - 11:55:00 PM - 3lyaa	Read The Tip
i used the same way and it worked very well with sql server 2005 now i test it on sql server 2008 but it doesn't work, i always have that error Backup failed for Server 'server name' i don't know what i should do

Thursday, October 18, 2012 - 3:01:06 PM - Nikhil Agrawal	Read The Tip
Good Article, learnt a lot about how smo works. I'm trying to transfer a sql database to sql unicode. So the columns need to have - nchar, nvarchar and ntext - change in the schema. How can I use this code listing you have to make my scenario work. Thanks, Nikhil

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now Wish your SQL Servers could run wide open? Learn how the Edgewood SQL Server Consultants can make it happen. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. The SQL Server Security THREAT - It’s Closer Than You Think