problem Restoring database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

problem Restoring database

Author

Topic

groadssql
Starting Member

9 Posts

Posted - 2010-12-07 : 17:57:29

I have two instances of sql2008 running on a server. I am trying to restore a backup from one to the other but I get an error. These are the steps I performed:

0) I connected to the first server instance
1) I created a new database on the sqlserver instance
2) I added a table to the database
3) added a couple of records to the table
4) Performed a fullbackup of the database
5) disconnected from the server instance
6) connected to the second sqlserver instance
7) Tried to restore the database from the backup of the first server instance on to the second server instance

I am trying to attach an image of the error but having trouble with that also, yes noobie here. The error states the database is being used by another process. Thanks in advance.

GAR

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-07 : 19:44:29

Could you post the exact text of the error?

It sounds like you just need to get exclusive to the database to be restored. You can use ALTER DATABASE to kill all connections quickly if that's the case .

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-07 : 21:21:11

What Tara said...or, the RESTORE is trying to overwrite the data files from the old location, which will generate an error too.

Make sure you're using the WITH MOVE option in your RESTORE command.

If it isn't that, or what Tara said, than as she said, post the text of the error message.

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 04:00:16

Make sure YOU are not connected to the database you are trying to restore!

USE master

will switch you to the Master database, and then restoring YOUR database should be possible.

lbseong
Starting Member

5 Posts

Posted - 2010-12-10 : 04:12:25

I believe you are trying to restore back to Instance 1 location. You may try this command :-

RESTORE DATABASE TestDB
FROM AdventureWorks2008R2Backups
WITH
MOVE 'AdventureWorks2008R2_Data' TO 'C:\MySQLServer\testdb.mdf',
MOVE 'AdventureWorks2008R2_Log' TO 'C:\MySQLServer\testdb.ldf';
GO

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-10 : 08:17:04

I'm voting for russel on this one!!

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-10 : 11:20:22

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources