I haven’t had time to do any Denali testing since my last post, so instead I thought I’d share a script I wrote a while ago which simply generates a script to attach one or more databases using their current file paths (and optionally then detaches these databases).
The reason I wrote this script in the first place was because we have a number of environment (i.e. dev, test, stage, reporting, etc), and some of these databases are made up of quite a few database files (i.e. more than a dozen or so).
So on the odd occasion when I’ve needed to detach a database, move some files around, and re-attach said database using the Management Studio GUI, I’ve found it frustrating having to manually browse to each file’s location over and over again. And I just prefer doing stuff in T/SQL over using the GUI – makes me feel more in control I think.
So, with this script I could easily generate the “attach” code for each database (including all file paths), make any path or filename changes necessary, and re-attach all the databases with a single mouse click!
USE [master]; DECLARE @database NVARCHAR(200), @cmd NVARCHAR(1000), @attach_cmd NVARCHAR(4000), @file NVARCHAR(1000), @i INT; DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR SELECT RTRIM(LTRIM([name])) FROM sysdatabases WHERE [dbid] > 4 -- exclude system databases OPEN dbname_cur FETCH NEXT FROM dbname_cur INTO @database WHILE @@FETCH_STATUS = 0 BEGIN SELECT @i = 1; -- Initial attach command stub SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10) + 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10); -- Get a list of files for this database DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR SELECT physical_name FROM sys.master_files WHERE database_id = db_id(@database) ORDER BY [file_id]; OPEN dbfiles_cur FETCH NEXT FROM dbfiles_cur INTO @file WHILE @@FETCH_STATUS = 0 BEGIN SET @attach_cmd = @attach_cmd + ' ,@filename' + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + '''' + CHAR(10); SET @i = @i + 1; FETCH NEXT FROM dbfiles_cur INTO @file END CLOSE dbfiles_cur; DEALLOCATE dbfiles_cur; -- Output "attach" command. PRINT @attach_cmd; -- DETACH (uncomment the following line at your peril - IT WILL DETACH ALL USER DATABASES!) /*EXEC sp_detach_db @dbname = @database, @skipchecks = 'true'; -- change this to false if you want it to update stats before detaching*/ FETCH NEXT FROM dbname_cur INTO @database END CLOSE dbname_cur; DEALLOCATE dbname_cur;
The usual disclaimers apply; I’ve used this in a limited number of scenarios so I wouldn’t consider it thoroughly tested, and if you run it in a production environment and “accidentally” detach databases, or otherwise break something – don’t blame me.