Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server 2008 running on a remote host (I bought it through a webhosting company).

My website is ready for testing, and I need to backup the database, so I can reset everything that I wrote in the tables, when testing is finished.

What I would prefer is to have a button in the administration site that can reset the database with the backup, when clicked. Is this possible?

How can I accomplish this with SQL Server Management Studio?

share|improve this question

migrated from stackoverflow.com Aug 15 '11 at 23:45

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers

First you'll need to know if you have the rights to backup and restore your database using SQL Server Management Studio. I'm guessing that you'll discover that you don't, unless you have a dedicated server. If you are in a shared hosting platform (other people are using the same SQL Server that you are) then I can pretty much guarantee that you won't have this ability.

Your hosting company should have a way for you to trigger a backup manually of your database via their control panel that they have provided. Failing that you may need to call them to have them start a backup.

If you do have the ability to do backups and restores (these are different permissions) then you can backup and restore the database using the BACKUP DATABASE and RESTORE DATABASE commands. Do keep in mind that restoring a database requires that you kick everyone out of the database. You'll also need to be VERY careful which backup you restore from as if you restore from the wrong backup, and don't have the correct backup available you've just lost all the data.

share|improve this answer
add comment

On your local copy of SSMS (if you can't RDP to the database server) just log in to the server\instance and your credentials. Once you are logged into the SQL Server then you can run a backup by right clicking on your database and under tasks -> backup... Once you do this you'll have the option to backup your database. Usually on public web servers/db servers there is a folder on that server you have access to. You'll need to backup your database to that folder and then use FTP to pull the .bak/.trn files to your local machine.

As for having this done automatically through an admin section on your website, it is absolutely possible. Backing up and restoring a database has many options tailored to your needs, but the basics should get you by. In your code-behind you can construct a dynamic SQL command to Backup your database. Then you can Restore your database as well.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.