Export database - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Export database

Author

Topic

henrik.andersson@goldware
Starting Member

6 Posts

Posted - 2005-06-03 : 05:27:27

Hi!

I need to archive a database in a way so that future generations can read the information.

I'm running SQL Server 2000 and the database contains a number of fields with the datatype text

What's the best approach?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-03 : 06:01:02

I doubt that future generations will have SQL Server 2000.
So I would bcp the data out to text files.
Also add the DDL (CREATE TABLE etc) for the database in a file.
Future Generations will probably have SQL

.

zip this up... (or maybe not, they might not have WinZip)

Storage media ?, (DVD/CD-ROM, durable ?, Paper in a bankvault, very durable I guess. Maybe both.)

Is the databse large ?

rockmoose

henrik.andersson@goldware
Starting Member

6 Posts

Posted - 2005-06-03 : 06:51:44

It's two databases that are rather large. And they contain text-datatype fields - so I can't use fixed width columns.

I have looked at XML, but SQL 2000 seems to use XDR schemas not XML-schema.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-03 : 07:37:54

bcp does not use fixed width columns.
Standard column and row terminators for bcp are newline and tab (\n,\t),
So You will have to watch out if the text columns contain the colum/row separators.

use northwind
go

create table q(col1 int, col2 text)
insert q
select 23,'my text is here...	... more text '
union all
select 42,'another chunk of text... 	.... .....	.....'

-- bcp out
exec master..xp_cmdshell N'bcp northwind..q out "C:\q.txt" -c -T -r "||"',no_output

truncate table northwind..q

-- bcp in
exec master..xp_cmdshell N'bcp northwind..q in "C:\q.txt" -c -T -r "||"',no_output

-- show the file
exec master..xp_cmdshell N'type "C:\q.txt"'

-- show the table
select * from northwind..q

drop table northwind..q

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources