Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using backup database programmatically

How can I use a .bak database backup file (backed up via a query in SQL Server) programmatically?

I want my application to back up my database to a location (which I can already do) and I also want it to be able to load a backed up database (the .bak file).

How can I do this using C#?

like image 380
Smiley Avatar asked Oct 17 '10 14:10

Smiley


People also ask

Can you backup SQL database while use?

SQL databases can be backed up while in use but it is important to ensure that your backups are capturing all information with a known RPO and RTO in case of problems.


3 Answers

You need to first make sure you have the SMO (SQL Server Management Objects) installed and available to you on your dev box. This is typically the case, if you have installed some version of SQL Server on it.

If you have the SMO library available, you can use this code snippet for your operation:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

static void Main(string[] args)
{
    // create instance of SMO Server object
    Server myServer = new Server("(local)");

    // create new instance of "Restore" object    
    Restore res = new Restore();
    res.Database = "SMO";  // your database name

    // define options       
    res.Action = RestoreActionType.Database;
    res.Devices.AddDevice(@"C:\SMOTest.bak", DeviceType.File);
    res.PercentCompleteNotification = 10;
    res.ReplaceDatabase = true;

    // define a callback method to show progress
    res.PercentComplete += new PercentCompleteEventHandler(res_PercentComplete);

    // execute the restore    
    res.SqlRestore(myServer);
 }

 // method to show restore progress
 static void res_PercentComplete(object sender, PercentCompleteEventArgs e)
 {
    // do something......
 }

For this to work, you need to have the following project references

alt text

and the namespace Microsoft.SqlServer.SmoExtended is implemented in the assembly called Microsoft.SqlServer.SmoExtended.dll which should be found in the directory C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ if you have SMO installed.

If you don't have SMO installed, you can go fetch it from here for SQL Server 2008 or here for SQL Server 2008 R2 (there's also an older version for SQL Server 2005)

like image 51
marc_s Avatar answered Oct 08 '22 22:10

marc_s


Simply use SqlCommand.ExecuteNonQuery to execute the SQL needed to perform the operations, such as:

BACKUP DATABASE [dbname] ......

RESTORE DATABASE [dbname] ......

Of course the SQL user in question will need to have appropiate permissions.

like image 34
driis Avatar answered Oct 08 '22 21:10

driis


This is how to backup:

-- =========================================================
-- Author:        Stefan
-- Create date:   16.07.2010
-- Last mutation: 16.07.2010
-- Description:   Backup der ausgewählten Datenbank
-- =========================================================
CREATE PROCEDURE [dbo].[sp_BackupDatabase] 
    @in_strDataBase varchar(50)
    --,@in_strUser varchar(36)

AS
BEGIN

DECLARE @strBasePath  nvarchar(3000)
DECLARE @strFileName  nvarchar(1000)

DECLARE @strFileNameAndPath  nvarchar(4000)

SET @strBasePath = 'E:\Temp\'

SET @strFileName = @in_strDataBase
SET @strFileName = @strFileName + '_'
SET @strFileName = @strFileName + convert(varchar, getdate(), 112)
SET @strFileName = @strFileName + '_' + REPLACE(convert(varchar, getdate(), 108),':','_'); 
SET @strFileName = @strFileName + '_sts' 
SET @strFileName = @strFileName + '.bak'

SET @strFileNameAndPath = @strBasePath + @strFileName

PRINT @strFileNameAndPath

BACKUP DATABASE @in_strDataBase TO DISK=@strFileNameAndPath

END

GO

And this is how to restore:

RESTORE DATABASE MyDatabase
FROM DISK='C:\temp\MyDatabase_20100810.bak' 
WITH REPLACE,
MOVE 'MyDatabase' TO 'E:\SQLData_2008\MyDatabase.mdf',
MOVE 'MyDatabase_log' TO 'E:\SQLData_2008\MyDatabase.ldf' 
like image 26
Stefan Steiger Avatar answered Oct 08 '22 21:10

Stefan Steiger