?
Solved

Difference between Backup/Restore and Detach/Attach

Posted on 2004-09-28
5
Medium Priority
?
1,624 Views
Last Modified: 2008-01-09
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
Comment
Question by:tjnoles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12173705
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
 
LVL 34

Expert Comment

by:arbert
ID: 12173717
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
 
LVL 12

Accepted Solution

by:
geotiger earned 400 total points
ID: 12173883

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
 
LVL 34

Expert Comment

by:arbert
ID: 12173982
Ah, the magic of search engines....
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12174066
lol
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question