Backup & Restore MS SQL 2000 Database

This is a short note I used at work to back up and restore the a MS SQL 2000 database.

I have a database with the name abcengine running on a local SQL Server. I need to back up this datbase to a file, send it over to a remote SQL Server to restore it.

There are two ways, which I’ve known, to back up a database. One of them is to use the Enterprise Manager to virsually back up, and another way is to use the Query Analyzer, writing the T-SQL code and run it.

In this case, I opted in to use the second option.

BACKUP DATABASE abcengine
	TO DISK = 'C:\tmp\abcengine.bak'

Execute the above code in the Query Analyzer, and if everything works well, we’ll get a backup file of the database, abcengine.bak stored in C:\tmp folder.

Now, send that backup file to the remote server people, so that they can restore it on their SQL Server.

Before we should run the restore command, we need to find out the name of the mdf and ldf files in the abcengine.bak. The mdf is the data file, and the ldf is the log file. The reason we need to check this is because usually two SQL servers tend to store the database files on different locations.

RESTORE FILELISTONLY
	FROM DISK = 'c:\location_you_keep\abcengine.bak'

We should see the names used in the abcengine database for mdf and ldf files. Now, we’re ready to restore this backup to a new database.

RESTORE DATABASE testdb
	FROM DISK = 'c:\location_you_keep\abcengine.bak'
	WITH MOVE 'abcengine_Data' TO 'c:\MSSQL2K\abcengine_2.mdf',
	MOVE 'abcengine_log' TO 'c:\Backup\abcengine_2.ldf',
	REPLACE

That’s it. It worked fine for me.

About these ads

2 thoughts on “Backup & Restore MS SQL 2000 Database

  1. This remind me 5 years ago which I had to work with MSDE and there is no EM or QA available. What I had to do is “sqli” to bring up SQL Interactive Shell where now of cause I do “sqlw” all the time.

    But yeah the approach is a bit different instead of backing up the data we just dettach and attach it back on the destination workstation.

  2. I found a tool that perfectly fits in this case, http://sqlbackupandftp.com creates the backup and save it for you in the remote site via your LAN or FTP. It can also compress and encrypt your file to increase security.

    Then, as you said, the remote server people, can easily restore it. Differential and Transaction Logs backups are also available in a scheduled backups.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s