ansaurus

Question

Answer 1

+2 A:

I would go for backup/restore as it leaves the original database in an operational state.

Especially if you're doing a 'production to test' conversion, it's important that the production database stays online.

Also, to me (although its more a feeling than anything else), backup/restore is "everyday work" whereas detach/attach is something you do in exceptional circumstances. Don't ask me where I got this idea though ;-)

Brimstedt 2009-09-25 17:35:07

Thanks Brimstedt! If downtime is not an issue, I think using attach/detach is better because it is faster than backup/restore?

George2 2009-09-26 09:29:49

Answer 2

+1 A:

Detaching the database will take it offline. Make a backup if you need the database to remain online while you copy it to another server.
Moving and restoring a backup file (.bak) may be simpler/easier than moving and attaching multiple mdf/ldf files (as you would if you detached the database).
Backup/restore will be faster than detach/attach

If you decide to backup/restore, use the WITH COPY_ONLY option during the backup to ensure that any existing maintenance plan's backup chain isn't broken.

A .bak file compresses well, so if you decide to go with making a backup, compressing the backup before moving it might save some transfer time.

CodeToaster 2009-09-25 17:41:49

A full backup will not include the log, but only the inflight transactional records that occured during the backup so that it can bring the database into a transactionally consistent state. It doens't take the whole log with it, there is no need.If it took the log and make VLF's inactive so that they cleared, your transactional log backup chain would be broken and you would be at significant risk.Only think that changes the log in that respect, is the log backup.

Andrew 2009-09-25 22:18:16

Backups do not contain loads of unused space. SQL only writes data from used pages into the backups so empty space in the data file does not make its way into the backup.They do compress very well, but that's not because they're mostly empty

GilaMonster 2009-09-26 08:11:28

Thanks CodeToaster, for backup file, how to "loads and loads of unused space"? Any special commands to use to free space while making backup?

George2 2009-09-26 09:34:00

"They do compress very well, but that's not because they're mostly empty" -- empty space is not unused space?

George2 2009-09-26 09:42:26

@Gilamonster I've updated my answer.

CodeToaster 2009-09-28 13:47:39

@George2 You can reduce the size of your backup using any ol' compression tool (like Winzip).

CodeToaster 2009-09-28 13:50:56

Answer 3

A:

I've always had issues with the "restore" part of backup/restore. I can't cite specifics as I eventually gave up on it and have been detatching/copying/attaching ever since.

The only thing about detach is that you HAVE HAVE HAVE to make sure you make sure the DBMS isn't also going to delete the database as well. Have had this happen, and its not a pretty sight.

Will 2009-09-25 17:42:53

The DBMS will not delete the database on detach. What kind of shop are you in if detach deletes files and restore has issues?

gbn 2009-09-25 18:15:25

What do you mean " make sure you make sure the DBMS isn't also going to delete the database as well"? Why "delete the database" relates to my question attach/detach v.s. backup/restore?

George2 2009-09-26 09:30:41

"what kind of shop are you in?" That question is goddamn bizarre. Look, let me say it again the same way as it cannot be clearer and I'm amazed that people can't grasp the concept--make sure when you detatch you don't drop and delete. Some DMBS' will have the option to drop and delete, and some of them may think that's your intent. Just be careful FFS. You asked for cons, that is one that I've experienced. If you aren't interested in con's, don't ask for them.

Will 2009-09-26 13:06:15

1. FFS is short for? 2. For detach/attach, in SSMS, when we detach the database, the database will not be displayed in SSMS (after detach, the database will not belong to the database instance, so), how could we drop or delete?

George2 2009-09-26 17:00:27

1) urban dictionary it. 2) SMSS has (I know in 2000 and pretty sure in 2k5) the option to delete the database when dropping. Don't have either installed so I can't sho repro steps.

Will 2009-09-27 02:42:47

"the option to delete the database when dropping" -- do you mean the option to delete the database when "detaching". I am confused.

George2 2009-09-27 03:05:29

Yes, it can be confusing. I guess that's my point.

Will 2009-09-27 13:14:20

Will, could you let me know which option in SSMS could be used to specify drop database while detaching? My confusion is when we detach, the database is no more in the SQL Server instances (i.e. from SSMS, you can never seen the detached database instance), so from this perspective, detach is the same as drop. Appreciate if you could let me know in SSMS where to specify drop database option?

George2 2009-09-27 17:16:10

@Will: sp_detach_db is not DROP: 2 separate and unrelated commands that have to be issued separately. A detached databases can not be DROPped or files deleted via SQL. A dropped database can not be detached. Detach does not have the "delete files" option via code or via SSMS. So, I can justify my first comment because you have to deliberately choose the option to delete the files on DROP. Not detach

gbn 2009-09-27 17:33:53

@Will: Don't post if you can't deal with folk questioning you about what they think are inaccurate or misleading answers

gbn 2009-09-27 17:34:37

Lol. As if I was reacting to the questioning and not the belligerent manner in which it was done. Or are you too stupid to understand that? (please note--that was a valid question asked in an unnecessarily confrontational manner in order to demonstrate the point).

Will 2009-09-28 14:17:05

@Will: Whatever.

gbn 2009-09-28 19:31:16

Answer 4

+2 A:

Backup/restore should normally be your method of choice.

You can use it consistently, also for production to test too.

See this SO question too, where the backup/restore vs detach/attach is mentioned

gbn 2009-09-25 18:14:28

Make sure you add the WITH COPY_ONLY option so it does not break the existing maintenance plan backup chain.

Remus Rusanu 2009-09-25 21:55:55

+1 to Remus - that is so important.

Andrew 2009-09-26 00:43:30

Thanks gbn! If downtime is not an issue, I think using attach/detach is better because it is faster than backup/restore?

George2 2009-09-26 09:28:23

Backup/restore will be quicker in most situations.

gbn 2009-09-27 13:45:24

Why? I have just experimented that restore takes about 10-20 mins, and attach just takes within 1 minute, for 1 100G footprint database.

George2 2009-09-27 17:13:56

backup-copy-restore vs detach-copy-attachx2? You took one minute to copy a 100 GB MDF from one server to another and the attach it...?

gbn 2009-09-27 17:23:29

Answer 5

A:

I recommend a "copyonly" backup using this method from a DOS shell (so that you dont interrupt transaction logs):

run from C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup directory:

backup.bat SQLDBNAME

where

backup.bat =

sqlcmd.exe -U username -P xxxxxxx -S SQL-SERVERNAME -Q "BACKUP DATABASE %1 TO DISK = '%1_COPYONLY.BAK' WITH COPY_ONLY,INIT;"

djangofan 2009-10-05 17:19:27

ansaurus

tags:

views:

answers:

attach/detach v.s. backup/restore

backup.bat =

related questions