How to get a backup of your database from our SQL Server

Visual Studio .NET 2008 includes a built-in database management tool which will allow you to connect directly to the SQL Server and run a script to backup your database. The following functions are available with this tool:

• Retrieve and edit data
• Create tables, views, and stored procedures (Enterprise & Architect Version only)

To connect to your SQL Server database with Visual Studio .NET, follow the steps listed below.

1. Open Server Explorer (View -> Server Explorer); With VWD 2008 Express it will be Database Explorer.
2. The Server Explorer should show up on the left side of the IDE
3. Right click on "Data Connections" and select Add Connection.
4. Enter the Server Name in the first textbox (do not use the dropdown box). MS SQL Server name can be found in the SQL Manager in your Site Info Manager in Control Panel.
5. Check Use SQL Server Authentication
6. Enter your SQL username & password
7. Select your database from the dropdown list.
8. Click Ok

Once connected, you should see a new entry in the Data Connections Section.

To generate the script that will create the backup of your database follow the steps listed below.

1. Right click on your database and choose Publish to Provider.
2. Click Next on the welcome screen of Publishing Wizard.
3. Insure that your database is selected and check mark "Script all objects in the selected database" and click Next.
4. Choose the "Script to file" option and type the name of the script you will be generating. Click Next.
5. Leave the default Publishing Options and click Next.
6. Click Finish.

To run the script that will restore your database to your local SQL Server it will be much simpler to use SQL Server Management Studio Express or SQL Server 2008. Follow the steps listed below to restore the database to your local SQL Server.

1. Open your database SQL Server Management Studio.
2. Click on File/Open/File and navigate to the SQL script you just created from the SQL Server.
3. Once the script is opened navigate to the very top of the page and type:
   
   USE [Local database name]
   GO
   (Insure that the bracket symbols are also deleted.)​
4. Click on the Execute button.

Note: If you have SQL Server Management Studio and Visual Studio installed on your computer then Database Publishing Wizard will also be available. However, if you only have Visual Studio, you may need to go into Microsoft's website and download the "Database Publishing Wizard" component so that you can use it with Visual Studio.

 

Whats the best practice for overwriting a database?

 

What do you mean by 'overwriting a database'? Who's database are you referring to? The database on our SQL Server or the database on your computer?

 

I mean on my computer ....

Scenario ...

On my development computer I have local_databasename.
On your server I have db67345673_databasename.

So I run the scripts.

But when open local_databasename, change the using, I get all sorts of errors.

Right now to get it to work, I rename my local_databasename to local_databasename_Saved, and then add a new one, and run the scripts. I still get errors, but from my testing nothing seems to not work.

Is this the correct procedure?

What would I do, if I was to have to restore to the Server? What would you recomend?

 

Whats the error you are getting? Are you running the script against your own databse on your computer?

 

A database backup tool is available now in Control Panel.