How do I run that database backup and restore scripts from my winform application?

Question

I am developing a small business application which uses Sqlserver 2005 database.

Platform: .Net framework 3.5; Application type: windows application; Language: C#

Question:

I need to take and restore the backup from my application. I have the required script generated from SSME.

How do I run that particular script (or scripts) from my winform application?

Answer 1

You can run these scripts the same way you run a query, only you don't connect to the database you want to restore, you connect to master instead.

Answer 2

If the machine where your application is running has the SQL Server client tools installed, you can use sqlcmd.

Answer 3

If you want to do it programatically you can use SMO

Tutorial

Answer 4

Just use your connection to the database (ADO I presume?) and send your plain TSQL instructions to the server through this connection.

Answer 5

For the backup you probably want to use xp_sqlmaint. It has the handy ability to remove old backups, and it creates a nice log file. You can call it via something like: EXECUTE master.dbo.xp_sqlmaint N''-S "[ServerName]" [ServerLogonDetails] -D [DatabaseName] -Rpt "[BackupArchive]\BackupLog.txt" [RptExpirationSchedule] -CkDB -BkUpDB "[BackupArchive]" -BkUpMedia DISK [BakExpirationSchedule]''

(replace the [square brackets] with suitable values).

Also for the backup you may need to backup the transaction log. Something like: IF DATABASEPROPERTYEX((SELECT db_name(dbid) FROM master..sysprocesses WHERE spid=@@SPID), ''Recovery'') <> ''SIMPLE'' EXECUTE master.dbo.xp_sqlmaint N'' -S "[ServerName]" [ServerLogonDetails] -D [DatabaseName] -Rpt "[BackupArchive]\BackupLog_TRN.txt" [RptExpirationSchedule] -BkUpLog "[BackupArchive]" -BkExt TRN -BkUpMedia DISK [BakExpirationSchedule]''

I'd recommend storing the actual commands you're using in a database table (1 row per command) and use some sort of template replacement scheme to handle the configurable values. This would allow for easy changes to the commands, without needing to deploy new code.

For the restore you will need to kill all connections except for internal sql server ones. Basically take the results of "exec sp_who" and for rows that match on dbname, and have a status that is not "background", and a cmd that is not one of "SIGNAL HANDLER", "LOCK MONITOR", "LAZY WRITER", "LOG WRITER", "CHECKPOINT SLEEP" do a "kill" on the spid (eg: ExecuteNonQuery("kill 1283")).

You'll want to trap and ignore any exceptions from the KILL command. There's nothing you can do about them. If the restore cannot proceed because of existing connections it will raise an error.

One danger with killing connections is ADO's connection pool (more for asp.net apps than windows apps). ADO assumes the a connection fetched from the connection pool is valid... and it does not react well to connections that have been killed. The next operation that occurs on that connection will fail. I can't recall the error... you might be able to trap just that specific error and handle it... also with 3.5 I think you can flush the connection pool (so... trap the error, flush the connection pool, open the connection, try the command again... ugly but might be doable).