October 27, 2008 at 10:57 am
Hi,
I'm a new member and doing lotsa tutorials because we have finally brought SQL2005 into the organization (only three years behind the power curve) and I did this morning what I thought was a simple import of a small applications database from a 2K server to our
2K5 server. The application developer informed me that it went great (that was about 30 minutes after the import). Then I went to lunch and I came back and he said he had some issues -- none of the default values came across for any of the columns that had default values and none of the ID columns that were set to be identity columns came across set as identity columns.
Did I miss a setting or something? I used the import wizard, seemed like a simple job.
v/r
//SIGNED//
Stephen S. Wolfe, YA2, DAF
6th MDG Data Services Manager
6th MDG Information System Security Officer Comm (813) 827-9994 DSN 651-9994
October 27, 2008 at 11:02 am
If you used the import/export wizard, it will just bring the data across and create tables based on that data. No keys, no constraints, no indexes, no defaults, etc
For that you should probably first script the DB from 2000 and apply the script on 2005 and then use import/export or, much easier, restore a backup onto the 2005 server.
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
October 27, 2008 at 11:04 am
Use backup/restore. It will save a lot of time.
October 27, 2008 at 11:17 am
dont forget to run dbcc updateusage against each database and rebuild the indexes too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 27, 2008 at 11:51 am
I was wondering if it would be as easy as restoring a backup, so, I could essentially do that now with the plain jane version of the database I currently have on my SQL2005 server.
I'll try that. I'm inthe much learning mode now and my boss has promised me training in 2 - 3 months, by then I'll most of the big fish fried -- if you guys can put up with me that long.
Thanks!
October 27, 2008 at 12:21 pm
stephen.wolfe (10/27/2008)
I was wondering if it would be as easy as restoring a backup, so, I could essentially do that now with the plain jane version of the database I currently have on my SQL2005 server.
It pretty much is. You should rebuild indexes, run an update usage (it corrects inaccuracies in the space-used metadata) and if you can, a checkDB (DBCC CheckDB(< Database Name > ) WITH NO_INFOMSGS) after the upgrade to get everything running smoothly and make sure there are no unexpected surprises.
Please note that while 2000 - 2005 is simple, downgrading a 2005 db it 2000 is no where near as simple. A 2005 DB cannot be restored on to 2000. To downgrade requires that the schema be scripted and all the data exported and reloaded.
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
October 27, 2008 at 2:27 pm
GilaMonster (10/27/2008)
stephen.wolfe (10/27/2008)
I was wondering if it would be as easy as restoring a backup, so, I could essentially do that now with the plain jane version of the database I currently have on my SQL2005 server.It pretty much is. You should rebuild indexes, run an update usage (it corrects inaccuracies in the space-used metadata) and if you can, a checkDB (DBCC CheckDB(< Database Name > ) WITH NO_INFOMSGS) after the upgrade to get everything running smoothly and make sure there are no unexpected surprises.
Please note that while 2000 - 2005 is simple, downgrading a 2005 db it 2000 is no where near as simple. A 2005 DB cannot be restored on to 2000. To downgrade requires that the schema be scripted and all the data exported and reloaded.
Actually - you want to run a DBCC CHECKDB [database] WITH DATA_PURITY after the restore. From BOL:
DATA_PURITY
Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.
For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. 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. After this, DBCC CHECKDB checks column-value integrity by default. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.
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
October 27, 2008 at 2:39 pm
Good point.
The other reason to run checkDB is that on SQL 2000 checkDB did not include checkcatalog, which is why we so often see checkDB errors about inconsistent metadata on SQL 2005. Someone hacked the system tables on 2000, left them in a mess, SQL 2000 didn't notice, 2005 does.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply