• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1732
  • Last Modified:

Difference between Backup/Restore and Detach/Attach

Experts,

I've used both of these methods to copy, move and/or resurrect databases, and each seems effective, but I've never really seen these two methods compared together. Can anyone compare / contrast the advantages / disadvantages of using backup / restore vs. detach / attach? I'm especially interested in situations  where one method would be better to use than the other plus any potential pitfalls and things to watch out for.

Thanks
0
tjnoles
Asked:
tjnoles
  • 2
  • 2
1 Solution
 
Saqib KhanSenior DeveloperCommented:
Well Backup is good for scheduleing.
I schedule my backups everynight using BACKUP which is not possible with detach/Attach(requires your database to go offline).
plus if you detach you need to make sure NO users are connected to your DB while in backup case it does not matter.

Good luck.
0
 
arbertCommented:
The biggest difference is DETACH/ATTACH has to take the database offline affecting your user's ability to access data (or not access data).

Backup/Restore makes a copy of the database while the database is ONLINE.  Theoretically, the users aren't blocked or locked  out while this happens.

The biggest plus I see to detaching/attaching is that it's faster than a backup restore.  You simply detach, and copy the files.

The bigges downside to a detach/reattach is everytime you detach the database, you risk the database not reattaching.  Granted, it doesn't happen very often, but it does happen.
0
 
geotigerCommented:

This article gives enough information to distinguish the two methods and the advantage and disadvantage of the two: http://support.microsoft.com/default.aspx?scid=kb;en-us;314546&Product=sql2k

Some highligts are:

Backup and Restore

1. The database can be in use while the backup is in process. If users perform INSERT, UPDATE, or DELETE statements on the database after the backup completes, the backup will not contain these changes. If you must transfer all changes, you can transfer the changes with minimal downtime if you perform both a transaction log backup and a full database backup.

2. The database on the destination server will be the same size as the database on the source server. To reduce the size of the database, you must either shrink the source database before you perform the backup, or shrink the destination database after the restore completes. For more information, see the "Shrinking a Database" sub-topic in the "Creating and Maintaining Databases" heading of SQL Server Books Online.

3. If you restore the database to a different file location than the source database, you must specify the WITH MOVE option. For example, on the source server the database is in the D:\Mssql\Data folder. The destination server does not have a D drive, and you want to restore the database to the C:\Mssql\Data folder. For additional information about how to restore a database to a different location, click the article numbers below to view the articles in the Microsoft Knowledge Base:

4. If you want to overwrite a pre-existing database on the destination server, you must specify the WITH REPLACE option. For more information, see the "RESTORE" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.

5. Depending on the version of SQL Server to which you restore, the character set, sort order, and Unicode collation may have to be the same on both the source and destination servers. For more information, see the "Note About Collation" section of this article.

Sp_detach_db and Sp_attach_db Stored Procedures

1. The database is inaccessible after the detach and you cannot use the database while you copy the files. All data contained in the database at the point in time of the detach is moved.

2. The character set, sort order, and Unicode collation may have to be the same on both servers when you use the Attach or Detach method. For more information, see the "Note About Collation" section of this article.
0
 
arbertCommented:
Ah, the magic of search engines....
0
 
Saqib KhanSenior DeveloperCommented:
lol
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now