[SQL 2005] Restore question

I trying to test my restore in SQL 2005. I created a DB called test_restore
and try to restore from backup but getting this error. I have tried using
"overwrite option" but the same. I also tried T-SQL statement with MOVE and
also tried using read-only DB but still the same .
Any idea?
*************************************************
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing
************************************************** ***
Are you trying to overwite a existing database or a new one?
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:

> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> ************************************************** ***
|||Hi i didnt read your question correctly...
I got the same error while trying to resotre to a created DB. So i tried
creating on a non existing DB it worked, try that way. Iam still checking on
how to overwrite the existing DB...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:

> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> ************************************************** ***
|||Yes, got the answer
Try this...
RESTORE DATABASE Test
FROM DISK = 'D:\Adv_Test.Bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
STATS = 1, REPLACE
GO
REPLACE is the key word while trying to overwrite existing DB.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:

> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> ************************************************** ***
|||hi Sreejit,
Thank you
Yes, i wanted to test the restore into another temporary db to test it.
Are you suggesting not to create the tempdb first and run the command below
straightaway? How does the db got created then?
By the way, i am restoring files and filegroups.Will this method work? Or,
do i need to specify all the filegroups names? There are long list..Any
better way? :-s
"Sreejith G" wrote:
[vbcol=seagreen]
> Yes, got the answer
> Try this...
>
> RESTORE DATABASE Test
> FROM DISK = 'D:\Adv_Test.Bak'
> WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
> STATS = 1, REPLACE
> GO
> REPLACE is the key word while trying to overwrite existing DB.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "rupart" wrote:
|||rupart wrote:
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
A RESTORE will create the database if it's not already there, so there
are no need to create it before a restore. The REPLACE option, is to
tell SQL server to overwrite an existing database with the same name as
the one that has been backed up. This is to prevent you from overwriting
a database by accident. You can read up on this in Books On Line.
Regards
Steen
|||hi guys,
thx a lot for your replies. I find out a few things:
1) Using GUI, to restore, click on "Database" after Instance Name, right
click, restore database or restore files and filegroup. Then, enter your db
name(or you choose your test db that you have created from drop down).
Choosing the test db directly and attempting to restore will give you error.
2) Using T-SQL.(files and filegrp to test db)
************************************************
USE master
Go
RESTORE DATABASE TestRestore
FROM DISK = 'X:\Test.bak'
WITH RECOVERY,
MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
GO
************************************************
"rupart" wrote:
[vbcol=seagreen]
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
> "Sreejith G" wrote:
|||rupart wrote:
> hi guys,
> thx a lot for your replies. I find out a few things:
> 1) Using GUI, to restore, click on "Database" after Instance Name, right
> click, restore database or restore files and filegroup. Then, enter your db
> name(or you choose your test db that you have created from drop down).
> Choosing the test db directly and attempting to restore will give you error.
> 2) Using T-SQL.(files and filegrp to test db)
> ************************************************
> USE master
> Go
> RESTORE DATABASE TestRestore
> FROM DISK = 'X:\Test.bak'
> WITH RECOVERY,
> MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
> MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
> MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
> MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
> GO
> ************************************************
>
Does this means that you've got it working or do you still have
problems? Your T-SQL will fail if the TestRestore database already
exists since you haven't supplied the REPLACE option.
Regards
Steen