Copy Large Database

Does anyone know of a good way to copy a large database or backup of a database (~10 GB) across long distances (transatlantic) in a minimal amount of time?

I have tried regular file copies, BCP, DTC, my own handwritten extraction utility, PCAnywhere, and several file splitters, all to no avail. To complicate things, the source is SQL 7 with a different code page from the destination, which is SQL 2K. The file copies always seem to get interrupted after several hours.How about some form of replication. That way the live data is now checked and the copy come from a backup.|||Thanks for the response. The situation here is that these are 24x365 production databases with high levels of activity that support manufacturing production, so replicating on an ongoing basis will be costly to the site. I'd really prefer some method of capturing a snapshot.

Add to that the fact that since it is 24x365, getting time to try things is at a premium. Many of the solutions I spoke of earlier worked well in test environments where the geographical distances were a few hundred miles, but broke down when going cross-country or transatlantic.|||Dump and zip and ftp?|||Yeah, I think that's about the only thing left. Docs all say that DUMP and BACKUP are synonymous; is that true?|||yeah...dump is a 6.5 term...just habit...

Did you mention something about a different collation?

If it is, I don't think you'll be able to RESTORE it...|||if you use SQLLitespeed (or other utilities allowing compression while backup is being taken)- you can always compress it on the fly here, ace or rar the resulting backup , ftp it over and extract on the other side.

simas

Copy Database

I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive. When I drag and drop the file it tells me "insufficient amount of space" even though the hard drive has plenty of storage. Please help
Hi,
Can you please post your query to Windows 2000 public group.
Thanks
Hari
MCDBA
"Johnson" <anonymous@.discussions.microsoft.com> wrote in message
news:97CC8EA4-9B6E-422C-AB0A-A017FBE603BB@.microsoft.com...
> I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive.
When I drag and drop the file it tells me "insufficient amount of space"
even though the hard drive has plenty of storage. Please help

Copy Database

I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive. Wh
en I drag and drop the file it tells me "insufficient amount of space" even
though the hard drive has plenty of storage. Please helpHi,
Can you please post your query to Windows 2000 public group.
Thanks
Hari
MCDBA
"Johnson" <anonymous@.discussions.microsoft.com> wrote in message
news:97CC8EA4-9B6E-422C-AB0A-A017FBE603BB@.microsoft.com...
> I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive.
When I drag and drop the file it tells me "insufficient amount of space"
even though the hard drive has plenty of storage. Please help

Copy Database

I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive. When I drag and drop the file it tells me "insufficient amount of space" even though the hard drive has plenty of storage. Please helpHi,
Can you please post your query to Windows 2000 public group.
Thanks
Hari
MCDBA
"Johnson" <anonymous@.discussions.microsoft.com> wrote in message
news:97CC8EA4-9B6E-422C-AB0A-A017FBE603BB@.microsoft.com...
> I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive.
When I drag and drop the file it tells me "insufficient amount of space"
even though the hard drive has plenty of storage. Please help

Copy data for reports

I need to copy a significant amount of data (1-2gb) during work hours
to another database for some lengthy numeric calculations and a report.
Phantom rows inserted during the copy operation would be a problem but
a serializable transaction would impact availability. I was
considering using BCP to export the data with a query since I think it
would be faster than directly copying across databases with t-sql. The
BCP docs dont say anything about other users inserting during a bulk
export. Are inserts prevented during bulk export? Is BCP a good way
to go? Other options? I must support sql2k and cannot rely on new 2005
features.
Jim KaneYou could restore or re-attach a backup of the production database.
<jkane@.satx.rr.com> wrote in message
news:1134740711.836936.47380@.g47g2000cwa.googlegroups.com...
>I need to copy a significant amount of data (1-2gb) during work hours
> to another database for some lengthy numeric calculations and a report.
> Phantom rows inserted during the copy operation would be a problem but
> a serializable transaction would impact availability. I was
> considering using BCP to export the data with a query since I think it
> would be faster than directly copying across databases with t-sql. The
> BCP docs dont say anything about other users inserting during a bulk
> export. Are inserts prevented during bulk export? Is BCP a good way
> to go? Other options? I must support sql2k and cannot rely on new 2005
> features.
> Jim Kane
>

Converting numeric data type to text data type

Hi,

I would like to convert a dollar amount ($1,500) to represent Fifteen
hundred dollars and 00/100 cents only for SQL reporting purposes. Is
this possible and can I incorporate the statement into an existing
left outer join query.

Thanks in advance,

Gavin[posted and mailed, please reply in news]

Gavin (gsegal@.mps.com) writes:
> I would like to convert a dollar amount ($1,500) to represent Fifteen
> hundred dollars and 00/100 cents only for SQL reporting purposes. Is
> this possible and can I incorporate the statement into an existing
> left outer join query.

If it is for reporting issues, it may be better to do this on client
level, but you could use a table to hold the various strings. I would
suggest that it will be simpler to implement, if you permit 1500 to be
rendered as "One thousand five hundred".

The table would look like this:

CREATE TABLE numberstrs (nr tinyint NOT NULL
CONSTRAINT ck_nr CHECK (nr BETWEEN 0 AND 99),
str varchar(23) NOT NULL,
CONSTRAINT pk_nr PRIMARY KEY (nr))
go
INSERT numberstrs (nr, str) VALUES (0, '')
INSERT numberstrs (nr, str) VALUES (1, 'one')
...
INSERT numberstrs (nr, str) VALUES (99, 'ninety-nine')

You would then use it as

SELECT CASE WHEN mil.str IS NOT NULL
THEN mil.str + ' millions and '
ELSE ''
END +
CASE WHEN hth.str IS NOT NULL
THEN mil.str + ' hundred '
ELSE ''
END +
CASE WHEN th.str IS NOT NULL
THEN th.str + ' thousand and '
ELSE ''
END +
CASE WHEN hun.str IS NOT NULL
THEN hun.str + ' hundred '
ELSE ''
END +
one.str + ' dollars ' +
ltrim(str((a.amt * 100) % 100)) + '/100 cents'
FROM tbl a
JOIN numberstrs one ON a.amt % 100 = nr
LEFT JOIN numberstrs hun ON (convert(int, a.amt) / 100) % 10 = nr
LEFT JOIN numberstrs th ON (convert(int, a.amt) / 1000) % 100 = nr
LEFT JOIN numberstrs hth ON (convert(int, a.amt) / 100000) % 10 = nr
LEFT JOIN numberstrs mil ON (convert(int, a.amt) / 1000000) % 100 = nr

This is something I made up, and I have not tested it. I don't think
the result will be that excellent for all numbers. For instance 101
would not come out pretty if you want it as "One hundred and one".
For even values like "One million" you need to add some logic.

Notice that there is a upper limit of 100 millions as I have written the
query. You would also have to arrange for the first letter in the
resulting string to be uppercase.

You mention how would incorporate into an existing query, but since I
don't see that query and don't where you want the value, I'm not taking
a stab at that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp