Proper steps to upgrade previous mssql db to an updated newest version of mssql

We just  finished installing mssql2013 and have databases from sql2005 and the majority sql2008/sql2008r.  To update  these databases to sql2013, just attached them to the instance? What other steps to follow for proper "attach" and upgrading these databases?
rayluvsAsked:
Who is Participating?
 
Eugene ZConnect With a Mentor Commented:
answer (again)
1)yes, restore
and
2)Again,  There are no many articles about this, thus use the info  from the DBAs "front lines" and MSFT BOL

But if you do not trust my posts,
trust MSFT (you have no choice)

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-2017
For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database

just idea -- good for your case too
in earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. Databases that were created on versions prior to SQL Server 2005 may contain incorrect counts. Therefore, we recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms188414(v=sql.90)


and the generic steps to move DB to another sql server (copy logins,  etc)

How to move databases between computers that are running SQL Server
https://support.microsoft.com/en-us/help/314546/how-to-move-databases-between-computers-that-are-running-sql-server

more read:
MSFT sql_server 2012 upgrade technical reference guide
http://download.microsoft.com/download/9/5/3/9533501a-6f3e-4d03-a6a3-359af6a79877/sql_server_2012_upgrade_technical_reference_guide_white_paper.pdf


see the valid for your tasks Tom  publication:

Upgrading to SQL Server 2016: Post-upgrade tasks

by Thomas LaRock
https://thomaslarock.com/2017/04/upgrading-sql-server-2016-post-upgrade-tasks/
0
 
MishaConnect With a Mentor ProgrammerCommented:
Create backup of your database in mssql2005 and then restore this database in your mssql2013.
0
 
Eugene ZConnect With a Mentor Commented:
did you mean mssql2012 ?

it may be a more complected task for sql 2005 DB upgrade to sql 2012

as per above posted method
after restore you need to run several important processes
updateusuage; CheckDB with data_purity, reindex, update stats.

however, you need  to do proper prep work before you do ( including testing on some Non prod sql)
Use Upgrade Advisor to Prepare for Upgrades
https://msdn.microsoft.com/en-us/library/ms144256%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
see
Are you upgrading from SQL Server 2005?
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/are-you-upgrading-from-sql-server-2005?view=sql-server-2017


if you use Full-text search - -it is an extra steps
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
rayluvsAuthor Commented:
We don't want to upgrade the sql instances of sql2005, sql2008 nor sql2008 r2; these are located in other computers   and planning to stop using as soon as we can have those DB in our new sql2013 instance, which is in another computer.  We just want to pass these databases to sql2013.

The above said,

Misha,

Just copying over the 2008, 2008r2 and 2005 database over to sql2013 folder doing an attach would be enough?

Eugene,

Your links are directed more to upgrading the sql instances 2005/2008, we don't want that.  Does that mean that copying over the databases and doing an "attacted" is not enough?

If "attach" can be done, you said "several important processes updateusuage; CheckDB with data_purity, reindex, update stats."; can you provide the apps or command that is needed?


Thank you both.

Please Note:
for us the databases sql2008/2008r are more important.  If the databases of sql2005 are too complicated to bring over to sql2013, we can do without these sql2005 DB.
0
 
MishaProgrammerCommented:
After you back up your dataBase, will be created file with extension *.bak. And then you select this file, when you will restode your database in SQL Server latest version.
... if I correct understand you.
0
 
Eugene ZCommented:
it is the "main idea" - -to do prep work starting with running Upgrade Advisor and test on non prod server

DBCC UPDATEUSAGE(0)
DBCC CHECKDB WITH DATA_PURITY

DBCC DBREINDEX (see BOL , try DB main plan..)
or this code

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

Open in new window

0
 
rayluvsAuthor Commented:
Misha,

I see you don't recommend just a copy over then an "attach", rather backup in the source sql instances and restore "*.bak" in the sql2013.
Doing a restore, do I still have to run Eugene recommendations on of DBCC UPDATEUSAGE, DBCC CHECKDB, DBCC DBREINDEX?

Eugene,

Do I copy over the databases and "attach", then run you recommendations or do a backup "*.bak" from the source sql and restore in sql2013 then run your recommendations?
0
 
Eugene ZCommented:
there is not MS SQl 2013 version

2012 2014 2016

it is important to know for sure
--


and 'yes' - -you must run all DBCC after restore
I'd dod backup and restore to new sql server
to have a good backup plan in case you need to roll back
0
 
rayluvsAuthor Commented:
OMG!!! Sorry about that!! I meant sql2012 (v11.0.7001.0 SP4 to be exact)

There is no need of roll back for us.  The old sql instances are in use and are separate of sql2012 entirely and sql2012 is not in production (it's a test environment to assure all go well).

Ok, so attach is out of the question.

  • That means in order to "restore" we have to create all the database first in the sql2012 instance right? (they dont' exist there yet)

  • And finally, with the restore, you still recommend DBCC UPDATEUSAGE, DBCC CHECKDB, DBCC DBREINDEX afterward?
0
 
MishaProgrammerCommented:
You create new database in MS SQL Server2012 and restore from back up file.
0
 
rayluvsAuthor Commented:
Ok... (doing that right now... it's lots of databases)

What about my question, after doing the restore, do I still have to run DBCC UPDATEUSAGE, DBCC CHECKDB, DBCC DBREINDEX ?
0
 
rayluvsAuthor Commented:
Nice info! Just finished reading all three!

Ok, based on the docs, the best option and less time is Backup & Restore.

So, questions I keep asking and nobody answers:
  • To "Restore" we have to create all the database first in the sql2012 instance since it don;t exist there yet? Or is there a way that the "Restore" will create the database name?

  • After "Restore", based on EE recommendations in the begghing, do hace to DBCC UPDATEUSAGE, DBCC CHECKDB, DBCC DBREINDEX afterward?
0
 
rayluvsAuthor Commented:
You are correct, the first question on creating the database prior restore was answer by Misha and your entry now clearly answers the second.  Great info!

Thanx!
0
 
Eugene ZCommented:
have fun :)
0
 
rayluvsAuthor Commented:
Hahaha! Nice! Will do!

Will proceed to close the question.
0
 
rayluvsAuthor Commented:
Reviewing the question, found "if you use Full-text search - -it is an extra steps" - what addition steps? please provide link, we'll follow up on it.
0
 
rayluvsAuthor Commented:
Hey Eugene, just saw that you also answer the questions previous in D: 42560121 (didn't noticed it)
0
 
Eugene ZCommented:
make sure to install FTS component  (part of sql install)
FTS will be in .ndf file (part of DB)

Upgrade Full-Text Search (same idea)
https://docs.microsoft.com/en-us/sql/relational-databases/search/upgrade-full-text-search?view=sql-server-2017
0
 
rayluvsAuthor Commented:
Thanx!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.