Wednesday, 15 April 2009

Configure MSDE: Installation/Server Setup, Database Creation/Backup/Restore/Deletion using OSQL

SERVER SETUP

Step 1: Download Microsoft SQL Server 2000 Desktop Engine Service Pack 3 file (MSDE2000A.exe) from
http://go.microsoft.com/fwlink/?linkID=14502

Step 2: Execute MSDE2000A.exe to extract the MSDE, it extracts to C:\MSDERelA by default

Step 3: Now open an MS-DOS command prompt, and change to the directory where MSDERelA file is extracted, now execute following command to set up the server.

Windows authentication with network access.
Setup.exe DISABLENETWORKPROTOCOLS=0 SAPWD=<password> <Press Enter>
Windows authentication without network access.
Setup.exe SAPWD= <password> <Press Enter>
Mixed Windows and SQL mode authentication with network access.
Setup.exe DISABLENETWORKPROTOCOLS=0 SAPWD=<password> SECURITYMODE=SQL <Press Enter>

Mixed Windows and SQL mode authentication without network access.
Setup.exe SAPWD=<password> SECURITYMODE=SQL <Press Enter>

Step 4: Now in installation window in the left pane double click “Named Pipes” and then “TCP/IP” and then click ok.

Step 5: Now go to Start>Control Panel>Administrative Tools>Services and right click MSSQLSERVER option in the right pane and press Start to start the service. Also make sure startup type should be “Automatic”.

CREATE DATABASE
Execute following command to create database

C:\>OSQL –U sa –P <password> –S <Press Enter>

1>use master
2>go
1>CREATE DATABASE <database name>
2>go

BACKUP DATABASE
Execute following command to backup database

C:\>OSQL –U sa –P <password> –S <Press Enter>

1>BACKUP DATABASE <database name> TO DISK=<backup file path>
2>go

RESTORE DATABASE
Execute following command to restore database

C:\>OSQL –U sa –P <password> –S <Press Enter>

1>RESTORE DATABASE <database name> FROM DISK=<backup file path>
2>go

DELETE DATABASE
Execute following command to delete database

C:\>OSQL –U sa –P <password> –S <Press Enter>

1>use master
2>go
1>DROP DATABASE <database name>
2>go

2 comments:

  1. This is very helpful and that it aids in software backup as well.

    ReplyDelete
  2. Hi,
    A fantastic read….very literate and informative. Many thanks….what theme is this you are using also

    ReplyDelete