Restore one file from a backup device
-
Hi all.
I frequently restore the hourly backup of our live database to a test area to have fresh data to test with.
Our test server cannot see the backup devices on the live server, so in the past I have simply chosen "restore from file" and there's no problem.
However, I went away on holidays and came back to find that our network guys have now combined the hourly backups for 3 of our databases into the one hourly backup. Where it used to contain just the .mdf and .ldf I need to restore, it now contains two .ndf files for other databases.
Since restoring from file will not allow you to select the specific file in the group that you wish to restore, I am assuming I now need to either write syntax to do the restore with the variables I need, or alternatively figure out how to access the backup device rather than the file, and it will allow me to choose which files within that device I wish to restore.
Problem is, as I said, I cannot see the backup devices on the live server from the test server. Does anyone know how to influence this and be able to see backup devices that you can't currently which are sitting in other locations?
-
What type of backups are the hourly backups? Full, differential, log?
If I understand, you now have backup sets for three databases in one backup file. You can choose which backup sets to restore if you use T-SQL RESTORE statements. What's returned when you run RESTORE HEADERONLY for the backup file in a query window?
Greg
-
Thanks for the questions Greg.
Running RESTORE HEADERONLY for the backup set returns what I would expect. Database name, etc. all look the same.
I've also run RESTORE FILELISTONLY which shows me a PRIMARY file ending in .mdf, and two other files ending in .ndf with the appropriate naming conventions for the databases they match up to, plus the .ldf file, last in the list, which matches the name of the primary .mdf file with _log appended to it.
All logical names are as expected, are as filenames and physical locations/names.
The database we're trying to restore to is in Full recovery mode, as are the ones the backup comes from, but changing this to simple recovery does not seem to actually make a difference at this point.
I am now trying to run the following on the server I want to restore the database to. The database already exists there, so have thrown in a replace, and have experimented with various commands in the WITH statement, to no avail. I have also tried referring to the TO portion of the MOVE with both the physical and logical locations of the db we're restoring to, also to no avail in either case.
Names below changed to generic to protect the innocent... when I run the following, or any iteration with various WITH clauses so far, I get the resulting error message as displayed below:
RESTORE DATABASE [RestoreDB]
FILE = 'backupDB_data',
FILE = 'backupDB_log',
FILEGROUP = 'PRIMARY'
FROM DISK = '\\server\data\Databases\SQL\backupDBHourly.bak'
WITH MOVE 'backupDB_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RestoreDB.mdf',
MOVE 'backupDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RestoreDB_log.ldf',
REPLACE
GO
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'RestoreDB' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
We have noticed one difference between this database and another test database that we WERE able to restore to in this way: The one we could restore to had logical filenames that matched the logical filenames of the database we were backing up from, e.g. backupDB_data, even though the filename of the restored database was its own, e.g. RestoreDB, etc.
I have tried going into the properties and changing the logical names for the mdf and log files for the RestoreDB to the same as the backupDB, but that also has no effect on successfully restoring the db, using the commands above.
Any thoughts?
-
If you're restoring over an existing database and it has the same logical files, you don't need to use MOVE, the replace will replace the existing files.
Does the backup device show you a media name? If so, you could add that to the restore command.
--------------------
Colt 45 - the original point and click interface
-
That's the problem, it does not have the same logical file names, but when trying to use MOVE it gets the message as above.
I have got around it for now by restoring one of our other test databases that does have the same logical filenames to that database name, and then doing the restore, but I'm still curious as to why the MOVE isn't working.
-
Are you able to post the output from RESTORE HEADERONLY and RESTORE FILELISTONLY?
--------------------
Colt 45 - the original point and click interface -
I'd rather not post specific details about our servers.
If there's something specifically you are looking for, I can check it out.
The logical filenames for the backup are as I'd expect, and are named as the primary .mdf logical files.
The database list has one .mdf on PRIMARY and two other .ndfs and an .ldf.
-
You can change the names to protect the innocent. 😉
--------------------
Colt 45 - the original point and click interface -
I could change the names, but it seems an awful lot of trouble to do just to post what basically amounts to the properties of the backup file. I can see the problem is not with anything about these items.
I've got around the problem for now, so that will do.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply