Copying and Moving 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:05

Despite the fact that using backups to copy or move databases may sound intimidating, it’s actually one of the easiest ways to do so as it typically involves much less work than other approaches and usually yields the highest rate of success.

That said, if you’d like to know about other options for copying and moving SQL Server databases, then make sure to check out the introductory video in this series: Options for Copying and Moving SQL Server databases.


Otherwise, in this video we’re going to take a look at a step by step tutorial of just what you need to do in order to most easily move and copy your SQL Server databases.

And, from a high-level perspective, the process for moving or copying your databases that we’ll look at in this video includes:
First, either creating or ensuring that you’ve got a viable backup of the source database you want to move or copy.

Second, copying or moving that backup to your destination server – which could be the same server as the source – or another server entirely.

Third, restoring the selected database while making sure that path and filename information won’t cause any collisions.

Finally, when moving or copying databases you’ll also need to address potential security issues due to logins being mapped on the source server which aren’t found on the destination server. Therefore, once you’ve concluded steps one through three you’ll likely want to check out this video’s companion video, Copying and Moving SQL Server Logins.

Moreover, if you plan on moving your database instead of just copying it, you’ll want to watch the companion video, along with this one, before you start the migration process in order to make sure you’ve collected all of the information needed before starting.


So, let’s get started by logging into one of my SQL Server 2008 dev servers where we’ll create a backup of the SSV database in order to deploy a copy of it on one of my other servers. And, while I’ll be using SQL Server 2008 in this video, the steps I’m outlining will be very similar if you’re following along on SQL Server 2005.


And now that we’re logged in, there’s one key consideration that we need to address when it comes to making backups for the purposes of copying. And that consideration is this: when it comes to backups, there are basically three different types: full, differential, and log file backups.

In our case, all we really care about are full backups, but since full backups serve as the BASIS for differential and log-file backups, you NEED TO BE VERY CAREFUL when creating backups for the purposes of making copies as you don’t want to jeopardize your backup and recovery schedules.

In other words, assume that you’re running full backups every night and log file backups every hour during the day. I

f you go ahead and create a new full backup of your database at say… 3:45PM any differential or log file backups that you make will made against that latest full backup – instead of the one you made at night.
So, if you end up moving this backup off-box, you can run into some sticky problems should you need to recover and can’t find that backup.

Happily though, there are a couple of things we can do to get around that issue.
First, we can just use a COPY of an existing backup if we don’t need our data to be 100% up to date. So, for example – we could just pinch a copy of the full backup made every night, and we’re done.

Otherwise, SQL Server 2005 introduced a great new addition known as the ‘Copy Only Backup’ – which gives you the ability to create a full-blown backup of your database that won’t flip differential bitmaps or mess up your disaster recovery plan. So it’s a perfect option to use in our case.

And to take advantage of that option, we just need to place a check mark in the appropriate check-box after specifying which database we want to backup and after making sure that we’re creating a full backup.


Likewise, in cases where using the GUI isn’t feasible or isn’t an option, you can also accomplish the same thing by downloading the T-SQL script from the code page accompanying this video on the SSV site to see what a scripted version of this copy only backup would look like if needed.

And, as you can see here, I’m just swapping out some parameters in this script, such as the name of the database I want to backup and the path to the backup, and then I’m good to go.

Otherwise, if you end up using the GUI, the next thing you’ll need to do is specify where you’d like your backup output.
And to do this, just clear out anything in the destination area, and then click the Add button to create a new backup device, or location.

Then, from here, just navigate to where you’d like to drop the file and give it a name. And, note that you’ll typically want to specify an extension for your backup to make it a bit easier to spot – with .BAK being the extension used by convention.

With those options configured, switch to the second, or options, tab and make sure that you specify the ‘overwrite all existing backup sets’ (as long as you’re not using an existing backup set).

If you’d like you can also toggle the verify backup when finished option, and if you’re running SQL Server 2008 Enterprise Edition you can also compress the backup to save some decent size. But if you use this option you must be sure that the destination server is also running SQL Server 2008 Enterprise Edition as well.

Of course, if you’re not running SQL Server 2008 Enterprise Edition, or can’t use it at the destination server, don’t worry as you can still zip or rar your backup once it completes – and you’ll typically WANT to do this as it can significantly cut down on the size of the .BAK file that you may end up transmitting over the wire or writing to disk, and so on.

Then, with all of the options set and configured, you can press the OK button and your backup will be created.

Once the backup is complete, or once you’ve located a viable existing backup, go ahead and compress it as needed and then just move it from the source server to the destination server as needed – as you’re now just dealing with plain old file-system files that can be moved around like any other file.

And, in cases where you just want to create a copy of your database on the same server – you won’t need to move this backup at all. In my case though, I’m deploying a copy on another server, so I’ll just copy and paste my backup there without compressing it in the interest of time.

At this point, most of the hard work is done and you now just need to restore your database on the target server – be that the same server as where you started out, or on a completely different server.

To restore your backup, just right-click on the databases node on the server where you want to restore the backup, and select the Restore Database option.

Within this dialog the first thing you’ll need to do is specify the name you’d like your database to have on the destination server – and this can be any valid database name you chose – but make sure you’re not using the name of a database that already exists (unless, of course, you want to actually replace an existing database).

Next, you’ll want to tell SQL Server that you’d like to restore from a device or file – which you can do by clearing any existing selected devices, and then clicking the Add button – at which point you can then navigate out and find the .BAK file that you either created on this server earlier, or – as in my case- which I copied to this new server from the original, source, server.

To verify the contents, click on the Contents button for a quick overview of what’s contained.

Then, make sure to check the box next to the selected backup, and switch to the Options page.

Here, the most dangerous check-box available is the top one – so don’t use that unless you’ve selected an existing database and WANT to over-write it.

Otherwise, the other check-boxes are pretty much moot for the task we’re working on here – so we’ll turn our attention to the only area that will require some attention – which is where we specify file names and paths.

And what’s going on here is that SQL Server has queried your backup and found out how many data files your source database had – including primary data files, secondary data files, log files, and full text indexes if there were any – and so on.

Then, for each file listed, the only thing that you need to do is make sure that the path specified in the ‘Restore As’ section of this dialog is both: correct or viable, and that it doesn’t collide with a file (or folder in the case of SQL Server 2005 full text indexes) that already exists – unless, of course, you’re attempting to overwrite a database.

And, with these options set, you’re now done because everything else is set up the way you want it – so click OK and SQL Server will go ahead and create the needed files on your destination server, configure them with the correct underlying security permissions to make sure that the SQL Server Service can work with these files, and then start loading them full of data pulled out of the .BAK file that you’ve specified.

And, as a part of this restoration process, SQL Server will also load information about user access and security as defined in your source database.

The problem though, is that this security information is typically coupled to login information stored at the server level. So, if you’ve copied this database to a different server, those server-level logins are likely going to be absent.

Therefore, you’ll want to watch this video’s companion video, Copying and Moving SQL Server Logins to make sure that you get those details all straightened out.

Otherwise, good luck copying and moving your own SQL Server 2005 and 2008 databases. I’m Michael Campbell, and I’d like to thank you for watching this video and I hope to see you in subsequent SQL Server Videos as well.