What are the challenges you face when working across database platforms? Take the survey
Options

Restore detached databases, & files not local to a SQLServer

SPROBINSONSPROBINSON Posts: 17
edited February 12, 2006 6:58PM in SQL Backup Previous Versions
I'm trying to restore a database that has been detached. Since it's detached I can't browse to the backup in the history browser (a silly limitation if you ask me but I guess that's Microsoft not Red-Gate removing it from msdb?).

I try to Browse to the backup files so I can restore them, but SQL Backup 4 only lets me browse file systems on registered SQL Servers. And it only lets me browse physical drives, not network drives. Wouldn't it make sense here to let me browse to any valid UNC path?

So how do I restore from, for example, network attached storage (which is where we keep all our backups) when the database is detached?

In SQL Server I would do this using RESTORE WITH REPLACE. Assuming I can get to the backup file (see above), will Red-Gate SQL Backup 4.0 let me restore a database that is detached? Is this where 'Scripts' come in - do I need to edit the Restore Script T-SQL before executing?

Anyway I'm just posting this for the benefit of others. I need to figure it out one way or another tonight, because I've taken an RG Backup of 2 databases, detached them and deleted the files! (Space is critical on the server I'm rescuing).

Hope I didn't just make a bad situation worse...

:?

Comments

  • Options
    Aha. In the Add input field, I can enter a full UNC spec that is valid from the Server, NOT from the Client. I guess this makes sense. Of course, since the restore job runs on the server machine, the path and drive letter etc must be as seen from the server machine.

    In fact it would be difficult for you guys to program a UNC browser in the client that runs from the Server machine's context.

    Maybe you could put a helpful hint on the screen there, especially if the RG Client detects it is not running on the target server, to say that the path must be as seen from the context of the target SQL Server machine.

    Sorry I probably didn't make it clear I was running the RGBackup Client on the NAS machine where the backup files live, which is not an SQL Server machine.

    Also to answer my question about restoring detached databases:

    I can see that in step 3 of Restore (once I've actually got a path to a valid .SQB file!!!), as well as a drop down of attached databases, I can manually enter a database name. I can also specify database file locations ("WITH MOVE") and Foce ("REPLACE") if necessary.
  • Options
    peteypetey Posts: 2,358 New member
    (a silly limitation if you ask me but I guess that's Microsoft not Red-Gate removing it from msdb?).

    SQL Backup is reading the list of databases from the master..sysdatabases table, not from the msdb..backupset. It will take significantly longer to read the list of unique database names from a large msdb..backupset table.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    OK makes sense - many thanks for the explanation. Maybe one day the Backup client could have an option to retrieve the full list of "select distinct database from backupsets" sort of thing.
Sign In or Register to comment.