January 8, 2016 at 4:07 am
Hi
I have a requirement to make a backup of one of databases 'Widgets' and restore the backup as a read only database, and restore the database with the name Widgets_copy.
During the restore I specified the file name of the data and log files to be 'Widgets_copy_dat.mdf' and 'Widgets_copy_log.ldf'
The restore was successful, but when I look at the newly restored database properties the file name is showing the original name ie Wigets_dat.mdf and Widgets_log.ldf.
Should this not be pointing to Widgets_Copy_dat.mdf and Widgets_copy_log.ldf' ?
January 8, 2016 at 4:12 am
Are you sure you're looking at the physical name of the files and not the logical name? Please will you post the RESTORE DATABASE script you ran? (If you used the GUI you can generate the script by going through the wizard and at the last minute clicking Script instead of OK.)
John
January 8, 2016 at 4:25 am
yes Im definetely looking at the Physical file name and not the logical one. I have noticed that the logical file names are the same on both the Widgets_copy and Widgets databases.
the script was produced from using the gui and is as follows
USE [master]
RESTORE DATABASE [Widgets_Copy] FROM DISK = N'D:\Backup\Widgets_auto.bak' WITH FILE = 1,
MOVE N'xxx_demo_dat' TO N'D:\DATA\Widgets_Copy_data.mdf', MOVE N'xxx_demo_log' TO N'D:\LOGS\Widgets_Copy_log.ldf', STANDBY = N'D:\Backup\Widgets_RollbackUndo_2016-01-08_10-47-02.bak', NOUNLOAD, STATS = 5
GO
January 8, 2016 at 4:39 am
That's curious. What does this return, please?
SELECT name, physical_name
FROM Widgets_Copy.sys.database_files
John
January 8, 2016 at 4:42 am
it returns the old file paths - not the new one !
January 8, 2016 at 4:48 am
however....
i just found this query from Gail Shaw
SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files AS mf
and that reveals the correct physical files.
I'm slightly confused here now....
When i right click the database and look at the properties it does show the old file name under the FileName section !!
January 8, 2016 at 4:53 am
Ooo, how interesting. I thought it was sys.master_files that doesn't get updated immediately, not sys.database_files.
John
January 8, 2016 at 5:23 am
Can anybody tell me why when in ssms and looking at the properties of the datafiles using the GUI it shows the old filename and not the one from the query
SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files AS mf
January 8, 2016 at 6:38 am
That's not something I've seen, so I don't have an explanation for it. Have you patched your SSMS installation? Assuming SSMS is running locally, even if you've regularly patched the server, you also need to run patches on SSMS (although, Microsoft is changing that with the new version of SSMS).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply