June 19, 2008 at 12:27 pm
Hello.
Somebody knows if its possible to change the Compatibility Level automatically (to SQL 2005) from Copy Database Wizard (or some parameters in the comand line of the job?)
I need to copy some databases from SQL 2000 to SQL 2005 and set Compatibility Level to SQL 2005 automatically.
Tanks!
June 19, 2008 at 12:43 pm
Not automatically, but easy enough using SQL:
ALTER DATABASE db1 SET COMPATIBILITY_LEVEL = 90;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2008 at 1:43 am
So in other words, in SSIS after you do the transfer database, have a execute sql task which does the change in compatability level.
Sounds relatively automatic and straightforward
~PD
June 20, 2008 at 1:45 am
cgchavero (6/19/2008)
I need to copy some databases from SQL 2000 to SQL 2005 and set Compatibility Level to SQL 2005 automatically.Tanks!
Why not just restore a backup? It'sll probably be faster than the copy database wizard and easier to set up.
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
June 20, 2008 at 1:53 am
Fully agree Gila.
So I go on site to my support guys for my weekly pow wow, and tell them that I am ready for the next phase of our implementation, moving from Preproduction to Production.
Can they please restore the backups for me.
They respond that they dont have sufficient space to be doing backups. Naturally I fall off my chair and phone my boss and everyone runs around pointing fingers and blaming anyone in sight, but still the data remains on Preproduction.
In such cases, I am forced to use plan B, plan C and very often a combination of both (such as transferring database via a GUI and setting compatability level). I know the right way is restoring backups, but sometimes that right way is simply not at my disposal.
Interestingly enough, they still aint backing up, no matter how much squeeze my boss is putting on them.
~PD
June 20, 2008 at 1:59 am
pduplessis (6/20/2008)
Interestingly enough, they still aint backing up, no matter how much squeeze my boss is putting on them.~PD
Um..... <speechless>
They are planning on implementing backups once it becomes production. Right?
Restoring backup isn't necessarily the right way, just the easiest way. No matter which option you take, you'll have to set the compat mode to 90.
Another option - detach database in preprod. Copy files to production. Attach in production. Reattach the preprod one.
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
June 20, 2008 at 2:06 am
Its a good question, and this posting actually reminded me to send the follow up mail to them.
Sometimes you are restricted by some things that you really take for granted.
When I got asked to move to production, I took it for granted that backups are in play. Obviously in some countries like mine, the level of ops people they assign needs some work.
Anyways, its in the process of being escalated.
Methinks the detach/attach option will assist in this case. Now to convince these fellahs....
Rgs
Phil
June 20, 2008 at 2:35 am
pduplessis (6/20/2008)
Sometimes you are restricted by some things that you really take for granted.
I've learnt never to take anything for granted.
Obviously in some countries like mine, the level of ops people they assign needs some work.
South Africa, by any chance?
Guessing based on your surname.
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
June 20, 2008 at 3:00 am
For sure, lesson learnt.
Worst behind it all is that I am not a DBA, and only know enough to be slightly dangerous...
Yeap, South Africa. Get me not wrong, I am not saying the entire country has such environments or that all of us are bad at SQL, but finding the really good people is just that much more difficult.
ps> Just got confirmation that the backups issue will be resolved soon
Thanks Gila
June 20, 2008 at 3:08 am
pduplessis (6/20/2008)
Yeap, South Africa. Get me not wrong, I am not saying the entire country has such environments or that all of us are bad at SQL, but finding the really good people is just that much more difficult.
Me too, and I know the feeling.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply