Solved

Difference between Backup/Restore and Detach/Attach

Posted on 2004-09-28
5
1,319 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
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:adilkhan
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 100 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:adilkhan
ID: 12174066
lol
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql server query? 6 28
Calculating Business Hours 19 65
SQL Split character from numbers 3 18
BULK INSERT most recent CSV 19 20
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now