Using OSQL to backup and restore a database
Added by Anthony Hernandez, last edited by Anthony Hernandez on Jan 08, 2008  (view change)
Labels:

## Logging in and Starting up OSQL

1. Enter the "DOS command line" mode of Windows by choosing Run from the Start menu and typing "command" into the dialog box.
2. At the DOS prompt, start OSQL and connect to your MSDE/SQL Server by typing:
OSQL -U sa -P admin -S (local)\METRIX
• sa is the username of an administrator for this MSDE/SQL Server installation
• admin is the password for that user (it will be admin unless that has been changed).
• (local) points to a server on the local computer. If you are accessing the server from a computer other than the one on which the server is installed, replace (local) with the name of the server.
3. This starts up OSQL and you can now begin using commands to work with your database(s).

### Backing-up a Database Using OSQL

To create a backup file of an existing database, use the following command:

BACKUP DATABASE dbname TO DISK = 'c:\path\to\backupfile.dat'
Go

Where:

• dbname is the name of the database you are backing up (by default this will be something like "METRIX")
• c:\path\to\backupfilename.dat is the full path to the backup file you are creating. Make a note of this location because you will need it later when you restore this file to the database server.

Note: If you do not know the name of your existing METRIX database, open METRIX using the shift-bypass technique and choose "Connection" from the File menu. The database name will appear in the "Select the database on the server..." dropdown box.

### Restoring a New Database Using OSQL

If you are creating a database that has never before existed on your instance of SQL/MSDE, you can use the following RESTORE command:

RESTORE DATABASE newdb FROM DISK = 'c:\path\to\backupfile.dat'
Go

### Restoring an Existing Database Using OSQL

If you are restoring a database that already exists on your instance of SQL/MSDE, you will need to enter the following command:

RESTORE DATABASE newdb FROM DISK = 'c:\path\to\backupfile.dat' WITH REPLACE
Go

If that does not work, then you can enter the following statement as one continuous string, but follow the capitalizations and the spacing in the example.

RESTORE DATABASE newdb FROM DISK = 'c:\path\to\backupfile.dat' WITH MOVE
'UpRunningSQL_dat' to 'c:\Program Files\Microsoft SQL
Server\MSSQL$METRIX\Data\METRIXV120copy.mdf', MOVE 'UpRunningSQL_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL$METRIX\Data\METRIXV120copy.ldf'

Where:

• c:\path\to\backupfile.dat is the full path to the file you created during the backup procedure above.
• 'c:\Program Files\Microsoft SQL Server...' is the path to your MSDE/SQL Server installation.
• METRIXV120copy.ldf is the new name of the log file for the database you created during the backup procedure above.
• METRIXV120copy.mdf is the new name of the data file for the database you created during the backup procedure above.

You can get the logical file names of the MDF and the LDF using:

RESTORE DATABASE m5 FROM DISK = 'D:\Metrix\2006 06 28 Go Live\20060628GoLive.dat' WITH MOVE 'METRIX 130' to
'c:\Program Files\Microsoft SQL Server\MSSQL$METRIX\Data\m5.mdf', MOVE 'METRIX 130_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL$METRIX\Data\m5.ldf'

or with:

RESTORE FILELISTONLY FROM DISK = 'c:\path\to\backup.dat'

# External Resources

While setting up Metrix on an instance of MSDE that was installed to the D drive, rather than C, we kept seeing errors when trying to restore the database in OSQL. As it turned out, the .mdf and .ldf files that are referred to in the above statement have different names.

These are the names that were used in this installation:

UpRunningSQL_dat became Metrix120.mdf
UpRunningSQL_log became Metrix120_log.ldf

Server\MSSQL$METRIX\Data\METRIXV120copy.mdf', MOVE 'Metrix120_log.ldf' to 'c:\Program Files\Microsoft SQL Server\MSSQL$METRIX\Data\METRIXV120copy.ldf'