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:
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. Make sure you click the "Options>>" button in the lower left corner when you connect so that you can specify your database name in the "Connection Properties" tab of the login dialog. You won't be able to browse any of the databases, even yours, if you just connect without specifying your database.
- 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,
- 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.
You can also use the Database Publishing wizard to create scheduled backups of your database by running it from the command line on your computer:
"C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe" script -S SERVERNAME -d DBNAME C:\output.txt -f
Where -S --> Servername
-d --> Database Name
-f --> Overwrite the existing file