Database Restore - backups not visible

Question

Database Restore - backups not visible

dave-dj

SSChampion

Points: 11297
More actions
February 11, 2010 at 6:22 am

#210862

I've taken a copy only backup of a database I want to restore on a test server.
the backup device I've used point to a share on the test server.
when I try restore the database in the SSMS GUI for the restore, I can select the database, but it doesn't give me the option to select anything to restore?? However, the backup is visible if I use RESTORE HEADERONLY?
Any clues as to why this is happening?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Viewing 15 posts - 1 through 15 (of 18 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Please try goggling on the error. I found various hits but I was not sure which scenario matched your situation.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

GTR SSCrazy Eights Points: 8007 More actions · Answer 2

Did you copy backup file form Prod to Test environment?

We had issues copying back up files from production to Dev environment. There is a way to restore Dev environment from Production share, you can ask Network Admins to open port one way to read Production backup fileshare from Dev Environment in firewall. So that you can restore Dev environment from production share itself.

EnjoY!

EnjoY!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I routinely used DTS Packaged to refresh the Development,Test & QA Environments.

I also wrote SSIS packaged to do the same on as routine basis. I would take the backup from the production environment and refresh the development & QA envionments. I maintained very strick source control of each enviroments so as I promoted from each level I was able to maintain integrity within each environment.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 4

That list will only show you backups for that database that were performed on that instance. Since the backup you are looking for was not performed on that instance, how is SQL Server supposed to know about it?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I know what backups were performed and when. You can query the system tables.

I used the file System object in addition to the system tables to verify existence of the file.

I copied the backup files to the development & QA Servers and I archived & deleted the backup files and I detemoine the most recent (correct) backup file in my program logic.

This worked for me without any issues at multiple client sites.

This is a no brainer, I'm not sure what your concern is?

MCDBA, MCSE, MCTP, MCSD, OCP

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

After examining your initial post it is clear that you approach is severely flawed.

I do not like to Criticize other members but I suggest that you Goggle this topic if you do not accept the advise that you have been provided.

Regards,

Welsh

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dave-dj SSChampion Points: 11297 More actions · Answer 7

Couple of things in reply to the posts above

1. I've tried googling the problem and can't seem to find any relevant results (maybe it's the search string I'm using), but I have no error messages to look up.... and hence my post

2. I can't see why the backup needs to know about the instance from which the database was backuped? I have backup up databases from SQL2000 and restored them on SQL2005. Is the point of a backup file not to have to rely on a single enviroment?

Let me ask the question a different way.....

Why does RESTORE HEADERONLY see the backup details, but the SSMS Restore GUI doesn't?

GT-897544 - I think your probably closest to what my problem might be, so I have a chat with my network guru

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Dave-DJ,

I can only speak for myself not someone else on the foeum but RESTORE HEADERONLY is frequently used by DBA's:

http://msdn.microsoft.com/en-us/library/ms178536.aspx

The SSMS GUI typically for convience or for those tyhat do not know whe commands.

Please feel free to consult with your "Network Guru" is you feel more comfortable with them.

Sorry that I did not answer your question correctly

Regards.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Dave Try this Link, I hope that it helps:

http://www.mssqltips.com/tutorial.asp?tutorial=105

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

This is the search criteria that I used in Advanced Google Seartc:

How to RESTORE HEADERONLY using SSMS

You can manipulate the abouve search to change the number of hits.

In my opinion, there are many instances when you get an error and it is often necessary to use Query Analyzer, etc. and various command to determine the Source of the problem.

Best of luck!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dave-dj SSChampion Points: 11297 More actions · Answer 11

thanks I'll check it out

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

GTR SSCrazy Eights Points: 8007 More actions · Answer 12

We encountered this problem before, that is why we are following this track now it saves lot of bandwidth on network side.

Note: Make sure you don't do this while you are running back ups( I mean both Read\Write same time to disk), This may slow down you back up process.

EnjoY!

EnjoY!

jjtetzlo SSCrazy Points: 2511 More actions · Answer 13

Backups taken with the copy_only command are not visible in an SSMS gui restore. I ran into this same issue recently. Is that what you're running into?

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/09/15/copy-only-backups-and-ssms.aspx

- Jeff

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

I saw that yesterday but I did not think to ask.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/