 | |
2nd Post: MSDN Subscriber - Sql Server 2005 Restore from tape fails ...
microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools
 |
| | Thread Tools | Display Modes |
#41
 
| |||
| |||
|
Re: 2nd Post: MSDN Subscriber - Sql Server 2005 Restore from tapefails ... -
11-26-2008
, 12:06 AM
|
Geoff, You and Jeff are exactly correct. After following Jeff's advice and executing a RESTORE FILELISTONLY I realized that the logical file names on the tapes did not have the .mdf / .ldf suffixes. I removed the suffixes and the RESTORE worked great! I had been trying to work from the SMS Backup Device Media Contents list. Many thanks to both you and Jeff. Your time and patience are very much appreciated !! ===== The successful restore renamed database script ==== RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO Barry in Oregon "Geoff N. Hiten" <SQLCraftsman (AT) gmail (DOT) com> wrote in message news:uPMcW5zTJHA.2136 (AT) TK2MSFTNGP04 (DOT) phx.gbl... SQL uses logical file names that are separate from physical file names. You are using the physical file name instead of the logical file name in the "WITH MOVE clause. The ".mdf" extension is a physical name artifact. Use the logical name and supply a physical destination and everything will work just fine. -- Geoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "frostbb" <frostbb (AT) newsgroups (DOT) nospam> wrote in message news:eDHMEdxTJHA.4456 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Jeff, Thanks for the quick response. Its very much appreciated. Unfortunately, I'm confused about what you're saying specially about the part about you can have the same logical file names pointing to different databases. Also unfortunately, I don't want to restore over the existing "sec" database. I want to restore a >> copy of << the "sec" database so that I can retrieve some lost information from the "sec" copy and put it back into the existing production "sec" database. I obviously don't understand the mechanics of restoring a database from tape as opposed to restoring from a set of .bak files on the hard drive. And this is NOT something I can screw up. Overwriting the existing production sec db is definitely off the table. Anyway, thanks again for the effort. Its very much appreciated. However, I think it's time to burn one of my "MSDN life line calls"  Best wishes !! Barry in Oregon "Jeffrey Williams" <jeff.williams3188 (AT) verizon (DOT) ent> wrote in message news:%23zHIJ8qTJHA.5268 (AT) TK2MSFTNGP04 (DOT) phx.gbl... The error message says it all: Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. This message is telling you that you cannot MOVE the database with a logical file name 'sec_2008-11-08.mdf'. Modify the MOVE statement to reference the actual logical file name that is defined for that backup. As the error message states - Use RESTORE FILELISTONLY to identify the actual logical file names for the database you are restoring. Note: you cannot rename or modify the logical names until after the database has been restored (and, you can have the same logical file names pointing to different databases). Jeff frostbb wrote: We're using Sql Server 2005, Win2003 and writing backups to an HP Ultrium2 tape drive. I can get a File List from the tape with no problems. I'm backing up 14 individual databases to the tape. I want to create a copy of the "sec" database that has a full backup in position #11 on the tape. I want to recover the "sec" database as "sec_2008-11-24". I've successfully created this type of restore many times when the .bak file is physically on the server harddrive. However, when I use the following script I get the error message listed below the script. ANY HELP OR SUGGESTIONS WOULD BE GREATLY APPRECIATED !! Thanks in advance ! Barry in Oregon == RESTORE SCRIPT == RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001.mdf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001.ldf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO == RESULTING ERROR MESSAGE == Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. |
#42
| |||
| |||
|
|
Geoff, You and Jeff are exactly correct. After following Jeff's advice and executing a RESTORE FILELISTONLY I realized that the logical file names on the tapes did not have the .mdf / .ldf suffixes. I removed the suffixes and the RESTORE worked great! I had been trying to work from the SMS Backup Device Media Contents list. Many thanks to both you and Jeff. Your time and patience are very much appreciated !! ===== The successful restore renamed database script ==== RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO Barry in Oregon "Geoff N. Hiten" <SQLCraftsman (AT) gmail (DOT) com> wrote in message news:uPMcW5zTJHA.2136 (AT) TK2MSFTNGP04 (DOT) phx.gbl... SQL uses logical file names that are separate from physical file names. You are using the physical file name instead of the logical file name in the "WITH MOVE clause. The ".mdf" extension is a physical name artifact. Use the logical name and supply a physical destination and everything will work just fine. -- Geoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "frostbb" <frostbb (AT) newsgroups (DOT) nospam> wrote in message news:eDHMEdxTJHA.4456 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Jeff, Thanks for the quick response. Its very much appreciated. Unfortunately, I'm confused about what you're saying specially about the part about you can have the same logical file names pointing to different databases. Also unfortunately, I don't want to restore over the existing "sec" database. I want to restore a >> copy of << the "sec" database so that I can retrieve some lost information from the "sec" copy and put it back into the existing production "sec" database. I obviously don't understand the mechanics of restoring a database from tape as opposed to restoring from a set of .bak files on the hard drive. And this is NOT something I can screw up. Overwriting the existing production sec db is definitely off the table. Anyway, thanks again for the effort. Its very much appreciated. However, I think it's time to burn one of my "MSDN life line calls" Best wishes !! Barry in Oregon "Jeffrey Williams" <jeff.williams3188 (AT) verizon (DOT) ent> wrote in message news:%23zHIJ8qTJHA.5268 (AT) TK2MSFTNGP04 (DOT) phx.gbl... The error message says it all: Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. This message is telling you that you cannot MOVE the database with a logical file name 'sec_2008-11-08.mdf'. Modify the MOVE statement to reference the actual logical file name that is defined for that backup. As the error message states - Use RESTORE FILELISTONLY to identify the actual logical file names for the database you are restoring. Note: you cannot rename or modify the logical names until after the database has been restored (and, you can have the same logical file names pointing to different databases). Jeff frostbb wrote: We're using Sql Server 2005, Win2003 and writing backups to an HP Ultrium2 tape drive. I can get a File List from the tape with no problems. I'm backing up 14 individual databases to the tape. I want to create a copy of the "sec" database that has a full backup in position #11 on the tape. I want to recover the "sec" database as "sec_2008-11-24". I've successfully created this type of restore many times when the .bak file is physically on the server harddrive. However, when I use the following script I get the error message listed below the script. ANY HELP OR SUGGESTIONS WOULD BE GREATLY APPRECIATED !! Thanks in advance ! Barry in Oregon == RESTORE SCRIPT == RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001.mdf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001.ldf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO == RESULTING ERROR MESSAGE == Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. |
#43
| |||
| |||
|
|
Geoff, You and Jeff are exactly correct. After following Jeff's advice and executing a RESTORE FILELISTONLY I realized that the logical file names on the tapes did not have the .mdf / .ldf suffixes. I removed the suffixes and the RESTORE worked great! I had been trying to work from the SMS Backup Device Media Contents list. Many thanks to both you and Jeff. Your time and patience are very much appreciated !! ===== The successful restore renamed database script ==== RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO Barry in Oregon "Geoff N. Hiten" <SQLCraftsman (AT) gmail (DOT) com> wrote in message news:uPMcW5zTJHA.2136 (AT) TK2MSFTNGP04 (DOT) phx.gbl... SQL uses logical file names that are separate from physical file names. You are using the physical file name instead of the logical file name in the "WITH MOVE clause. The ".mdf" extension is a physical name artifact. Use the logical name and supply a physical destination and everything will work just fine. -- Geoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "frostbb" <frostbb (AT) newsgroups (DOT) nospam> wrote in message news:eDHMEdxTJHA.4456 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Jeff, Thanks for the quick response. Its very much appreciated. Unfortunately, I'm confused about what you're saying specially about the part about you can have the same logical file names pointing to different databases. Also unfortunately, I don't want to restore over the existing "sec" database. I want to restore a >> copy of << the "sec" database so that I can retrieve some lost information from the "sec" copy and put it back into the existing production "sec" database. I obviously don't understand the mechanics of restoring a database from tape as opposed to restoring from a set of .bak files on the hard drive. And this is NOT something I can screw up. Overwriting the existing production sec db is definitely off the table. Anyway, thanks again for the effort. Its very much appreciated. However, I think it's time to burn one of my "MSDN life line calls" Best wishes !! Barry in Oregon "Jeffrey Williams" <jeff.williams3188 (AT) verizon (DOT) ent> wrote in message news:%23zHIJ8qTJHA.5268 (AT) TK2MSFTNGP04 (DOT) phx.gbl... The error message says it all: Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. This message is telling you that you cannot MOVE the database with a logical file name 'sec_2008-11-08.mdf'. Modify the MOVE statement to reference the actual logical file name that is defined for that backup. As the error message states - Use RESTORE FILELISTONLY to identify the actual logical file names for the database you are restoring. Note: you cannot rename or modify the logical names until after the database has been restored (and, you can have the same logical file names pointing to different databases). Jeff frostbb wrote: We're using Sql Server 2005, Win2003 and writing backups to an HP Ultrium2 tape drive. I can get a File List from the tape with no problems. I'm backing up 14 individual databases to the tape. I want to create a copy of the "sec" database that has a full backup in position #11 on the tape. I want to recover the "sec" database as "sec_2008-11-24". I've successfully created this type of restore many times when the .bak file is physically on the server harddrive. However, when I use the following script I get the error message listed below the script. ANY HELP OR SUGGESTIONS WOULD BE GREATLY APPRECIATED !! Thanks in advance ! Barry in Oregon == RESTORE SCRIPT == RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001.mdf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001.ldf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO == RESULTING ERROR MESSAGE == Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. |
#44
| |||
| |||
|
|
Geoff, You and Jeff are exactly correct. After following Jeff's advice and executing a RESTORE FILELISTONLY I realized that the logical file names on the tapes did not have the .mdf / .ldf suffixes. I removed the suffixes and the RESTORE worked great! I had been trying to work from the SMS Backup Device Media Contents list. Many thanks to both you and Jeff. Your time and patience are very much appreciated !! ===== The successful restore renamed database script ==== RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO Barry in Oregon "Geoff N. Hiten" <SQLCraftsman (AT) gmail (DOT) com> wrote in message news:uPMcW5zTJHA.2136 (AT) TK2MSFTNGP04 (DOT) phx.gbl... SQL uses logical file names that are separate from physical file names. You are using the physical file name instead of the logical file name in the "WITH MOVE clause. The ".mdf" extension is a physical name artifact. Use the logical name and supply a physical destination and everything will work just fine. -- Geoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "frostbb" <frostbb (AT) newsgroups (DOT) nospam> wrote in message news:eDHMEdxTJHA.4456 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Jeff, Thanks for the quick response. Its very much appreciated. Unfortunately, I'm confused about what you're saying specially about the part about you can have the same logical file names pointing to different databases. Also unfortunately, I don't want to restore over the existing "sec" database. I want to restore a >> copy of << the "sec" database so that I can retrieve some lost information from the "sec" copy and put it back into the existing production "sec" database. I obviously don't understand the mechanics of restoring a database from tape as opposed to restoring from a set of .bak files on the hard drive. And this is NOT something I can screw up. Overwriting the existing production sec db is definitely off the table. Anyway, thanks again for the effort. Its very much appreciated. However, I think it's time to burn one of my "MSDN life line calls" Best wishes !! Barry in Oregon "Jeffrey Williams" <jeff.williams3188 (AT) verizon (DOT) ent> wrote in message news:%23zHIJ8qTJHA.5268 (AT) TK2MSFTNGP04 (DOT) phx.gbl... The error message says it all: Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. This message is telling you that you cannot MOVE the database with a logical file name 'sec_2008-11-08.mdf'. Modify the MOVE statement to reference the actual logical file name that is defined for that backup. As the error message states - Use RESTORE FILELISTONLY to identify the actual logical file names for the database you are restoring. Note: you cannot rename or modify the logical names until after the database has been restored (and, you can have the same logical file names pointing to different databases). Jeff frostbb wrote: We're using Sql Server 2005, Win2003 and writing backups to an HP Ultrium2 tape drive. I can get a File List from the tape with no problems. I'm backing up 14 individual databases to the tape. I want to create a copy of the "sec" database that has a full backup in position #11 on the tape. I want to recover the "sec" database as "sec_2008-11-24". I've successfully created this type of restore many times when the .bak file is physically on the server harddrive. However, when I use the following script I get the error message listed below the script. ANY HELP OR SUGGESTIONS WOULD BE GREATLY APPRECIATED !! Thanks in advance ! Barry in Oregon == RESTORE SCRIPT == RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001.mdf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001.ldf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO == RESULTING ERROR MESSAGE == Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. |
#45
| |||
| |||
|
|
Geoff, You and Jeff are exactly correct. After following Jeff's advice and executing a RESTORE FILELISTONLY I realized that the logical file names on the tapes did not have the .mdf / .ldf suffixes. I removed the suffixes and the RESTORE worked great! I had been trying to work from the SMS Backup Device Media Contents list. Many thanks to both you and Jeff. Your time and patience are very much appreciated !! ===== The successful restore renamed database script ==== RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO Barry in Oregon "Geoff N. Hiten" <SQLCraftsman (AT) gmail (DOT) com> wrote in message news:uPMcW5zTJHA.2136 (AT) TK2MSFTNGP04 (DOT) phx.gbl... SQL uses logical file names that are separate from physical file names. You are using the physical file name instead of the logical file name in the "WITH MOVE clause. The ".mdf" extension is a physical name artifact. Use the logical name and supply a physical destination and everything will work just fine. -- Geoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "frostbb" <frostbb (AT) newsgroups (DOT) nospam> wrote in message news:eDHMEdxTJHA.4456 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Jeff, Thanks for the quick response. Its very much appreciated. Unfortunately, I'm confused about what you're saying specially about the part about you can have the same logical file names pointing to different databases. Also unfortunately, I don't want to restore over the existing "sec" database. I want to restore a >> copy of << the "sec" database so that I can retrieve some lost information from the "sec" copy and put it back into the existing production "sec" database. I obviously don't understand the mechanics of restoring a database from tape as opposed to restoring from a set of .bak files on the hard drive. And this is NOT something I can screw up. Overwriting the existing production sec db is definitely off the table. Anyway, thanks again for the effort. Its very much appreciated. However, I think it's time to burn one of my "MSDN life line calls" Best wishes !! Barry in Oregon "Jeffrey Williams" <jeff.williams3188 (AT) verizon (DOT) ent> wrote in message news:%23zHIJ8qTJHA.5268 (AT) TK2MSFTNGP04 (DOT) phx.gbl... The error message says it all: Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. This message is telling you that you cannot MOVE the database with a logical file name 'sec_2008-11-08.mdf'. Modify the MOVE statement to reference the actual logical file name that is defined for that backup. As the error message states - Use RESTORE FILELISTONLY to identify the actual logical file names for the database you are restoring. Note: you cannot rename or modify the logical names until after the database has been restored (and, you can have the same logical file names pointing to different databases). Jeff frostbb wrote: We're using Sql Server 2005, Win2003 and writing backups to an HP Ultrium2 tape drive. I can get a File List from the tape with no problems. I'm backing up 14 individual databases to the tape. I want to create a copy of the "sec" database that has a full backup in position #11 on the tape. I want to recover the "sec" database as "sec_2008-11-24". I've successfully created this type of restore many times when the .bak file is physically on the server harddrive. However, when I use the following script I get the error message listed below the script. ANY HELP OR SUGGESTIONS WOULD BE GREATLY APPRECIATED !! Thanks in advance ! Barry in Oregon == RESTORE SCRIPT == RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001.mdf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001.ldf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO == RESULTING ERROR MESSAGE == Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. |
#46
| |||
| |||
|
|
Geoff, You and Jeff are exactly correct. After following Jeff's advice and executing a RESTORE FILELISTONLY I realized that the logical file names on the tapes did not have the .mdf / .ldf suffixes. I removed the suffixes and the RESTORE worked great! I had been trying to work from the SMS Backup Device Media Contents list. Many thanks to both you and Jeff. Your time and patience are very much appreciated !! ===== The successful restore renamed database script ==== RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO Barry in Oregon "Geoff N. Hiten" <SQLCraftsman (AT) gmail (DOT) com> wrote in message news:uPMcW5zTJHA.2136 (AT) TK2MSFTNGP04 (DOT) phx.gbl... SQL uses logical file names that are separate from physical file names. You are using the physical file name instead of the logical file name in the "WITH MOVE clause. The ".mdf" extension is a physical name artifact. Use the logical name and supply a physical destination and everything will work just fine. -- Geoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "frostbb" <frostbb (AT) newsgroups (DOT) nospam> wrote in message news:eDHMEdxTJHA.4456 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Jeff, Thanks for the quick response. Its very much appreciated. Unfortunately, I'm confused about what you're saying specially about the part about you can have the same logical file names pointing to different databases. Also unfortunately, I don't want to restore over the existing "sec" database. I want to restore a >> copy of << the "sec" database so that I can retrieve some lost information from the "sec" copy and put it back into the existing production "sec" database. I obviously don't understand the mechanics of restoring a database from tape as opposed to restoring from a set of .bak files on the hard drive. And this is NOT something I can screw up. Overwriting the existing production sec db is definitely off the table. Anyway, thanks again for the effort. Its very much appreciated. However, I think it's time to burn one of my "MSDN life line calls" Best wishes !! Barry in Oregon "Jeffrey Williams" <jeff.williams3188 (AT) verizon (DOT) ent> wrote in message news:%23zHIJ8qTJHA.5268 (AT) TK2MSFTNGP04 (DOT) phx.gbl... The error message says it all: Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. This message is telling you that you cannot MOVE the database with a logical file name 'sec_2008-11-08.mdf'. Modify the MOVE statement to reference the actual logical file name that is defined for that backup. As the error message states - Use RESTORE FILELISTONLY to identify the actual logical file names for the database you are restoring. Note: you cannot rename or modify the logical names until after the database has been restored (and, you can have the same logical file names pointing to different databases). Jeff frostbb wrote: We're using Sql Server 2005, Win2003 and writing backups to an HP Ultrium2 tape drive. I can get a File List from the tape with no problems. I'm backing up 14 individual databases to the tape. I want to create a copy of the "sec" database that has a full backup in position #11 on the tape. I want to recover the "sec" database as "sec_2008-11-24". I've successfully created this type of restore many times when the .bak file is physically on the server harddrive. However, when I use the following script I get the error message listed below the script. ANY HELP OR SUGGESTIONS WOULD BE GREATLY APPRECIATED !! Thanks in advance ! Barry in Oregon == RESTORE SCRIPT == RESTORE DATABASE [sec_2008-11-08] FROM [HP-Ultrium2-Tape] WITH FILE = 11, MOVE N'sec_data_001.mdf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_data_2008-11-08.mdf', MOVE N'sec_log_001.ldf' TO N'C:\Sql Server 2005\owrd\MSSQL.1\MSSQL\DATA\sec_log_2008-11-08.ldf', NOUNLOAD, STATS = 10 GO == RESULTING ERROR MESSAGE == Msg 3234, Level 16, State 2, Line 1 Logical file 'sec_data_001.mdf' is not part of database 'sec_2008-11-08'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.