[RESOLVED] Restore MS SQL Databse from a backup file

[ComputerJy]

Hi everyone.
I'm not much of a database designer but I need to know how can I restore an MS SQL Database from a backed-up file.

Any help would be great. Thanks

[techgnome]

What version of SQL Server? 2000, 2005, SQL Express?

-tg

[ComputerJy]

2000 Sp3

[szlamany]

You can do it in Query analyzer with commands like this:

BACKUP DATABASE Acctfiles 
   TO DISK = 'c:\ACS DESKTOP\Acctfiles_D18.bak'
-- this does a backup
 
RESTORE FILELISTONLY 
   FROM DISK = 'c:\ACS DESKTOP\Acctfiles_D18.bak'
-- this displays the "files" in the backup
 
RESTORE DATABASE Acctfiles_D18
   FROM DISK = 'c:\ACS DESKTOP\Acctfiles_D18.bak'
   WITH MOVE 'Acctfiles_data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\Acctfiles_D18.mdf',
   MOVE 'Acctfiles_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\Acctfiles_D18.ldf'
-- this actually does a restore - you can restore to a different DB
-- name and specify the data and log locations
GO

Are you restoring a DB from backup to correct a problem or to move a production DB to another SERVER?

[ComputerJy]

Moving to another server.
Does it matter?

[szlamany]

Moving to another server.
Does it matter?

Then the SQL I gave you should be sufficient - we use this type of script to move DB's all over the place - from server to server - customer to development - test DB names...

The reason I asked is you should really "delete" the DB from the destination server before doing the RESTORE - you do not want to restore a DB into an existing DB - unless of course you are trying to recover from a production loss...