asked on

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?

SOLUTION

Misha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jana

ASKER

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.

Misha

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.

EugeneZ

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

jana

ASKER

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?

EugeneZ

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

jana

ASKER

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?

Misha

You create new database in MS SQL Server2012 and restore from back up file.

jana

ASKER

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 ?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jana

ASKER

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?

ASKER CERTIFIED SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jana

ASKER

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!

EugeneZ

have fun :)

jana

ASKER

Hahaha! Nice! Will do!

Will proceed to close the question.

jana

ASKER

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.

jana

ASKER

Hey Eugene, just saw that you also answer the questions previous in D: 42560121 (didn't noticed it)

EugeneZ

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

jana

ASKER

Thanx!