CODEWITHSUNDEEP
sqlsql-server-2008database-restore
Mr A
Mr A

Reputation: 6768

Restoring the database.bak from local machine to the server

I am trying to restore 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.

Upvotes: 5

Views: 18869

Answers (3)

marc_s
marc_s

Reputation: 754240

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.

Upvotes: 12

kenwarner
kenwarner

Reputation: 29120

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

Upvotes: 3

Josh M.
Josh M.

Reputation: 27773

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.

Upvotes: 1

Related Questions