Chapter 21. Moving, migrating, and upgrading databases
The more you work with SQL Server, the more likely you are to need to move, migrate, or upgrade a database from time to time, and in this chapter I’ll show you some of the main techniques for doing so. I’ll be focusing strictly on moving (or upgrading, or whatever) an entire database, completely intact, without making changes. You might need to do that if you’re relocating a database to a new SQL Server instance, for example, or if you’re upgrading versions of SQL Server.
I won’t be covering situations where you need to modify the database in some fashion, such as when you’re upgrading the version of the application that uses the database. That’s beyond SQL Server itself.
If you set up a VM-based lab when you started this book, then you should remember all about attaching databases, but here’s a quick review.
SQL Server keeps your data in a file, on disk, that has a .MDF filename extension. Optionally, some databases may have data in one or more secondary .NDF files as well. Also, each database will have a transaction log file in a .LDF file. By default, SQL Server has these files open for its own exclusive use at all times (well, so long as SQL Server is running, at least), making it impossible to copy these files. To get SQL Server to let go of a database’s files, closing them and making them available for filesystem operations, you detach the database.