August 9, 2015 at 1:22 pm
I've got an old version of SQL Server 2008 R2 Developer Edition on an old PC which is failing. I've got a new PC and have put SQL Server 2014 Developer Edition onto it. Now before the old machine completely dies, I've gotten into SSMS on the old machine and did a backup of the databases I want to save. I've moved the .BAK files to where I could get to them from SSMS on the new machine. I've gotten into SSMS and tried to do a restore the database to my new machine. However I'm getting an error that does not make any sense to me.
The database I'm I've backed up is named JobSearch. When I backed it up, that was the only database I had selected. Like I said I copied the .BAK to the new machine. Got into SSMS, told it that I wanted to restore the JobSearch database, telling it where I wanted to put it, and it then immediately fails with a:
"Restore of database 'JobSearch' failed.
System.Data.SqlClient.SqlError: Logical file 'VideoLibrary_Data' is not part of the database 'JobSearch'. Use RESTORE FILEISTONLY to list the logical file names."
Well of course VideoLibrary isn't "the logical file". But neither did I select VideoLibrary (which is a database I also want to move, but I'm doing one at a time). So what in heck is going on here? Why is it complaining about a database I haven't even selected to back up? Why, when I check everything on the old machine, it's backing up JobSearch, but on the new machine it sees VideoLibrary? I don't understand what's wrong nor how to fix it.
Rod
August 10, 2015 at 12:46 am
As suggested run restore filelistonly against the backup file and post back the results
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 10, 2015 at 2:45 am
The logical name of a file doesn't have to have anything to do with the name of the database. Run the restore that Perry suggested, look at the logical names of the files in the backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2015 at 6:56 am
please also run a RESTORE HEADERONLY and provide this info so that we may confirm the actual database name the backup references
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2015 at 7:01 am
Perry Whittle (8/10/2015)
As suggested run restore filelistonly against the backup file and post back the results
Here are the results of my doing a RESTORE FILELISTONLY against the backup file:
LogicalNamePhysicalNameTypeFileGroupNameSizeMaxSizeFileIdCreateLSNDropLSNUniqueIdReadOnlyLSNReadWriteLSNBackupSizeInBytesSourceBlockSizeFileGroupIdLogGroupGUIDDifferentialBaseLSNDifferentialBaseGUIDIsReadOnlyIsPresentTDEThumbprint
VideoLibrary_DataC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\VideoLibrary.MDFDPRIMARY62259203518437208064010000000000-0000-0000-0000-0000000000000057016325121NULL123000000055100072FDF932F9-3DDD-4B9F-86BE-6692527F5B3101NULL
VideoLibrary_LogC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\VideoLibrary_1.LDFLNULL186777603518437208064020000000000-0000-0000-0000-0000000000000005120NULL000000000-0000-0000-0000-00000000000001NULL
Rod
August 15, 2015 at 7:05 am
Perry Whittle (8/10/2015)
please also run a RESTORE HEADERONLY and provide this info so that we may confirm the actual database name the backup references
This one is more interesting. Here's the RESTORE HEADERONLY command I issued:
restore headeronly from disk='I:\SQL Backups\JobSearch.bak'
And here are the results:
BackupNameBackupDescriptionBackupTypeExpirationDateCompressedPositionDeviceTypeUserNameServerNameDatabaseNameDatabaseVersionDatabaseCreationDateBackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDateBackupFinishDateSortOrderCodePageUnicodeLocaleIdUnicodeComparisonStyleCompatibilityLevelSoftwareVendorIdSoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildMachineNameFlagsBindingIDRecoveryForkIDCollationFamilyGUIDHasBulkLoggedDataIsSnapshotIsReadOnlyIsSingleUserHasBackupChecksumsIsDamagedBeginsLogChainHasIncompleteMetaDataIsForceOfflineIsCopyOnlyFirstRecoveryForkIDForkPointLSNRecoveryModelDifferentialBaseLSNDifferentialBaseGUIDBackupTypeDescriptionBackupSetGUIDCompressedBackupSizeContainmentKeyAlgorithmEncryptorThumbprintEncryptorType
VideoLibrary-Full Database BackupNULL1NULL012saTARDISVideoLibrary6612009-10-23 22:49:32.00058368001230000000591000361230000000607000011230000000591000361230000000551000722015-01-09 17:09:20.0002015-01-09 17:09:21.000520103319660990460810502550TARDIS512BD03CC01-7CF5-49BC-B1C4-624AD856608F1A716D09-BF89-41C9-BF45-91D4B3A4AD63SQL_Latin1_General_CP1_CI_ASB659C1B4-B0AC-4F26-9BD7-C4867D380F7C00000000001A716D09-BF89-41C9-BF45-91D4B3A4AD63NULLFULLNULLNULLDatabase0C4FA6B9-E988-47EE-ABCB-297FF1254B6258368000NULLNULLNULL
JobSearch-Full Database BackupNULL1NULL022saTARDISJobSearch6612014-11-07 14:17:53.000217497613600000000610009013600000000960000113600000000610009002015-02-21 18:09:08.0002015-02-21 18:09:09.0005201033196609100460810502550TARDIS5124E06B776-6077-4008-B868-6E63A5E44EF1AFAF8A2E-A647-4C52-89D8-A64951046D1CSQL_Latin1_General_CP1_CI_ASAFAF8A2E-A647-4C52-89D8-A64951046D1C0000000000AFAF8A2E-A647-4C52-89D8-A64951046D1CNULLFULLNULLNULLDatabaseD4C9E38C-7F5C-430A-906F-B763A51543C621749760NULLNULLNULL
VideoLibrary-Full Database BackupNULL1NULL032saTARDISVideoLibrary6612009-10-23 22:49:32.00058357761230000000705000361230000000721000011230000000705000361230000000591000362015-03-11 13:17:41.0002015-03-11 13:17:43.000520103319660990460810502550TARDIS512BD03CC01-7CF5-49BC-B1C4-624AD856608F1A716D09-BF89-41C9-BF45-91D4B3A4AD63SQL_Latin1_General_CP1_CI_ASB659C1B4-B0AC-4F26-9BD7-C4867D380F7C00000000001A716D09-BF89-41C9-BF45-91D4B3A4AD63NULLFULLNULLNULLDatabase2816AA32-673F-44A6-B9CE-0A3A0CA1738558357760NULLNULLNULL
JobSearch-Full Database BackupNULL1NULL042saTARDISJobSearch6612014-11-07 14:17:53.00024371201360000000693000371360000000709000011360000000693000371360000000061000902015-08-09 12:36:31.0002015-08-09 12:36:31.0005201033196609100460810502550TARDIS5124E06B776-6077-4008-B868-6E63A5E44EF1AFAF8A2E-A647-4C52-89D8-A64951046D1CSQL_Latin1_General_CP1_CI_ASAFAF8A2E-A647-4C52-89D8-A64951046D1C0000000000AFAF8A2E-A647-4C52-89D8-A64951046D1CNULLFULLNULLNULLDatabase63B8B87D-8855-4796-8921-BCBCE166152624371200NULLNULLNULL
JobSearch-Full Database BackupNULL1NULL052saTARDISJobSearch6612014-11-07 14:17:53.00024371201360000000718000371360000000734000011360000000718000371360000000693000372015-08-09 13:10:00.0002015-08-09 13:10:01.0005201033196609100460810502550TARDIS5124E06B776-6077-4008-B868-6E63A5E44EF1AFAF8A2E-A647-4C52-89D8-A64951046D1CSQL_Latin1_General_CP1_CI_ASAFAF8A2E-A647-4C52-89D8-A64951046D1C0000000000AFAF8A2E-A647-4C52-89D8-A64951046D1CNULLFULLNULLNULLDatabase88EA329D-E90D-4F58-A93C-465BCA6ADD6B24371200NULLNULLNULL
JobSearch-Full Database BackupNULL1NULL062saTARDISJobSearch6612014-11-07 14:17:53.00024371201360000000743000371360000000759000011360000000743000371360000000718000372015-08-09 13:28:55.0002015-08-09 13:28:56.0005201033196609100460810502550TARDIS5124E06B776-6077-4008-B868-6E63A5E44EF1AFAF8A2E-A647-4C52-89D8-A64951046D1CSQL_Latin1_General_CP1_CI_ASAFAF8A2E-A647-4C52-89D8-A64951046D1C0000000000AFAF8A2E-A647-4C52-89D8-A64951046D1CNULLFULLNULLNULLDatabase762F4C03-5B41-4224-A42F-6E23626209CC24371200NULLNULLNULL
I have no idea why there are other backups of other databases in that!
Rod
August 15, 2015 at 7:27 am
OK I now see what I did wrong. I guess the default backup mechanism is to append to the backup set. What I really wanted, at least when moving the database, was to overwrite whatever else had been previously written there. Once I did that, it reduced the size of my .BAK to a tenth of what it was and the only thing in the .BAK was my JobSearch database. I easily restored that onto my new machine.
Thank you Perry and Gail!
Rod
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply