I work in a development shop where we primary use SQL Server for our day-to-day development and testing.
Sometimes issues occur on someone's machine, often a labtop, that I would like to reproduce on my machine. I therefore want to transfer their databases to my machine. This transferring is often offline where the labtop is elsewhere, so we dump databases, FTP them in and restore them, so online transfer mechanisms aren't usually available. Usually there are 5+ dump files to restore.
The current process is to use
backup database +
restore database and passing the dump files around. This works ok but is a real pain because of the physical names stored in the backups. When doing the restore I have to do
with move to map the logical files into the backup file to the physical location of the mdf/ldf files on my machine.
Given SQL Server knows the default location to create these files, as
create database works like this, is there any way I can come up with a smarter way of moving these dbs from one instance to another? I would be quite happy if db names were exactly the same (if that is scrappable from the dump files) and physical location would be inferred from the target instances default.
NOTE: As an aside the Management Studio restore option (GUI) seems to be clever enough to manage this without having to detail the physical locations manually.