June 22, 2009 at 12:45 pm
I have 12 user databases on my SQL 2005 server. I would like to detach all of them and move to another instance runing on the same server. Yes the file name and path of the data and log files will remain the same. I am able to take care of the detach procss. now i want to automate the attach process on the instance. i was wondering, how can i put up a script file to do the attach? Is there a way to do this through a script file? if yes, can somebody please share?
Many thanks in advance,
June 22, 2009 at 2:00 pm
OK, this script is not for the purist as it still refers to sysaltfiles but it will work and also will reverse engineer the database owner for you, which could be important. I know there is a a script on this site which builds the create database for attach statements if you want to be fully SQL 2005 compliant, but I can't find it right now.
I presume you have less than 16 files per database otherwise this will fail.
use master
go
set quoted_identifier off
declare
@dbid smallint,
@name sysname,
@statement nvarchar(40),
@filename1 nvarchar(80),
@filename2 nvarchar(80)
declare attach_cursor cursor for
select database_id, name from sys.databases
open attach_cursor
fetch next from attach_cursor
into @dbid, @name
while @@fetch_status = 0
begin
select @filename1 = rtrim(filename) from sysaltfiles where fileid = 1 and dbid = @dbid
select @filename2 = rtrim(filename) from sysaltfiles where fileid = 2 and dbid = @dbid
print 'exec sp_attach_db ' +"'" +@name +"'," +"'" +@filename1 +"',"
print "'" +@filename2 +"'"
select 'sp_changedbowner '+"'"+suser_sname(owner_sid)+"'" from sys.databases where database_id = @dbid
print ''
print '-------------------------next database-----------------------------'
print ''
fetch next from attach_cursor
into @dbid, @name
end
close attach_cursor
deallocate attach_cursor
---------------------------------------------------------------------
June 22, 2009 at 4:16 pm
Centuries thanks a million for sharing the script. it did worked in my environment and easy my migration process by a ton.
Thanks agian you guys are genius.
June 22, 2009 at 5:00 pm
Glad it helped , thanks for the feedback.
Just noticed I in fact gave you an older version of the script that only handles two files, a data file and a log file, I presume that is the setup of all your databases. For your purposes I have also added a where clause to omit system databases as I presume you are not moving them! I've had to tweak it for SQL 2005 and can't test it right now so check it out. If the old one works for you then fine.
use master
go
set quoted_identifier off
set nocount on
declare
@dbid smallint,
@name sysname,
@statement nvarchar(40),
@filename nvarchar(80),
@filename1 nvarchar(80),
@filename2 nvarchar(80),
@fileid tinyint,
@count tinyint
declare attach_cursor cursor for
select database_id, name from sys.databases where database_id > 4
open attach_cursor
fetch next from attach_cursor
into @dbid, @name
while @@fetch_status = 0
begin
select @filename1 = rtrim(filename) from sysaltfiles where fileid = 1 and dbid = @dbid
select @filename2 = rtrim(filename) from sysaltfiles where fileid = 2 and dbid = @dbid
print 'exec sp_attach_db ' +"'" +@name +"'," +"'" +@filename1 +"',"
print "'" +@filename2 +"'"
select @fileid = max(fileid) from sysaltfiles where dbid = @dbid
if @fileid > 2
begin
set @count = 3
while @count !> @fileid
begin
select @filename = rtrim(filename) from sysaltfiles where fileid = @count and dbid = @dbid
print ",'" +@filename +"'"
set @count = @count+1
end
end
select 'sp_changedbowner '+"'"+suser_sname(sid)+"'" from sys.databases where database_id = @dbid
print ''
print '-------------------------next database-----------------------------'
print ''
fetch next from attach_cursor
into @dbid, @name
end
close attach_cursor
deallocate attach_cursor
---------------------------------------------------------------------
June 23, 2009 at 10:32 am
Thanks for modifying the cursor. Yes i wont be moving the system databases from my previous instance to new one. the newly provided script is working fine.
Thanks again for all your help.
June 24, 2009 at 7:39 am
Sorry I got to this one late. FWIW sp_attach_db is deprecated in favor of CREATE DATABASE FOR ATTACH.
I wrote this handy dandy sp that I use to basically sweep a directory and attach every database not in the instance's list of sysfiles from each database already on it.
CREATE PROCEDURE [dbo].[usp_attach_dir]
@dirname varchar(max)
AS
SET NOCOUNT ON
/***********************************************
Procedure: usp_attach_dir
Author: Mark Tassin
Date: 11/06/2008
Purpose: To attach all the databases in a directory that are not already attached to the SQL instance
Execution: usp_attach_dir ''
Outputs: DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The above message should repeat once for every database attached.
Requirements: SQL Server 2005 SP2+
************************************************/
/***** Test Data ************
DECLARE @dirname varchar(max)
set @dirname = 'E:\MSSQL\DATA'
********************************/
DECLARE @cmd nvarchar(max)
DECLARE @filename varchar(max)
DECLARE @dbname sysname
--Test for trailing backslash in the dirname parameter
IF right(@dirname,1) != '\' SET @dirname = @dirname + '\'
-- First we get a list of all the files in the input directory
CREATE TABLE #results(subdir varchar(max),depth int,isfile int)
set @cmd = N'insert into #results exec xp_dirtree ''' + @dirname +''',1,1'
EXEC sp_executesql @cmd
--Remove non-files that get picked up
delete from #results where isfile != 1
--Remove non-SQL data files
delete from #results where right(subdir,4) != '.mdf'
--Append the directory to the filenames
update #results
set subdir = @dirname + subdir
--Remove system database data files that are used by SQL server, but don't get actual database entries in sys.databases
DELETE FROM #results where subdir like '%\distmdl.mdf%' OR subdir like '%\mssqlsystemresource.mdf%'
--Create a table to store the output from sys.files for each db
CREATE TABLE #sysf(
[fileid] [smallint] NULL,
[groupid] [smallint] NULL,
[int] NOT NULL,
[maxsize] [int] NOT NULL,
[growth] [int] NOT NULL,
[status] [int] NULL,
[perf] [int] NULL,
[name] [sysname] NOT NULL,
[filename] [nvarchar](260) NOT NULL,
[dbname] [sysname] DEFAULT DB_Name()
) ON [PRIMARY]
--Get a list of all the files used by each db on the server
exec sp_msforeachdb '
insert into #sysf(fileid,groupid,size,maxsize,growth,status,perf,name,filename,dbname)
select fileid,groupid,size,maxsize,growth,status,perf,name,filename,''?'' from ?.dbo.sysfiles'
--Cursor to attach the dbs not on the server already
DECLARE csr_attachdbs CURSOR FAST_FORWARD FOR
SELECT
a.subdir
FROM #results a
WHERE
NOT EXISTS (SELECT * FROM #sysf b WHERE a.subdir = b.filename)
OPEN csr_attachdbs
FETCH NEXT FROM csr_attachdbs INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #fileinfo([property] sql_variant NULL, [value] sql_variant NULL)
SET @cmd = N'DBCC CHECKPRIMARYFILE (N''' + @filename + ''',2)'
INSERT INTO #fileinfo EXEC(@cmd)
SELECT @dbname = cast([value] as sysname)
FROM #fileinfo WHERE cast([property] as varchar)= 'Database name'
SET @cmd = N'CREATE DATABASE ' + @dbname + ' ON (FILENAME=''' + @filename + ''') FOR ATTACH_REBUILD_LOG'
EXEC sp_executesql @cmd
DROP TABLE #fileinfo
FETCH NEXT FROM csr_attachdbs INTO @filename
END
CLOSE csr_attachdbs
DEALLOCATE csr_attachdbs
DROP TABLE #results
DROP TABLE #sysf
This one will rebuild the log file as well. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply