Learn more about SQL Server tools

   
   















































Backup and Restore SQL Server databases programmatically with SMO

MSSQLTips author Arshad Ali By:   |   Read Comments (14)   |   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 1 - Full Database Backup

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
 * 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 */

bkpDBFull.Initialize = false;
 
/* Wiring up events for progress monitoring */
bkpDBFull.PercentComplete += CompletionStatusInPercent;
bkpDBFull.Complete += Backup_Completed;
 
/* SqlBackup method starts to take back up
 * You can also use SqlBackupAsync method to perform the backup
 * operation asynchronously */
bkpDBFull.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);
}

Result:

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.

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



Last Update: 10/1/2009


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, June 16, 2014 - 1:57:52 PM - Jitesh Kumar Read The Tip

Hi,

 

Thank for this awesome tutorial.

I follow your code and try to create backup of database, it works fine and creating backup on default location, but when i am try to creating at 

specified location, then its throwing exception.

 

Code for location:

backup.Devices.AddDevice(@"D:\", DeviceType.File);

 

Exception:

Backup failed for Server 'JITESH-PC\SQLEXPRESS'.

 

Please assist me.

Thank in advance.


Monday, May 19, 2014 - 4:16:17 AM - Munashe Read The Tip

I am using SQL Server 2012 LocalDb.

I can't find the Backup and Restore classes in Microsoft.SqlServer.Management.Smo

The only classes I see are classes like BackupDeviceBackupDeviceCollectionBackupMediaBackupSet etc

I have the reference to Microsoft.SqlServer.Smo.

Any idea why the Backup and Restore classes are not appearing in my Visual Studio 2013?


Thursday, June 27, 2013 - 6:01:09 AM - sridhar Read The Tip

sir i want write for backup, but in c# 2010 backup.Action property is missing. Please help me sir.


Wednesday, June 26, 2013 - 12:13:24 AM - Md Mashkoor Alam Read The Tip

using System;

 

using System.Collections.Generic;

 

using System.Text;

 

using Microsoft.SqlServer.Management.Smo;

 

using Microsoft.SqlServer.Management.Common;

 

namespace ConsoleApplication1

 

 

{

classProgram

 

 

{

staticvoid Main(string[] args)

 

 

{

Server myServer = newServer(@"MASHKOOR-PC");

 

try

 

 

{

myServer.ConnectionContext.LoginSecure = false;

 

myServer.ConnectionContext.Login = "sa";

 

myServer.ConnectionContext.Password = "md22mashkoor";

 

 

myServer.ConnectionContext.Connect();

Database myDatabase = myServer.Databases["Kolkata_Dental"];

 

 

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

 

 

 

 

}

 

}

privatestaticvoid BackupDatabaseFull(Server myServer, Database myDatabase)

 

 

{

Backup bkpDBFull = newBackup();

 

bkpDBFull.Action = BackupActionType.Database;

 

 

bkpDBFull.Database = myDatabase.Name;

bkpDBFull.Devices.AddDevice(@"D:\KolkataDentalFull.bak", DeviceType.File);

 

bkpDBFull.BackupSetName = "KolkataDental database Backup";

 

bkpDBFull.BackupSetDescription = "KolkataDental database - Full Backup";

 

bkpDBFull.ExpirationDate = DateTime.Today.AddDays(1000);

 

bkpDBFull.Initialize = false;

 

 

bkpDBFull.PercentComplete += CompletionStatusInPercent;

 

bkpDBFull.Complete += Backup_Completed;

 

bkpDBFull.SqlBackup(myServer);

 

 

 

}

privatestaticvoid CompletionStatusInPercent(object sender, PercentCompleteEventArgs args)

 

 

{

Console.Clear();

 

Console.WriteLine("Percent completed: {0}%.", args.Percent);

 

 

}

privatestaticvoid Backup_Completed(object sender, ServerMessageEventArgs args)

 

 

{

Console.WriteLine("Hurray...Backup completed.");

 

Console.WriteLine(args.Error.Message);

 

 

}

privatestaticvoid RestoreDatabase(Server myServer, Database myDatabase)

 

 

{

Restore restoreDB = newRestore();

 

 

restoreDB.Database = myDatabase.Name;

/* Specify whether you want to restore database or files or log etc */

 

restoreDB.Action = RestoreActionType.Database;

 

restoreDB.Devices.AddDevice(@"D:\KolkataDentalFull.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);

 

}

privatestaticvoid Restore_Completed(object sender, ServerMessageEventArgs args)

 

 

{

Console.WriteLine("Hurray...Restore completed.");

 

Console.WriteLine(args.Error.Message);

 

 

}

 

}

 

}

 

Backup failed for Server 'MASHKOOR-PC'
Could not laod file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An attempt was made to load a program with an incorrect format.


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


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

 


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.



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;


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


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


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.


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.


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.



 
Sponsor Information