restore of a backed up database failes

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

SQL Server 2005 Forums

Transact-SQL (2005)

restore of a backed up database failes

Author

Topic

android.sm
Starting Member

36 Posts

Posted - 2010-11-29 : 05:57:01

i created a backup of a db called test. next, i created another database called testbackedup. however, i cannot use the below sql to do the restore. i created the backup from task / backup. the restore from task does not work - it complains the database is in use.

RESTORE DATABASE TestBackedUp 
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\Test.bak' 
with replace

Error:

RESTORE cannot process database 'TestBackedUp' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 06:08:46

It's because the dtabase is in use - probably by you.
You'll need to close (or move) all connections to the database.
try
select * from master..sysprocesses where dbid = db_id('TestBackedUp')

Any of your connections just move to another database - select a different database in the drop down list and execute a command and click on a different database in the left hand pane.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-29 : 06:09:14

Use master
GO
RESTORE DATABASE TestBackedUp
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\Test.bak'
with replace

Madhivanan

Failing to plan is Planning to fail

android.sm
Starting Member

36 Posts

Posted - 2010-11-29 : 06:13:56

select * from master..sysprocesses where dbid = db_id('TestBackedUp')

the above line executed and returned nothing. how do i close the connection?

I also tried using the master db - got errro:

The file 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Test.mdf' cannot be overwritten.  It is being used by database 'Test'.
Msg 3156, Level 16, State 4, Line 1

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 06:18:24

different error.
You are trying to restore the database to another one.
By default if tries to create/replace the database files with the same name as in the backup - your old dtabase is still there and using hte files.
You need to use the move option on the restore to create the logical files with different physical names..
To find the logical names you can look at the old database or do a header only restore of the backup file.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

android.sm
Starting Member

36 Posts

Posted - 2010-11-29 : 06:30:14

hi - sorry i don't know much about sql server and not sure how i do what u asked me to do. anyway - i used the same database name (test) and that seems to work.

i find it odd i can't restore my backed up database to a new database!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 06:43:44

You can - did you read my last post?
A database is a logical name in a server. The data for that database is held in (at least) two files a .mdf and a .ldf.
When you restore a database you create these two files.
when you restored over the database with the same name it deleted the old file sand created new ones.
When you tried to restore to a different name it tried to create the files but found that they were already in use by another database - the one you backed up.
To do this you need to specify a different name for the files - you do this via the move option in the restore command.
Have a look at the restore command in bol.

it will be something like

RESTORE DATABASE TestBackedUp
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\Test.bak'
with move 'test_data' to 'c:\test.mdf', 'test_log' to 'c:\test.mdf'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2010-11-29 : 07:06:49

Ins-and-Outs of using Restore to restore a backup to a different named database / different drives / different something else! explained here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

android.sm
Starting Member

36 Posts

Posted - 2010-11-29 : 07:15:48

thanks nigel for the explanation

makes sense and now it works!

thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources