We just migrated a group of production sites from one hosting environment to another. The new environment has staging and production servers, so we really completed two migrations. There were a lot of databases, and if you've been following my blog at all you probably know that I love to script repetetive tasks - not (only) from laziness, but from a desire to avoid typographical errors due in repetetive manual work.
First, I backed up all databases on the old server. I ran this script, which generated a DOS batch file:
So I shifted the Query Analyzer output to text mode (ctrl-t), ran the above query, and saved the results to a file - BackupDatabases.bat. I wanted a batch file so I could test the migration up to the cutover day, at which time I'd need to do a final backup of the old sites. Looking good so far.1
At this point, I zipped up the backup directory (full of .bak files) and copied it to the new server. Now it's time to restore all those .bak files. The following batch file rips through a directory, restoring everay .bak file it finds:
Unfortuantely, it didn't work for a few of the databases. What had I done wrong? Well, I'd assumed the simple case (hence the title of this post) - I'd assumed that the database logical names matched the database name, so Example.bak would restore to Example_Data (in Example_Data.mdf) and Example_Log (in Example_Log.ldf). That's not always the case, especially if a database has been copied via backup / restore / rename.
I'll talk about how to script backup / restore when database logical names don't match the database name next...
1 You can use of course set up SQL Server jobs to backup your databases, but there are advantages to having a single batch file which backs up all databases in one go.