Attaching and Detaching SQL Server Databases

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:15
  -
  -
  -
  -
07:30
  -
  -
  -
  -
07:45
  -
  -
  -
  -
08:00
  -
  -
  -
  -
08:15
  -
  -
  -
  -
08:30
  -
  -
  -
  -
08:45
  -
  -
  -
  -
09:00
  -
  -
  -
  -
09:15
  -
  -
  -
  -
09:30
  -
  -
  -
  -
09:45
  -
  -
  -
  -
10:00
  -
  -
  -
  -
10:15
  -
  -
  -
  -
10:30
  -
  -
  -
  -
10:33

Hello and welcome to this SQL Server Video, my name is Michael Campbell, and in this video we’ll be looking at moving and copying databases while they’re offline, which is typically referred to as detaching and attaching databases.

And, if you don’t know what that means, or if you want to look at other options for copying and moving SQL Server databases – including online options that don’t require you to take your databases offline, then you should definitely check out the introductory video in this series: Options for Copying and Moving Databases.

Otherwise, the purpose of this video is to walk you through the step-by-step process that you’ll need to take to either copy or move databases from one server to another. And, from a high-level perspective, that process effectively looks like:

First, locate the files for your databases on the source, or host, server.
Second, remove SQL Server’s ‘hooks’ from those files by either shutting down the server, or by detaching the database in question.

Third, move or copy the files identified in step one, and Fourth, ‘re-online’ or re-enable your source database on the source server if needed (for example when you’re making a copy instead of moving the database).

Fifth and sixth, move copies of your database files to the destination server where you’ll need to configure some security details relative to attaching the databases, and then go ahead and attach them.

Finally, you’ll then need to address security issues with any potentially orphaned logins or users – which will be covered in a separate video.

However, if you’re planning on moving your database instead of just copying it, then you’ll want to make sure you watch that video, along with this one, BEFORE you move your database as it will help you gather security information that will need to be transferred as part of the migration process.

So, to get started, let’s fire up SQL Server Management Studio and move a copy of the SSV database from one dev server to another. And, in this video, we’ll use SQL Server 2008, but the process outlined will be very similar to what you’ll need to do with SQL Server 2005 databases.

Once we’re logged in, we’ll switch to the SSV database, and fire up a new query. Then, by interrogating the sys database_files catalog view we can get a listing of all of the files belonging to the current database – along with a paths to these files on the local file-system.

And, in the case of this database, the third file listed represents a full-text catalog – which, in SQL Server 2008 has been integrated into the SQL Server engine to the point where this catalog shows up as an actual file.

In SQL Server 2005, however, this full text catalog would show up as the path to the folder containing the contents of my catalog. In either case though, when you’ve got Full-Text Indexing enabled you’ll want to make sure all of the files and folders listed by this query are copied or moved – otherwise your database won’t work correctly.

Now, in terms of detaching your database, the goal is to get SQL Server to take its ‘hooks’ out of your files so that you can copy them or move them as needed. Otherwise, you won’t be able to access them without something like Shadow-Copy because the files will be in use.

And, to do that you’ll either need to stop SQL Server – which is obviously a bit of a drastic approach to take, or you can just take your database offline.
Stopping SQL Server is pretty easy to do – as it just entails stopping the service, and restarting it once you’ve copied files.

A much less invasive way to approach this task though is to just take the target database offline – which can be done in two different ways.

The first option, which is slightly more difficult to manage if you’re just making a copy, is to detach the database.

And, by detaching a database you’re effectively telling SQL Server to delete the database from service – WITHOUT deleting the underlying files.

As such, this then gives you the ability to move those files to a different server, or make copies of them and then re-attach your source files to re-enable the source database.

And, to do this from the GUI, you’ll want to kill any existing connections – which can sometimes take a couple of attempts from within the GUI.

And, obviously, when you take this approach, not only will you be taking this database offline, but you run the risk of violently terminating any processes that are interacting with it – so you’ll want to do this at a point in time where such an operation would make sense.

If copying instead of moving the database is your goal, I’d recommend taking the target database offline instead of detaching it. Taking it offline will still accomplish the goal of letting you interact with the underlying files, but doesn’t instruct SQL Server to pretend like these files never existed. The up-side of that is that when you need to bring the database back online, you just push a button and you’re done.

The only problem with this approach though is that the GUI won’t let you ‘offline’ a database while it’s in use. As such, I prefer using a simple script you can download from the code page accompanying this video on the SSV site which creates a simple cursor that kills any connections, and then takes the DB in question offline.

Otherwise, once the script has run, we can see that our database is now offline – meaning that we can move and copy files around as needed.

And, since this is an offline operation, we’ll typically want to quickly copy and paste these files to a temporary location – in order to resume service as quickly as possible.

Then once things are back up and running, we can the zip or rar our files and then them off to a remote location as needed.

In this case though, since I’m just moving a copy of my SSV database from one dev machine to another, I’ll just initiate that transfer here and call it good.

With copies of our files now made, we can bring our database back online – and the beauty of using the OFFLINE approach is that instead of having to go through the process of manually attaching our files back to the SQL Server, we can just bring the db back online with a few clicks of the mouse.

With our source server now configured, we’re ready to switch gears and go look at what it takes to attach our database on a new server – and this process will be the same whether we’re moving or copying databases.

However, when it comes to attaching files on a new server, there are two things you’ll need to watch out for.
The first is to make sure that the account that SQL Server runs under has access to the files you’ll be attaching out on your destination server.

Typically, this will be done automatically if you’re copying to a folder where that account already has permissions – but this is something you’ll usually want to check anyhow.

The second, and more tedious bit of configuration stems from the fact that starting with SQL Server 2005 and up, there are restrictions placed on which users can attach a database.

Primarily, this is due to the fact that it is possible for malicious code to be executed when attaching databases from unknown sources. Therefore, even if your login has SysAdmin perms, you won’t be able to attach databases unless you’re logged in as a member of the local administrators group – as outlined by Books Online.

Personally, I think this is a stupid and heavy-handed approach on Microsoft’s part – even though I do appreciate the security implications. Mostly because this is a security hurdle that’s imposed to protect people who are dumb enough to try and attach databases that they get from untrusted sources. That, and you don’t run into this security hurdle at all when using backups.

At any rate, what this means is that you’ll typically have to log into your SQL Server locally in order to attach SQL Server 2005 and 2008 databases – which I’ve done here by logging in to my destination server where I’ve fired up SQL Server Management Studio.

Then, by right-clicking on the databases node, I can begin the process of attaching a database by navigating out to the primary data file for the database that I want to attach. By convention, this file should employ a .mdf extension – but if that’s not the case, it will be the first file listed by the sys.database_files catalog view that we looked at earlier.

And once I find this file, SQL Server will read header information within that file – which tells it where that file THINKS other files pertaining to this database are located.

The key thing to note though, is that those paths all represent the paths from the source server. Therefore, if you’ve changed the location of those files on your destination server, like I have, then you’ll need to go through and make any changes to ensure that SQL Server can find all of the files as needed.

Likewise, you’ve also got the option to specify what name SQL Server should use when attaching this database – and you can also specify the owner – which I like to set to sa or SysAdmin.

And, of course, it’s worth pointing out that if you’re using this approach to create a copy of your database on the same server as the source database, you’ll need to make sure that there are no naming collisions with either the database name, or with the full paths and names of your files – though you can easily append something like _copy to all of your files or rename them entirely – but if you do so you’ll just need to manually repoint the actual paths and files as needed.

Otherwise, once all of your files are added, you can go ahead and click the OK button – at which point SQL Server will begin verifying file contents, attach your database, and bring it online – ready for service.

At this point, all that remains is the 7th, and final step, which involves addressing security settings and configuration details within the database itself – especially in terms of how that relates to login details stored on the new, or host, server.

Of course, in cases where you’ve merely created a copy of a database on the same server, everything should typically just work as the logins needed in order to map user access and rights into your database will already be in place.

Otherwise, though, if you’ve copied or moved this database to a remote server you’ll want to check out this video’s companion video, Copying and Moving SQL Server Logins, in order to make sure that security information is correctly migrated as well.

So, with that in mind, thanks for joining and we look forward to seeing you in subsequent SQL Server Videos.