from sql2005express to sql2000 & vice versa

My asp.net website is using sqlexpress2005. I am planning to use sql2000 with this existing website. My questions:

1. How can I transfer the database definitions and actual data from 2005 to 2000? is this possible?
2. What if I have exisiting database in sql2000, can I convert it to be used in sql2005?
3. should I manually recreate the database when converting from one version of sqlserver to another? and how abt the actual data?

PLS HELP. big thanks!

There are lots of options and lots of caveats...

When a SQL2000 database is attached/touched by a SQL 2005 instance it can not easy be moved back. To go from SQL2000->SQL2005 its as simple as back/restore or detach, move file, attach.

Moving data from one place there are lots of options, DTS is probably the easiest but it is not included in SQL2005 express, but it is included in full SKUs of SQL 2000 and the dev edition of SQL 2005.

Its also possible to use scripting to move meta data definitions, a 2000 script should work really easily with 2005, going back the other way is harder and there will be some enhancements coming in a future service pack of SQL Server 2005 to make the generate script wizard more useful here.

|||thanks for the info man! :)sql

From one server to another

Bear in mind that I am just a beginner and ingorance is bliss!

Two questions

1. Could someone tell me the most efficient (and easiest) way to move summurize and move data from one db on server A to another db on serverB. This process needs to run on an hourly basis.

If they were on the same server I would create a job under Management and use this code.

INSERT INTO tblNewTable
SELECT SUM(thiscolumn), SUM(thatcolumn), SUM(ETC)
FROM tblOld
WHERE columnDateTime > (SELECT MAX(columnDateTime) FROM tblNewTable)
GROUP BY DATEPART(hh, columnDate), Line, Shift

My problem is I THINK that you have to script the connection between the two severs and I dont know how to do this.

2. In tblNewTable, the data is used to report machine efficency for each line and shift. The problem is that the 3rd shift begins at 11PM, but its production is applied to the following day. I have used

WHERE columnDateTime >= DATEADD(hh, -1, StartDate) AND columnDateTime <= DATEADD(hh, -1, EndDate)

and leave off the GROUP BY columnDateTime to get the shift's data together.

Is it possible for 11 to 12 PM to be brought in as the following days date?

Thanks,
LeeQ1 [Do I have to script] the connection between the two severs? and I how [do I] do this.
A1 You might want to use linked servers (you could use osql if you really want to script a connection).

Q2 Is it possible for 11 to 12 PM to be brought in as the following days date?
A2 Yes, but you will need to code the logic to do so. (case and / or if statements, etc.)