Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Difference between Backup/Restore and Detach/Attach

Posted on 2004-09-28
5
Medium Priority
?
1,673 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: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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

916 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