February 20, 2008 at 2:48 pm
Hello,
Using the Management Studio GUI, I tried to restore a database backup along with transaction log backups for a few hours after the full backup. But I got an error like this one:
Error: The volume on device '[path]' is not part of a multiple family
media set. BACKUP WITH FORMAT can be used to form a new
media set. RESTORE DATABASE is terminating abnormally.
I ended up using Transact-SQL to do the restore. It was actually kind of neat to do a restore that way. But does anyone know what might cause the GUI method to fail with the above error?
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 20, 2008 at 9:59 pm
It looks like you performed a striped backup and then tried to restore one file. At least that's what it seems.
Did you restore the same files through T-SQL? I might actually Profile this from the GUI and see if there's something strange it submitted to the server.
February 21, 2008 at 10:54 am
Steve Jones - Editor (2/20/2008)
It looks like you performed a striped backup and then tried to restore one file. At least that's what it seems.Did you restore the same files through T-SQL? I might actually Profile this from the GUI and see if there's something strange it submitted to the server.
Thanks for your reply, Steve.
What is a striped backup? Sorry for my ignorance; I don't know this term or how it would impact a restore.
Regarding the T-SQL: Yes, I restored the same files - 1 bak file and about 10 trn files - using T-SQL. In the GUI I had tried to add the bak file and then the trn files in the dialog that pops up after selecting the "From device:" radio button and clicking the "..." button.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 21, 2008 at 11:06 am
You can perform a backup to multiple files, meaning that if you had 2 files, 1/2 the data is in 1 and 1/2 is in the other. You need both files to do the restore, but the backup runs quicker.
Did you select multiple files in the FROM dialog? I think you had the reverse problem. You cannot select multiple logs and expect SQL Server to sort them out (though I'm not sure why).
You need to use the GUI 11 times to do this restore, picking one file each time.
February 21, 2008 at 11:11 am
Steve Jones - Editor (2/21/2008)
You can perform a backup to multiple files, meaning that if you had 2 files, 1/2 the data is in 1 and 1/2 is in the other. You need both files to do the restore, but the backup runs quicker.Did you select multiple files in the FROM dialog? I think you had the reverse problem. You cannot select multiple logs and expect SQL Server to sort them out (though I'm not sure why).
You need to use the GUI 11 times to do this restore, picking one file each time.
Ahh, I see. Yes, your explanation makes sense. I definitely did not back up to multiple files. But I did try to restore multiple files at the same time via the GUI, which would explain why I got that error.
I think I will work on refining my T-SQL script, since it takes care of restoring all of the log files with one execution.
Thanks again!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 21, 2008 at 11:21 am
There are some scripts here on the site as well that might help you automate this.
Or post yours when it's done!
February 21, 2008 at 2:03 pm
Here's what I have so far. It is very crude, containing only what I needed to get the restore done. I know others probably have many improvements for purposes of automation, error checking, and so on.
If you could explain a little more about how STOPAT works, I would be very grateful. I don't really understand it that well.
Thanks!
webrunner
-- Restore the full database backup.
-- Leave the database in a recovering state so that transaction logs can be applied.
RESTORE DATABASE MyDB_Restored
FROM DISK = 'D:\Backup\MyDB\MyDB_backup_200802191805.bak'
WITH NORECOVERY,
MOVE 'MyDB' TO 'D:\MS SQL\MSSQL.1\MSSQL\Data\MyDB_Restored.mdf',
MOVE 'MyDB_log' TO 'D:\MS SQL\MSSQL.1\MSSQL\Data\MyDB_Restored_log.LDF'
-- Apply the transaction logs.
-- This "STOPAT" time happens to coincide with the last log backup time.
-- But apparently there is a way to specify any time within the range
-- of log backups selected in order to restore to that specific point in time.
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802191830.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802191930.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802192030.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802192130.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802192330.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802200030.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802200130.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802200230.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802200330.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802200430.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802200530.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802200630.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802200730.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
RESTORE LOG MyDB_Restored
FROM DISK = N'D:\Backup\MyDB\MyDB_backup_200802200830.trn'
WITH RECOVERY , STOPAT = N'02/20/2008 8:30:00 AM'
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 21, 2008 at 4:06 pm
STOPAT would only be for the last log and that's if you wanted to restore only to some time prior to the end of the last log.
All of these should be with NORECOVERY. RECOVERY would open the database and prevent more restores.
February 22, 2008 at 7:40 am
Thanks, I see. So using STOPAT would allow recovery to, say, 7:45 AM if the last log file contains transactions between 7:30 AM and 8:30 AM?
Also, regarding using NORECOVERY in the log restores, below are the results of the code I posted earlier, and it looks like the logs were restored even though I used RECOVERY instead of NORECOVERY. Am I misreading the results, or is there some other explanation?
Thanks again,
webrunner
-- Results of restore operation.
Processed 674944 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 454 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
RESTORE DATABASE successfully processed 675398 pages in 191.395 seconds (28.908 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 745 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 745 pages in 0.616 seconds (9.905 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 1544 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 1544 pages in 1.140 seconds (11.088 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 1068 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 1068 pages in 0.474 seconds (18.444 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 1466 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 1466 pages in 1.161 seconds (10.338 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 2758 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 2758 pages in 1.910 seconds (11.828 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 1072 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 1072 pages in 0.925 seconds (9.491 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 1003 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 1003 pages in 0.589 seconds (13.937 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 925 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 925 pages in 0.847 seconds (8.938 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 796 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 796 pages in 1.549 seconds (4.208 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 686 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 686 pages in 1.079 seconds (5.208 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 733 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 733 pages in 0.557 seconds (10.779 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 707 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 707 pages in 0.545 seconds (10.618 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 2662 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 2662 pages in 1.281 seconds (17.023 MB/sec).
Processed 0 pages for database 'MyDB_Restored', file 'MyDB' on file 1.
Processed 5341 pages for database 'MyDB_Restored', file 'MyDB_log' on file 1.
RESTORE LOG successfully processed 5341 pages in 1.808 seconds (24.199 MB/sec).
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply