May 10, 2004 at 2:38 pm
deratopic viewers,
I have been asked lately about the difference between attach DB and restore DB.
Well, i guess i really don't know.
Any help would be appreciated
May 11, 2004 at 1:06 am
Hi,
Basically the main difference is that when attaching DB you need all original database files or a copy of then and when restoring a database you are using a backup file and let the SQL Server create the files for you (in which location you select)
Regards,
May 11, 2004 at 8:00 am
When one uses DETACH and ATTACH, the physical files that comprise the database (data and log files) are "delinked" from the database. This is useful when you need to move the database to a different physical disk (I just did that this morning on 5 databases) without using DTS to copy all of the objects. When using ATTACH, you must have *all* of the physical files - SQL Server will know if you don't have all of them! Lastly, files that are being ATTACHed, are those that were previously DETACHed.
RESTORE is a bit different in that it is used on a "backup set" - a single file that contains both data and logs. It is created when using BACKUP (as opposed to DETACH). This is used when a database becomes corrupt, and you need to restore to a point in time where the database is not corrupt.
Basic rule of thumb: Use DETACH/ATTACH as a "Utility Tool" - one component of your toolset for quick maintenance. BACKUP/RESTORE are used for database recovery after corruption.
HTH,
-- Joe
May 11, 2004 at 9:36 am
thanks for these infos.
kevin
May 11, 2004 at 1:16 pm
So, there is not danger in make attach?
In the master database has not information about the database attached?
Felipe Cavalcante
May 12, 2004 at 4:58 am
I have been doing this dettach and attach, from development to Production environment.
I did not faced anyproblem. This is a handy procedure it reduces your file size. no need to keep log in this case. ( will be created on attaching)
May 12, 2004 at 10:50 am
1. Yes, the MASTER database contains information about the user database. But it's schema level information. The user database is self contained and can be moved to another SQL Server instance with no problem by using DETACH and ATTACH. I've done it without any trouble. Once you attach a database to a new instance, backup both the user database and the master databases.
2. When attaching just the database file (.mdf) and not the log file, you must use sp_attach_single_file_db in Query Analyzer or in Enterprise Manager, make sure the line for the log file is empty. I suggest always attaching both files unless your log file is corrupt.
-SQLBill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply