SQL Server 2008 mdf/ldf gotten to a 2005 box

We are on SQL Server 2000 Enterprise and are planning to upgrade to '08 Enterprise later this month. One of our issues is that we send an .mdf each night to a university that is using SQL server 2005. They are currently able to attach our 2000 version .mdf to their 2005 instance so this hasn't been an issue. The university will not be upgrading until later this year so I would like to know if there is a way that I can get the database to them without requiring them to change anything on their end.

The file we send is about 1Gb (1054Mb). The process needs to be automated on our end. The only possibility I have seen is to export the database and data as a script and specify explicitly that it should be compatible with the earlier versions. Is this the only way? If so should I just write a DTS that exports the script with the parameter and then have my 2000 box look for that script file?

The key is that I don't have to involve the university in any changes on their end. I want to be able to send them the same .mdf and .ldf they have been getting for the last year.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

They will not be able to attach a 2008 mdf file to a 2005 instance.

The way to do this is with replication. The only problem will be if you start using the new data types in 2008 (e.g. date)
This will also help with speed if the university doesn't need all the data - you could just replicate the tables they need.

here is an article showing the various options of replicating between different versions

Mark WillsTopic AdvisorCommented:
As gothamite has said, you will not be able to use a more recent version of SQL database with the prior version of SQL Server. Backup wont help either.

No problems the other way around (within reason).

Now, gothamite also suggest replication, which is a worthy consideration to make it more automated from server to server. But you dont really have to worry about replication if you dont want to because it is one way traffic and can just as readily be scripted in 2008 and "pushed".

What you might be able to do is to link to your SQL 2000 box and "push" the data over a linked server. Or, use the export wizard on your 2008 server to export the data and then import on your 2000 box. You can then detach the database and send as usual.

So, your option of running the export and then importing into your 2000 server I would say is the best way to go.

And as you have found, there are not too many choices...

thefarmAuthor Commented:
I did a test to see how long it would take to export the db with data for 2000 and it took about 40 minutes and I ended up with a 6.5 Gb .sql file. This process would have to be scripted and I've not been able to find a resource for that but the task is so cpu intensive i don't think it's worth pursuing, although scripted it might not consume so many cycles. Then I'd have to move it to a nonproduciton server, slurp it into the waiting 2000 db which I imagine would take a bit to run. Then detach it, zip the mdf and ldf and ftp them to the university. I'm going to look at replication and see if that wouldn't save me from having to do the scripting part and reimport part. Wouldn't the log files grow considerably doing all those inserts?

This is a lot of work considering the university is going to be upgrading this summer to 2008...lol.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mark WillsTopic AdvisorCommented:
Not if you do bulk insert - and you could keep the backup SQL2000 db at simple recovery...

With replication, the first time / getting it established will be pretty intensive, but after that, it will be basically the new activity.

Is it just data - or is it also structure (like indexes, FK, constraints etc) ?

Is it all tables ?
Jim P.Commented:
Have you looked at the Microsoft SQL Server Database Publishing Wizard 1.1?

Not ideal, but a workaround....

thefarmAuthor Commented:
We have a dts package set up to drop the tables in the destination db and then recreate and copy the data over. There are no SPs, indexes, constraints or keys that we're moving.  This runs each night.
The destination database doesn't get backed up but can 2008 do a bulk insert into a sql server 2000 db?

I don't think DPW will work with 2008. The site mentions 2005 and 2000 only
Jim P.Commented:
The DPW just creates a giant SQL script(s) to recreate the tables and/or data. It should  be compatible with any version from SQL 7 on up. The major scripting thing that changed between versions is the large text fields going from the Text data type to the Varchar(Max) and some other things of that nature.
Anthony PerkinsCommented:
If you can connect directly to the destination server you may want to look into purchasing the Red-Gate tools:
SQL Compare®
SQL Data Compare™

If they fit the bill you should be able to automate it using:
SQL Comparison SDK™
thefarmAuthor Commented:
The solution needs to run without my intervention nightly for a limited number of months (until someone upgrades to SQL 2008). So Third Party purchases are out. We don't have money for it. DPW and the 2008 Export tool aren't scriptable, that I've seen, so I'm thining I will use Replication and keep it as simple as possible.

I'm looking at the Central Publisher Model and use a Transactional Replication which is initialized form a backup. It'll run nightly until the other party upgrades their server. At the very least the experience setting this up may come in handy if we start a using a data warehouse or if we need to seperate our some users to their own db.
Mark WillsTopic AdvisorCommented:
Sounds like a plan.

And it will be very valuable experience moving forward.

Besides the fall back is to simply transfer data (or create a linked server and push the data through) and we can do that anytime...

Anthony PerkinsCommented:
>>The solution needs to run without my intervention nightly for a limited number of months (until someone upgrades to SQL 2008). So Third Party purchases are out.<<
Than you are obviously did not read what I posted.

>>We don't have money for it. <<
So you value your time you have spent on this already and the time you will spend less than a few hundred dollars?  That IMHO is very short-sighted.

>>so I'm thining I will use Replication and keep it as simple as possible. <<
I cannot see how Replication is going to help you.  But if it does more power to you.
Jim P.Commented:
From the command prompt:
C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing>SqlPubWiz.exe /?
Microsoft (R) SQL Server Database Publishing Wizard
Copyright (c) Microsoft Corporation.  All rights reserved.

sqlpubwiz script ( -C connection_string | -d local_database_name ) (output_file) [switches]
sqlpubwiz publish ( -C connection_string | -d local_database_name )  ( web_service_options ) [switches]

For more information type

        sqlpubwiz help script
        sqlpubwiz help publish


Open in new window

Jim P.Commented:
http:#32343350 Author:gothamite
http:#32344410 Author:mark_wills

I would say those seem like the most likely suspects.
thefarmAuthor Commented:
The other end upgraded to sql server 2008 and out process was able to move forward without a hitch.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Commented:
Then I say delete -- refund. :-p
thefarmAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.