850 reputation
1020
bio website
location New York, NY
age 37
visits member for 3 years, 8 months
seen Jun 3 at 13:53

May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
Found it!!! This is where having the backup file would have helped you I'm sure. The backup file contains multiple backups. So the FILELIST command the way that I'm using it is pulling the earliest backup logical file name. which is not the logical file name associated with the backup I'm trying to restore.
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
Gotcha but in this case wouldn't the backup file have gotten the designation from sys.master_files?
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
By that command I mean the ALTER DATABASE x MODIFY FILE (name = 'y', newname = 'z'); command.
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
@AaronBertand - I just wanted to take a moment and say thanks for your persistence in helping me with this problem. I really appreciate it. - I can use that command to set them to 423 which would allow the procedure to go forward but when I change them back it doesn't change the value coming back from filelist. Where does filelist pull this information from? The logical file is correct in sys.master_files.
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
Yep Sorry about the file names. They are actually all 4 digit numbers (and we have 3000 of them) for reasons that predate me. were working on fixing that. If I were to use Modify file on these databases now to make the logical names match the physical names is that likely to fix the issue do you think?
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
@AaronBertrand - since the front of the house can use that stored procedure to create databases from "template databases" ie just another db. and then use those new databases to create other databases I can't tell. I suspect that database 423 is in database 4295's ancestry at some point. Did that make sense?
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
@AaronBertrand - So just to make sure I'm answering fully. I get 423 both from the procedure which is referenced in the old ticket and also if I run the command listed in my previous comment. while if I use the SSMS GUI I have a different value listed.
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
@AaronBertrand - Now I get you. When I run the following RESTORE FILELISTONLY FROM DISK = 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\TemplateCopy\4295.BAK' I receive the 423 value. I made a type above. Its 423 not 349. I'll fix that.
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
@AaronBertrand - I'm sorry Aaron. Yes I'm referring to the code referenced in the linked ticket when I say using code. and the without using code refers to using the SSMS GUI to do the backup and restore. Please see my comment to Bob Pusateri.
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
@BobPusateri - by manual I'm referring to using the SSMS GUI. Under Options the Original File Name is listed as 3497. I've found in the past that if I hardcode my procedure to use that same value that the procedure works fine.
May
29
comment SQL Server FILELISTONLY command providing incorrect logical database name
@AaronBertrand - You caught me. Yes sir that is what I mean.
May
23
comment Backup Restore Procedure error SQL Server 2008 R2
@AaronBertrand - is this something I should ask as a separate question?
May
23
comment Backup Restore Procedure error SQL Server 2008 R2
@AaronBertrand - Is it possible for filelistonly to return the wrong logical name? When I do a manual restore of the DB I get a different logical name than the one I see from the filelistonly.
May
23
comment Backup Restore Procedure error SQL Server 2008 R2
@AaronBertrand - I wish that was an option. Unfortunately reengineering the process the organization uses to spin up these sites is a multidepartment undertaking that is not likely to get off the ground at the moment.
May
22
comment Backup Restore Procedure error SQL Server 2008 R2
@AaronBertrand - I used SET SQL = N' RESTORE DATABASE ' + CONVERT(NVARCHAR(500), DestinationDB) + ' FROM DISK = ' + CONVERT(NVARCHAR(500), BackupPath) + ' WITH FILE = ' + CONVERT(NVARCHAR(500), Position) + ', MOVE ' + CONVERT(NVARCHAR(500), DataFile) + ' TO ' + CONVERT(NVARCHAR(500), NewDBFileName) + ', MOVE ' + CONVERT(NVARCHAR(500), LogFile) + ' TO ' + CONVERT(NVARCHAR(500), NewLFFileName) + ', NOUNLOAD, REPLACE, Stats = 10;' SELECT SQL.
May
22
comment Backup Restore Procedure error SQL Server 2008 R2
@AaronBertrand - well in the original the file paths are passed in as NVarchar variables so.... No. No quotes. However, I suspect I should have put them in for my example. sorry about that.
May
22
comment Backup Restore Procedure error SQL Server 2008 R2
@RLF - We are running SP2. Version number 10.50.4000.0 (x64)
May
22
comment Backup Restore Procedure error SQL Server 2008 R2
@AaronBertrand - I've added a print of the sql that is created as reqyested.
May
22
comment Backup Restore Procedure error SQL Server 2008 R2
Nope. Only one mdf and only one ldf file.
Apr
25
comment Hidden user rights
That query returns nothing as well.