Backup & Restore - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Backup & Restore

Author

Topic

madnandurrani
Starting Member

4 Posts

Posted - 2003-07-14 : 14:26:24

Hi all,
I am having a problem with restoring the database, I want to restore an existing data base on other Machine, But when i do copy of main server's backup file to the other server, and then try to restore it, then it gives an error like "The backup is not taken with the data base where u restoring", I understood the meaning of that error but i don't know that how to fix it.

Basically i want to take backup of my SQL server, Currently i am taking physical back off Data files, now i need to take hot backup even i can take backup successfully, but when i simulate to restore it on different machine, it gives same error which i mentioned above.

Could some one help me please, Your help would be appreciated.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-14 : 14:28:17

Please post the exact error message as the error that you have posted is not the exact error. How are you performing the RESTORE? Through Query Analyzer or Enterprise Manager?

Tara

madnandurrani
Starting Member

4 Posts

Posted - 2003-07-14 : 15:02:37

Thanks tara,
I am using Enterprise manager for restoring, If i am selecting "From Device" from Restore page, then i am getting the following error.
"The backup set in file 'C:\test\adnantest' was created by BACKUP DATABASE ... FILE=<name> and cannot be used for this restore operation.
RESTORE DATABASE is terminating abnormally."

If i select file/group or database from restore page, then i am getting

"Cannot find file ID 2 on Device 'c:\test\adnantest'
RESTORE DATABASE is terminating abnormally"

above "adnantest" is the bak file name, where i took the backup.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 15:19:18

Why not do it from qa with a script( i think i did it onve through em)


ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
		
		RESTORE DATABASE TaxReconDB_Prod
		   FROM DISK = 'D:\...your location\*.dmp'
		   WITH   MOVE 'something_Data' TO 'D:\db mdf location\*.MDF' 
		        , MOVE 'something_Log'  TO 'D:\db ldf location\*.LDF'
			, REPLACE
[

Or something like that

Brett

8-)

madnandurrani
Starting Member

4 Posts

Posted - 2003-07-14 : 15:43:48

Hi Brett,
i used that query from Query Analayzer with all my files and locations, even i got same error message again.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 16:27:01

OK, What happens when you do:



RESTORE  FILELISTONLY   
	FROM DISK = 'D:\...your dmp location\*.dmp'

?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-14 : 16:34:04

After you click "From Device", what do you select next? You need to click select devices, then click add, then click the button with the dots (...), then navigate to the backup file. Are you able to navigate and select the file? Do you still get an error when you do this?

Doesn't your backup file have an extension on it like BAK? You say that adnantest is the bak file name, but isn't it adnantest.bak?

I would suggest using Query Analyzer as well:

RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\BackupFileName.bak'
WITH REPLACE, STATS

Just replace DBNameGoesHere with the name of the database and modify the path and file name as needed.

Tara

madnandurrani
Starting Member

4 Posts

Posted - 2003-07-14 : 17:12:49

hi Tara & Brett,

Thanks for your prompt reply, i really appreciate it, and you know that, i can restore my backup now, just because both of you, you both came on same point, now i am using restore command, its working,

thanks both of you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources