1

I've written a Windows application which uses the Sql Express database server.

How can I programatically backup and restore the database through my application?

3 Answers 3

2

You could have your application launch a .sql file that does the backing up or restoring for you. Granted, I've mostly seen this done in a Scheduled Task, but I suppose you could do it from a triggered event inside your app.

System.Diagnostics.Process p = new System.Diagnostics.Process();
p.StartInfo.FileName = "C:\\path\\to\\backup.sql";
p.Start();

UPDATE:

As pointed out in the comments, this won't work if you don't have SQL Management Studio installed on the server. Alternatively, you could call a stored procedure. Upon reflecting, I'm not sure why I didn't suggest the stored proc first - probably because the other methodology was fresh on my brain due to being forced to implement it that way in a previous project.

Sign up to request clarification or add additional context in comments.

1 Comment

This assumes the Management Studio is installed in the same machine as the windows app. That could not be the case, so this is not going to work always.
1

You can request a backup from within your app by executing:

@"BACKUP DATABASE [MyDBName] TO DISK = 'c:\somedir\MyDBName.bak' WITH INIT" (ref)

Or use SQL SMO Objects SqlBackup() directly.

Comments

1

You could use SQL Server Management Objects

First add a reference in your project to: Microsoft.SqlServer.Smo.dll, Microsoft.SqlServer.SmoExtended.dll, Microsoft.SqlServer.SqlEnum.dll and Microsoft.SqlServer.SmoEnum.dll.

After that to Backup your Database follow this sample:

//Connect to the server
Server srv = new Server();
//If Sql Server is not local or is a named instance you could do 
//Server srv = new Server("SERVERNAME");

Database db = srv.Databases("YourDB");

//Create a backup definition
Backup backup = new Backup(); 

backup.Action = BackupActionType.Database; 
backup.BackupSetDescription = "Full backup of Adventureworks2008R2"; 
backup.BackupSetName = "My app Backup"; 
backup.Database = "YourDB"; 

//Configure the backup device

BackupDeviceItem backupDevice = new BackupDeviceItem("YourDB.bak", DeviceType.File); 

backup.Devices.Add(backupDevice); 

//Specify wether do a full backup or not
backup.Incremental = false; 

//Specify log truncation mode
backup.LogTruncation = BackupTruncateLogType.Truncate; 

//Do the backùp
backup.SqlBackup(srv); 

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.