DTS vs sp_detachdb vs BACKUP/Restore 
Author Message
 DTS vs sp_detachdb vs BACKUP/Restore

I've seen several postings here about copying/moving databases and I am
curious as to which method is considered to be the most reliable and/or
efficient.

DTS can transfer a database from one SQL Server to another - I have done
this and it worked fine. I have not used the sp_detachdb et al yet and, to
be honest, have not had a chance to read up on it. Backup/Restore are
basically just new commands for what used to be Dump/Load. But which method
is best? Does it depend on the situation?

--
Mike MacGregor
Principal Consultant
TygerCon Inc.
Tel: 519-579-5276
Fax: 519-579-7360
Cell: 416-568-4805

www.tygercon.com



Sun, 02 Dec 2001 03:00:00 GMT
 DTS vs sp_detachdb vs BACKUP/Restore

Mike,

People will differ. For development data, I'll use DTS on small database
transfers but not large ones.

For production databases, I'll always make a backup, copy the backup file,
and then restore. The reason is that it seems the most cautious.

If the data files are extremely large and it's not production data, I've
used sp_detachdb/attachdb and then copied the files.

Ron
Ron Talmage
SQL Server MVP, MCSD, MCP

Quote:

>I've seen several postings here about copying/moving databases and I am
>curious as to which method is considered to be the most reliable and/or
>efficient.

>DTS can transfer a database from one SQL Server to another - I have done
>this and it worked fine. I have not used the sp_detachdb et al yet and, to
>be honest, have not had a chance to read up on it. Backup/Restore are
>basically just new commands for what used to be Dump/Load. But which method
>is best? Does it depend on the situation?

>--
>Mike MacGregor
>Principal Consultant
>TygerCon Inc.
>Tel: 519-579-5276
>Fax: 519-579-7360
>Cell: 416-568-4805

>www.tygercon.com



Sun, 02 Dec 2001 03:00:00 GMT
 DTS vs sp_detachdb vs BACKUP/Restore
There are reasons for each technique... if all you're concerned about is
speed... then sp_attachdb and the related commands should always be faster.
If you've moving the entire data set, DTS would be the slowest option but
would provide lot's of custom flexibility. Backup/Restore is slower than
sp_attachdb but has the advantage of keeping the db "up" so users can
connect...

I'm not aware of any data integrity issues that could be caused by using
sp_attachdb compared to backup/restore for production data. Does anyone have
any interesting experiences on that topic?

--
Brian Moran

Director of Database Technology
CIBER Washington D.C.
MCSE, MCSD, SQL Server MVP
Windows NT Magazine Columnist
President, Capital Area
SQL Server Users Group


Quote:
> I've seen several postings here about copying/moving databases and I am
> curious as to which method is considered to be the most reliable and/or
> efficient.

> DTS can transfer a database from one SQL Server to another - I have done
> this and it worked fine. I have not used the sp_detachdb et al yet and, to
> be honest, have not had a chance to read up on it. Backup/Restore are
> basically just new commands for what used to be Dump/Load. But which
method
> is best? Does it depend on the situation?

> --
> Mike MacGregor
> Principal Consultant
> TygerCon Inc.
> Tel: 519-579-5276
> Fax: 519-579-7360
> Cell: 416-568-4805

> www.tygercon.com



Sun, 02 Dec 2001 03:00:00 GMT
 DTS vs sp_detachdb vs BACKUP/Restore
Mike,

I agree with all the contributors to this thread but I'd like to point out
one advantage of DTS over the other two methods: it will allow you to move
data from one server to another where the processor architecture/sort
order/character set are different.

Kieran


Quote:
> I've seen several postings here about copying/moving databases and I am
> curious as to which method is considered to be the most reliable and/or
> efficient.

> DTS can transfer a database from one SQL Server to another - I have done
> this and it worked fine. I have not used the sp_detachdb et al yet and, to
> be honest, have not had a chance to read up on it. Backup/Restore are
> basically just new commands for what used to be Dump/Load. But which
method
> is best? Does it depend on the situation?

> --
> Mike MacGregor
> Principal Consultant
> TygerCon Inc.
> Tel: 519-579-5276
> Fax: 519-579-7360
> Cell: 416-568-4805

> www.tygercon.com



Mon, 03 Dec 2001 03:00:00 GMT
 DTS vs sp_detachdb vs BACKUP/Restore
Thanks guys!

--
Mike MacGregor
Principal Consultant
TygerCon Inc.
Tel: 519-579-5276
Fax: 519-579-7360
Cell: 416-568-4805

www.tygercon.com



Mon, 03 Dec 2001 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Please help...Backup vs Transaction log vs Restore is so important

2. Please help...Backup vs Transaction log vs Restore is so important

3. Access vs Approach vs Paradox vs FilePro vs Filemaker Pro

4. Replication vs Backup/Restore

5. replicate complete DB vs backup/restore

6. Moving SQL7 to New Computer - Backup / Restore vs sp_detach_db / sp_attach_db

7. DB Restore and Trans Log vs.Differential Backup question

8. times of ontape backup vs ontape restore

9. times of ontape backup vs ontape restore

10. sql server 7 dts vs. backup

11. SP vs View vs Function vs Trigger

12. Sql Server vs Oracle vs Ibm vs....


 
Powered by phpBB® Forum Software