Restoring to standby server

Question

Restoring to standby server

Bev Christoffersen

SSC Rookie

Points: 37
More actions
April 29, 2005 at 2:26 am

#109390

I am trying to restore to a standby server but am getting the following error. Can anyone help me with this problem. I am new to SQL. Thankyou.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

Hi Bev,

We can't see the error you're getting, can you type it out rather than trying to post a screengrab and we'll see if we can help you.

Bev Christoffersen SSC Rookie Points: 37 More actions · Answer 2

Thank you.

Device activation error. The physical file name c:\data\sqldata\MSSQL\DATA\finpower_filename.mdf may be incorrect. File finpower_filename cannot be restored to c:data\sqldata\MSSQL\DATA etc use WITH MOVE to identify a valid location for the file.

File "filename_log cannot be restored to C:\data\sqldata|MSSQL\DATA etc, log_ldf may be incorrect.

RESTORE DATABASE is terminatling abnormally.

Jon-183221 SSCommitted Points: 1908 More actions · Answer 3

The destination location for the data and log file is different to where it orginated.

Does c:\data\sqldata\MSSQL\DATA\ exist on the stand by server?

If it doesnt you will have to MOVE the files to where you want them to go.

Through enterprise manager (against the stand by server) you can use - Restore Database - and check out the Options tab, this will allow you to move the files to their new respective locations.

You can also do this in QA qith the RESTORE DATABASE WITH MOVE command check it out in BOL.

Does this help?

Jon

Bev Christoffersen SSC Rookie Points: 37 More actions · Answer 4

Thank you for your reply. I will try out your suggestion in the morning when my brain feels a little fresher. I will let you know how I get on.

Bev Christoffersen SSC Rookie Points: 37 More actions · Answer 5

Still no luck. I have been trying to do the restore through enterprise manager. I did have the options tab checked for moving files.

I am not sure what QA is. Can you explain this for me please?

Could something be missing from my backup or is this likely to be a configeration issue?

Thanks

Kishore-132325 Hall of Fame Points: 3784 More actions · Answer 6

QA is Query Analyser, a tool which comes with SQL Server. You can write T-sql scripts using this tool.

Start -->Programs --->Microsoft Sql Server -->Query Analyser.

Connect using either Windows/SQL Authentication.

Then try out this command :

RESTORE DATABASE TestDB

FROM DISK = 'c:\Northwind.bak'

WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',

MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'

GO

Replace the database name and the path with yours.

Hope this helps.

--Kishore

Restore

Jon-183221 SSCommitted Points: 1908 More actions · Answer 7

Still no luck. I have been trying to do the restore through enterprise manager. I did have the options tab checked for moving files.

On the options check out the "Move to physical file name", the backup will probably be pointing to an invalid location. Change this to the location you want to restore the backup to, both for the Data and Log file. It should then restore.

I am not sure what QA is. Can you explain this for me please?

QA is Query Analyser, you can do everything that you can do in Enterprise Manager (and more) through QA but its done through code not a GUI.

Could something be missing from my backup or is this likely to be a configeration issue?

I think the problem is to do with the location of the files and not the backup. You can verify the backup by running a RESTORE VERIFYONLY FROM (backup file, e.g. c:\tmp\backup.bak), this will not restore anything just check the backup and make sure its valid. Do this from within QA.

Hope this makes some sense, let me know how it goes.

Jon

Bev Christoffersen SSC Rookie Points: 37 More actions · Answer 8

I have just resolved the problem. I took a full copy of the database and put it on the standby server. I then took a backup of the database and restored that to the standby server and it worked. Putting a full copy on to start with was obviously the step I had missed. Thanks for all the suggestions.