Using backup database programmatically
Solution 1
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
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)
Solution 2
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.
Solution 3
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'
Solution 4
Check this link:
Creating SQL Server backup file (.bak) with c# to any location
Also this:
Smiley
Updated on July 23, 2022Comments
-
Smiley almost 2 years
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#?
-
driis over 13 yearsSMO shouldn't really be needed for such a simple task.
-
Mitch Wheat over 13 years@driis: since when was using a .bak file programatically a simple task?
-
marc_s over 13 years@driis: yes, if it's only this task - then SMO might be overkill. But the OP might extend this to do more. Sometimes it's also just a choice - do I want to use T-SQL statement and execute those, or do I want to use a task-specific library to achieve something.
-
Dr TJ over 13 years@marc_s: That's really cool... But, I can't find the reference of
Microsoft.SqlServer.Management.Common;
-
Dr TJ over 13 years@marc_s: thank you, but I was looking for just
Common
namespace which is hereC:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
-
Smiley over 13 yearsso does this mean that I don't really need to add the reference of the SMO? :)
-
marc_s over 13 years@Kim Rivera: you do need those references - you just might need to "browse..." for the correct assembly (or you might find it in the ".NET" tab of the "Add Reference..." dialog in Visual Studio)
-
Jake over 13 years+1 there is always a way to delegate responsibility to another language. thanks for posting how to do this with C#.
-
GrayedFox over 11 yearsThis is a great answer. However, for me, it didn't work because I had to relocated files. So, be sure to create
RelocateFile
file objects and add them toRestore.RelocateFiles
container. The list of files that need to be relocated can be found in withrestore.ReadBackupHeader(sql_server)
-
Cory Baumer about 6 yearsYou can also install SMO with Nuget package manager, see: nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects