Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
qjam
Starting Member
19 Posts |
 Posted - 2007-01-17 : 18:19:37
|
| Hi all,I recently found out that there is no easy way to distribute a MSSQL 2005 database to MSSQL Server 2000. Most forums that I have read say to use the DTS facility to import the data into the SQL 2000, however this is not an option (for a variety of reason I won't go into).The next best option in my mind is to script the entire database, including the data into one or a few script files then run them on the SQL 2000 server to recreate the database. Unfortunately, exporting the data, what I thought would be a fundamental feature, isn't part of SQL 2005. So does anybody know of a good (free) scripting program that will allow me to export the entire database from a server? I've tried:- Free program from the CodeProject.com (program dies when there's more than 5000 lines) http://www.codeproject.com/dotnet/ScriptDatabase.asp-SQL Scripter www.sqlscripter.com (Doesn't script table which don't have primary keys, and produces a script for each object instead of just a single file)I'd probably like a program which only creates a single script, as the database has over 200 tables and I don't want to have to go through this process everytime I need to distribute the DB (which will be often). The only other option I can think of is a program which converts an SQL 2005 backup to a 2000 version.Thanks for your help! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-17 : 20:00:45
|
| Easier to bcp the data out and in.You can script all the tables easily then the data isSomething like (probably mistakes but should be close) select 'exec master..xp_cmdshell ''bcp mydb..' + name + ' out c:\' + name + '.txt /N /S' + @@servername + ''''from sysobjects where xtype = 'U'Run that and then run the result and it should bcp everything out to files (change the folder)Change the out to in and you will have the bcp in commands which you can add to the script.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Exporting Data