9

I am new to administering SQL Server however I am comfortable with the SQL language and making SSIS packages.

I want to migrate data from SQL Server 2005 to 2016.

My question is do I need to worry about the system databases and other objects such as indexes, stored procedures, view, security and permissions. logins or can I just migrate data.

Which would be the recommended procedure in this case.

  • Are you attempting to upgrade a production instance? i.e. do you need the same logins, indexes, stored procs, etc? As it is, your question is really quite broad. Please take the tour and read the help center on how to ask good questions. Thanks! – Max Vernon Dec 7 '16 at 18:40
7

Max gave a decent answer which I will upvote once I'm done typing this alternate view up.

I am not a fan of restoring system databases when doing an upgrade migration and I prefer doing migrations over in place upgrades as I discussed in this lengthy answer to another question.

Basically I like to start "fresh" when I do a migration. I find that playing with system database migrations and upgrades through restore to sometimes cause frustrations with the restores and it can carry over potential sins.

You also asked about indexes, stored procedures, views. Those items at the database level should all live inside of a user database. So when you restore database X to the new server, all of the database objects (Tables, Users, Views, Procs, Functions, etc) will be there as well.

What exist in the system databases are jobs, logins, alerts, linked servers, encryption keys, etc. Instance level items.

I like to review those and migrate over what I need using various scripts - lately that is the DBATools.Io powershell scripts. I like using their script to copy sql logins especially, because it handles the SQL authenticated users keeping their passwords and security identifiers the same so the database users from those logins will work. They also have an entire SQL Server migration command which runs their sub commands to copy over the items I typically would copy over.

I do not believe Max to be wrong with that answer hence the upvote. I just have had more success and more luck and feel more comfortable migrating to new instead of trying to restore over system databases between versions. I would say that I've honestly can't remember the last time I did a version upgrade migration and didn't do it this way instead of restoring the system databases.

| improve this answer | |
  • Thanks for your answer. I don't want to upgrade from 2005 to 2016. I want to migrate a database from an instance no longer used on sql server 2005 to an existing instance on 2016. This migration has not been approved yet, but I want to be prepare when it does. Basically, I got this project because I am the only one who has any SQL experience as far as querying and creating SSIS packages. I was also concern with compatibility issues. Once again thanks. – Robert Dec 8 '16 at 20:30
  • Sure - but when moving from 2005 to 2016 - you are doing an upgrade. You are doing an upgrade and a migration. Because you are taking a database from one version of SQL to another - that implies upgrade for sure. You should run the upgrade advisor on your database. – Mike Walsh Dec 8 '16 at 21:04
  • What if SQL Server 2005 is hosted by Windows Server 2003 R2 x86 and SQL Server 2016 is on a newer OS. – Robert Dec 9 '16 at 0:26
  • @MikeWalsh I agree with almost all of what you described in your response. I too follow almost all of it when doing SQL Upgrade/Migration – nam Apr 10 '19 at 15:30
4

You'd want to consider migrating the system databases (master, msdb, and perhaps model), if you need access to the metadata stored in those databases.

Master stores items such as logins, security certificates, etc.

msdb contains details about backups, and stores SQL Server Agent job configurations, among other details.

Model may have been customized by you or your team to allow newly created blank databases to contain a set of predefined objects you use in every database.

Migrating system databases can be accomplished fairly easily; detailed instructions are available from MSDN at Back Up and Restore of System Databases (SQL Server).

Depending on your requirements, you could perform a BACKUP DATABASE operation against the user databases on the 2005 instance, and then RESTORE DATABASE on the 2016 instance to bring the entire database, including all data, indexes, and other objects.

This will, at least initially, require the same amount of space consumed by the database on the 2005 instance. However, once you've restored the database, you could take advantage of data compression to substantially reduce the required footprint.

See About Working with SQL Server Backups for details on performing backups, and About Restore Scenarios for details about restores.

| improve this answer | |
2

Is it to 2012 or 2016? It makes a difference in that, IIRC 2012 is a tested migration path while 2016 is not. As such, known issues are documented and/or will be caught by Upgrade Advisor for 2012. A non-tested path might still work without any issues, it's just unknown. That said, strongly recommend you go to 2016. I suspect the effort will be almost the same.

See Are you upgrading from SQL Server 2005? in the documentation for 2005 upgrade options and links to a very detailed upgrade process. The upgrade process was written for 2014 but is still applicable for 2016.

| improve this answer | |
  • 1
    It's 2016. Sorry about that – Robert Dec 8 '16 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.