I am noticing a problem while using COPY DATABASE WIZARD in SQL Server 2008 (10.00.2573). What i did is changed Default Data & Log file location for SQL Instance by right clicking on INSTANCE -> Properties and then Database Settings option.
eg: OLD PATH: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA
NEW PATH: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA1
( I created DATA1 folder manually at this location).
Next i opened FACET property for the instance, over there it shows me new path i.e.
C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA1
Then I restarted SQL Instance so that these changes should be submitted permanently.
Next I tried to create a new database and its getting created to new location i.e. C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA1 location.
Next I created a sample table and inserted few rows.
I started COPY DATABASE WIZARD and selected SOURCE & DESTINATION server same & selceted this newly created database and used COPY option. It shows me a name say DBNAME_NEW but strange part is that DATABASE FILE & LOG FILE location is still pointing to old i.e. C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA. Howerver I have made a change and as per that it should point to C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA1 location.
I though might be its taking this location from SOURCE database which I am trying to copy but that is also not the case as its created after changing DATA & LOG File location and its files are there in C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA1 location?
So why is this behaviour? Is this a bug? Please let me know about this.
Gurpreet Singh Sethi