September 20, 2012 at 3:26 am
Hello,
I am migrating a database from a SQL Server 2005 instance with a collation of SQL_Latin1_General_CP1_AS to an instance with a collation of Latin1_General_CI_AS. Due to the way the objects in the database are setup the way I plan on changing the collation of the database to Latin1_General_CI_AS is:-
1) Script all Indexes in DB
2) Script all Statisitics (not generated by indexes) in DB
3) Script all Constraints in DB
4) Drop all Indexes in DB
5) Drop all Statistics in DB
6) Drop all Constraints in DB
7) Alter column collation
8) Alter DB collation
9) Create all Constraints in DB
10) Create all Indexes in DB
11) Create all Statistics in DB
I have generated all the steps via stored procedures apart from the CREATE STATISTICS step. I know you can generate the CREATE STATISTICS statements via the SCRIPT DB wizard but would like to be able to do this from a stored procedure. Does anyone have a stored procedure that could do this?
Thank you,
Andrew
September 20, 2012 at 4:33 am
You could also try a different path.
There's an undocumented startup parameter that allows changing the collation for the whole instance, including all user databases attached to it.
You could set up a temporary instance with the original collation, restore there the database and restart the instance with the undocumented parameter.
The parameter is described here: http://spaghettidba.com/2011/05/26/changing-server-collation/
Hope this helps
Gianluca
-- Gianluca Sartori
September 20, 2012 at 8:58 am
Thank you, does this work for SQL Server 2005?
Regards,
Andrew
September 20, 2012 at 9:20 am
It worked for me many times on 2005.
-- Gianluca Sartori
September 20, 2012 at 9:22 am
Brilliant. I think it was just me being impatient. I can see in the log that the indexes in my DB are currently being restored.
Thank you very much!
Andrew
September 20, 2012 at 9:30 am
Be very, very careful with that option. It's undocumented for a reason, it does not always work, it's not completely tested. If it leaves your databases damaged, MS will offer no assistance.
For stats, unless you've created manual stats, I'd just leave off recreating them and letting SQL recreate them if it wants them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2012 at 10:00 am
GilaMonster (9/20/2012)
Be very, very careful with that option. It's undocumented for a reason, it does not always work, it's not completely tested. If it leaves your databases damaged, MS will offer no assistance.
Agreed.
Worth a try on a test database IMHO.
I've never seen this method failing, but I won't be surprised the day it does.
-- Gianluca Sartori
September 20, 2012 at 10:06 am
Definitely, the database has been migrated to a test environment and the startup parameter implemented.
The problem with the Stats was that there are user created stats in the database which need to be dropped so that the collation can be changed.
Andrew
September 20, 2012 at 10:08 am
Andrew Pruski (9/20/2012)
Definitely, the database has been migrated to a test environment and the startup parameter implemented.
So, I guess it worked, didn't it?
-- Gianluca Sartori
September 20, 2012 at 10:16 am
The log is still showing that SQL is still restoring indexes to the new collation. I'll check again tomorrow but hopefully this will work!
Andrew
September 20, 2012 at 10:18 am
In the meantime does anyone have a script that will generate CREATE STATISTICS statements in a DB?
Thank you,
Andrew
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply