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.
[sourcecode language=’sql’]
BACKUP DATABASE abcengine
TO DISK = ‘C:tmpabcengine.bak’
[/sourcecode]
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.
[sourcecode language=’sql’]
RESTORE FILELISTONLY
FROM DISK = ‘c:location_you_keepabcengine.bak’
[/sourcecode]
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.
[sourcecode language=’sql’]
RESTORE DATABASE testdb
FROM DISK = ‘c:location_you_keepabcengine.bak’
WITH MOVE ‘abcengine_Data’ TO ‘c:MSSQL2Kabcengine_2.mdf’,
MOVE ‘abcengine_log’ TO ‘c:Backupabcengine_2.ldf’,
REPLACE
[/sourcecode]
That’s it. It worked fine for me.