Basic Backup and Restore for MS SQL Server 2000

Here is a simple tutorial for backup and restore in SQL Server 2000. This tutorial covers the basic procedures in creating backup and restoring databases. In this tutorial, we will be using Northwind Database as our example since it available on the sample databases of SQL Server

Backup

Before we backup our database, we have to create a logical backup device for our database.
On the query analyzer, make the database in use by selecting it on the drop down menu or by executing the code:


Use Northwind


Now we will create our logical backup device. Make sure the path exists when you execute this. If you execute this without errors it means that you are doing right.

EXEC sp_addumpdevice 'disk', 'NWINDBackup',
'c:\NorthwindSystems\Backup\NWind_backup.bak'
--NWINDBackup will be the name of our logical backup drive

After successful execution, you cannot run this code again since it already exists¹
Then we will now backup our database with the following code:

As I said(or typed :o)) earlier, you should make sure that the path you created exists².

BACKUP DATABASE Northwind TO NWINDBackup

Restore

Now we will restore are database based on the backup file we have created.

RESTORE DATABASE Northwind
FROM NWINDBackup

The syntax follows as:

RESTORE DATABASE [your db name]
FROM [logical drive]

Notes:
¹"Honestly, I haven't figured out yet how to alter that logical backup thing but please stay tuned for updates. You can also post some suggestions for this"-jereme

². Tip: Or at least tell your front end application to create folders if it does not exist.