This article shows how to backup and restore databases in MSSQL from the application (Win Forms / ASP.NET) using scripts. This is done using executing sql queries so it is all done from sql server not from the application side. So when restoring and when taking backup from the database all the file be created and taken from the sql server side not from the application server or from the client machine.
The following codes shows how to take a backup from a database.sql query used:BACKUP DATABASE @DBName TO DISK = @PATH
*Make sure that you don’t use SqlTransaction to execute this query because it does not allow you to execute within a transaction.
The following codes shows how to restore a .bak file to a database.
First of before restoring a .bak file its better to see whether it is corrupted or not. Otherwise if you restore it to a live database without checking you will be getting in to big trouble.so it is good to check .bak file before restoring.The following code will show how to do that.sql query used:RESTORE VERIFYONLY FROM DISK = @PATH
After verifying the .bak file you can restore it to the database. Here it is done using a sql script. when resorting the database make sure that you are not restoring the .bak file to the same database your going to run the script to restore the database. So it is good to create the connection master database and run the sql script. Before restoring the database we have to kick all the user who are using the database otherwise sql server won’t allow to restore the database. Following code can be used to remove all the users :This will only allow one user to use the database.ALTER DATABASE ” + strDBName + ” SET Single_User WITH Rollback Immediate;After making it to single mode you can restore the database.RESTORE DATABASE ” +strDBName + ” FROM DISK = @PATH WITH REPLACE ;After executing restore script make sure you turn it back to multi user mode.ALTER DATABASE ” + strDBName + ” SET Multi_User ;