January 15, 2007 at 2:06 pm
I need to make weekly dumps of my databases from my production server to another server on a different network, accessible through the Internet of course. Anyway, I was using a DTS package that uses the Import/Export functionality built into SQL Server 2000, however it keeps telling me that I cannot have duplicate keys, no surprise here, and it also bombs when referential integrity is breached. The database in question has had liked three different developers working with it, some of which, did not know sound design principles at all. My question is, what are some of you guys doing/using to offload your data from one server to the other?
Thanks in advance,
Jim
January 15, 2007 at 4:40 pm
What is the size of the database? If you need only data then you have to use dts or bcp..
If you complete db with schema and data then backup restore is the best option... I am using backup restore...
You can backup the database to multiple file and zip them befor copy and unzip them before restore...
If your company can efford you buy third party tools like LiteSpeed which will shrink your backup file to 80%...
MohammedU
Microsoft SQL Server MVP
January 15, 2007 at 5:54 pm
The database is really pretty small, less than 100 MB in size. I suppose I could script the database without primary/foreign keys, then simply dump the data. I just have to drop all of the records before dump the data or else I get a bunch of duplicates. I am not always the only person managing this particular database, so I was hoping that there was something pretty simple, so when it gets passed off and something breaks the person behind me will not be so overwhelmed that they will not try to fix any problems. I want to automate this as much as possible, so I am not sure that the backup/restore/zip/unzip his route I want to take.
January 15, 2007 at 10:03 pm
For this issue, I would use the database backup and restore option.
January 15, 2007 at 11:50 pm
If the db is 100 mb then backup the database and zip the file and restore it
OR
You can do snapshot replication too over internet...
MohammedU
Microsoft SQL Server MVP
January 16, 2007 at 7:58 am
have always used backup and restore ( lite speed with larger databases or where capacity and performance is an issue ). Use replication sometimes, but replicated databases can present their own issues with schema changes.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply