How to make a restore for a MSDE database?

 

Open a DOS Windows

Go to the C:\MSSQL7\BINN directory

Type the command:

OSQL –U sa

Press <Enter>

You will be prompted to enter a Password:

Press <Enter>

You will receive a command line as follow:

1>

Write the command:

1> Restore database msdb from disk='c:\mssql7\backup\msdb.bak'

Press <Enter>, you will receive a new command line:

2>

Write the command:

2> go

Press <Enter>

 

 

You will receive a message if the command is successful and the command line will come back to:

1>

Type the following command to quit:

1> Exit

Press <Enter>

You will come back to the prompt:

C:\MSSQL7\BINN

 

You have then to restart the service.

 

The RESTORE command can be run in one step with the following syntax:

 

OSQL –U sa –P "" –Q " restore database msdb from disk='c:\mssql7\backup\msdb.bak' "

 

 

Caution 1:

 

Be careful: if you have used the "master" database to create or save your tables, you can make a backup of the "master" database as explained above.

But if you want to restore that backup, you will receive a message saying that, to restore the "master" database, you must do it using a single user mode.

 

To do so, stop the SQL Server service and start it again with the "–m" parameter.

 

Under Windows 98 you can create a shortcut to the program:

 

C:\MSSQL7\Binn\sqlservr.exe –m

 

Under Windows NT/2000 you can start manually the Service adding the parameter –m

 

Once the service is started in single-user mode you can restore the "master" database using the restore command as explained before.

 

Caution 2:

 

If the backup concerns the "master" database, it is recommended to restore that database on the same machine or on another machine having the MSSQL7 directory on the same disk and location. It is also recommended to backup and restore the other databases such as "model".

The reason is that the "master" database makes reference to the other database, such as "model" for example.

 

So, if you restore only the "master" database on a machine where MSSQL7 is on drive E: and the backup has been done on a machine where MSSQL7 was on drive C:, you will get problem when starting the Service because the "master" database will look for the "model" database on drive C:.