This KB isn't directly related but the quote in it is:
MSKB2615182: Windows Server Backup May Fail Because of the SQL VSS Writer:
When Windows Server Backup attempts to backup a disk volume, a Volume Shadow Copy Snapshot is created for the volume. When the snapshot is created any VSS writer associated with the volume is called.
If there are SQL Server VSS writer backups being taken then you will see records in the MSDB.dbo.BackupMediaFamily
table in the physical_device_name
column. You might want to look into disabling the SQL Writer Service
.
TechNet: SQL Writer Service
Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time:
1) Backup and Restore Databases - Is this the best option?
It's an option. If you need the downtime to be less, you can use mirroring or log shipping (or do it by hand) to keep the databases in sync and then migrate during a fast downtime or cutover.
Please note that system databases cannot be log shipped or mirrored and would need to either be frozen or copied right before the cutover.
2) Migrate Logins - use Microsoft KB? kb/918992
If you restore the master database to the new instance, no migration should be needed. All server level logins are stored in the master database.
3) Migrate Credentials/Certificates - what is required for this?
If this is inside of SQL Server, these would be held in their respective databases (and possibly master as well). The one difference would be the service master key (SMK) that would change. You make want to back that up and restore it on the new server if you're relying on automatic key decryption.
4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)
If you restore the msdb system database, all agent jobs will be held in it. There would be no need to script->restore.
5) Migrate SSIS Packages - (How to do this?)
If the SSIS packages are on the filesystem (not default) it would be trivial to create the same location on the new server and copy. By default the SSIS packages are held in the msdb system database and restoring it would get you to the same place, just like #4 and previous.
6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)
This is also stored in msdb. See #4, #5.
7) Recreate assemblies
These live in their respective databases. If backup and restore is used, this should not be a problem. Any assemblies outside of SQL Server would need copied to the new server.
8) Recreate Linked Servers
These also live in the master database, see #2.
Best Answer
A backup of the entire server will be inclusive of the SQL Server instance and the database files as a snapshot in time at that moment. This is assuming that the database files live on the server itself that you're taking a backup of, as it is possible to setup the database's such that their files live on remote shares.
You can verify the location of your databases' files in SQL Server Management Studio, by right clicking on a specific database, clicking Properties, and then navigating to the Files page, then looking at the Path and File Name properties.
For example:
In the above, my
Test
database's files are stored in the default SQL instance folder. Please ensure the drives that your database files live on are part of the server / snapshot being taken of it, as even mapped drives can have drive letters but point to remote shares.There shouldn't be any additional work on the destination server, but you may want to make sure the appropriate SQL services (in the Windows Services) are started and running just the same as on the source server.
For example:
The above is my test instance which probably has a lot more components of SQL Server installed than yours will, so don't take this as a definitive list, rather it's an example. Compare against your source server to the destination server.
The only other thing I can think of is to ensure all the same Windows permissions are setup to the correct accounts on your destination server as its respective source server. For example, if you're using the SQL Agent, or a feature like Replication, those might leverage Windows accounts that currently have special permissions setup on your source server which need to be replicated on your destination server. (This is a little outside the scope of DBA.StackExchange though, and might be more of a ServerFault question, if you need help with that.)