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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MishaProgrammerCommented:
Create backup of your database in mssql2005 and then restore this database in your mssql2013.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.