tim laqua dot com Thoughts and Code from Tim Laqua

27Feb/103

Script sp_configure Output To Migrate Settings Between SQL Servers

Sometimes when setting up a new server, all you want is for it to work and be configured exactly like the old server. In some cases, the new server is almost identical in every way already. If you've worked much with SQL Server, you know very well that there are a billion switches and knobs and every time you do this, you forget at least one. I had a similar situation recently and figured I'd be lazy about it and just turned the output of sp_configure in to a script I could execute on the new server:

27Feb/100

Script RESTORE DATABASE … WITH MOVE Stub

I think most people setup the drives on their Development servers to match their Production servers - this is so that restores go smoothly and files go where they're supposed to, things match up nicely, etc. Unfortunately, when you create a full backup all the backup file contains is the logical name of all the files - no physical paths. This means that if the database doesn't exist on the destination server yet, the engine has absolutely no idea where to put the files or what to name them.

The following script can be executed in the context of the source (original) database that was backed up to script out the MOVE statements if your plan is to put the files in the same place on the destination server.