This lesson is meant to help you backup and restore your SQL database. These instructions should help you move your data from another hosting company on to our servers, or periodically backup your data from our servers for safe keeping.
- Download and install the Microsoft SQL Server Database Publishing Wizard from http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
- Startup the Wizard and enter your connection information for the database you want to backup. You should only backup the following object:
Stored Procedures
Tables
User-Defined Functions
Views
The wizard is pretty self explanatory. The only bit of information you'll need to know is that you are scripting the database, we don't have the web service it talks about setup here. When you are done, you should end up with an sql text file on your hard drive.
- If you don't have it already, download Microsoft SQL Server Management Studio Express (free) from Microsoft at: http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
- Connect to your database using Microsoft SQL Server Management Studio Express. When you connect you won't be able to see any databases in the list, even the one you own. But, you can run a script against it. If you need to manage the database directly, you may be interested in SQL Manager 2008 Lite for SQL Server which, as the name implies, is free.
- Load the SQL text file you created in step 2.
- If you will be restoring the database to a database of a different schema (user name), replace the old schema name with an empty string in the SQL script. That is, if your scripts read [schema].[tablename], change them to read, just [tablename].
- Put a use database statement on the first line, followed by GO so that the script will execute against the database you want to restore to. Ie,
Use myDatabase
GO
…
- Execute the SQL. If you get an out of memory error, you can block sections of the SQL and execute a section at a time. Just make sure you end on a GO statement.
- Using this procedure, I was able to script out a DotNetNuke database from one server and put it on a new server.
For those of you who want a more automated process. I would strongly recommend you try Handy Backup. For as little as $30 you can get a program that will backup files from an FTP site to your local drive, backup your ODBC compliant (remote) database to a file as well as allowing you to backup local files to an FTP location.