SQL Server's Journal (original) (raw)

I needed to shift an SQL server installation along with all databases from one machine (say X) to other machine (say Y). I have successfully done that except for one difficulty. I need the datafiles in a different path.

To begin with here is how the shift went through -
1. Took backup of Master database on X.
2. Started SQL Server in single user mode on machine Y.
3. Restored Master database to Y (This is to retain all logins and their passwords in Y)

The problem started after doing this. Restoring master on Y requires that datafiles reside on the same path as that of X (Say "D:\"). Unfortunately thats not the case. Datafiles on Y is to be placed at "E:\" instead. I managed to get the required path on Y. SQL Server started running properly once it could locate datafiles in the required path ("D:\"). Later on i could shift datafiles to the desired path ("E:\") on Y through one more round of backup and restore. The only databases that refused to get shifted to desired location is 'Model' and 'Distribution'. Both are system databases (model act as template database, distribution is required for replication) and simply refuse to any movements.

Solutions for shifting system DBs, if any?

PS: My datafiles are not being stored in 'E:\' as stated above. I don't seek suggestion over the 'ideal path' for datafiles.