Restoring the database.bak from local machine to the server

I am trying to re开发者_运维问答store the whole db with diagrams nd foreigns keys to the existing database on the server i want to replace that with the new one , I tried the following script with no success

drop database  DuxburyCaravans    
go
RESTORE DATABASE stonestore
  FROM DISK = 'C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\DuxburyCaravans.BAK'
  WITH MOVE 'DuxburyCaravans' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DuxburyCaravans.mdf',
  MOVE 'DuxburyCaravans_log' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DuxburyCaravans.LDF'

but it comes up with errors:

Msg 3702, Level 16, State 3, Line 3

Cannot drop database "DuxburyCaravans" because it is currently in use.

Msg 3201, Level 16, State 2, Line 1

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\DuxburyCaravans.BAK'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

The RESTORE only works on the actual server machine - is this your own PC, or is it a separate machine??

If it's a separate machine: you cannot restore a database onto a remote server from your local harddisk - you need to put the *.bak file onto a drive that the server can reach - e.g. the server's own local drives, or a network drive that the server has a mapping (and access) to.

The error states that database DuxburyCaravans is in use. You can close existing connections in SQL by changing to SINGLE_USER mode

ALTER DATABASE [DuxburyCaravans] SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE [DuxburyCaravans] FROM DISK = N'C:\Path\To\Backup.bak'
ALTER DATABASE [DuxburyCaravans] SET MULTI_USER

You first need to either:

• Close all connections to the database you're trying to restore, or
• Using MS SQL Management Studio, connect to the remote server. Right-click on the root server node and choose Activity Monitor. Expand Processes, right-click on each process that is using your database and choose Kill. Do this with caution, of course.

Now that all connections have been closed, you can restore your database.