May 25, 2011 at 4:23 pm
We have all of our data files on D:\ and our log files L:\.
I have to attach around 1k worth of databases and I was curious if anyone had or knew of TSQL/Powershell magic to do this?
Thank you.
May 25, 2011 at 5:01 pm
Not really. The commands are really simple but going about might be a little more complicated.
Step 1 is that it's really best to copy than move. If for some weird reason the move fail and you lose the file the DB's gone for good unless you have a backup.
If for some odd reason you can't simply do backup / copy .bak / restore, then I'd go with dettach, COPY, reattach, checkDB, reindex and stats if changing version. Then delete from the old server only when you're 100% sure the move is completed.
Going about it should be pretty straight forward, but I don't have a ready made script to give you.
May 26, 2011 at 10:26 am
Do you know if generating a list of databases in a directory then generating the attach code for that is feasible?
Basically it would prompt you to select the directory that your database files reside on then ask you for the directory of the log files and generate the TSQL needed..
Thoughts?
May 26, 2011 at 10:34 am
You can use xp_cmdshell to get a list of files but that doesn't tell you from which db it comes from. You'd need to look it up the the system tables.
From there on out you just need to figure out the command to do 1 DB and repeat the process for them all in a loop.
Just in the time you waited for an answer you could have done the move manually already. If you don't plan to do it again it "might" not be worth it to spend a lot of time scripting this over!
May 26, 2011 at 10:38 am
What I generally do for things like this:
You can generate one script, then get it to be in a single line of code.
Then use Excel/some spreadsheet to do the work for you in geenrating the rest.
For example, if column A has database names in it, the following formula would generate the command using the database name:
="ALTER DATABASE "&A1&" SET RECOVERY SIMPLE"
May 26, 2011 at 10:41 am
ryan.macy (5/25/2011)
We have all of our data files on D:\ and our log files L:\.I have to attach around 1k worth of databases and I was curious if anyone had or knew of TSQL/Powershell magic to do this?
Thank you.
I have also had to undertake this task and used this with some success: http://nclsqlclrfile.codeplex.com/ - please read the caveat on http://nclsqlclrfile.codeplex.com/discussions/257085
Essentially this is the code I used:
USE master
go
sp_detach_db 'DBNameGoesHere'
GO
[dbo].[MSPMoveFile]
--(with parameters)
WAITFOR DELAY '00:00:05'
go
--(to allow for the move/copy to complete)
USE master
go
sp_attach_db 'DBNameGoesHere', 'G:\MSSQL\Data\DBNameGoesHere.mdf', 'G:\MSSQL\Data\DBNameGoesHere_1.ldf'
go
USE [DBNameGoesHere]
go
sp_helpfile
go
gsc_dba
May 26, 2011 at 10:46 am
gsc_dba (5/26/2011)
ryan.macy (5/25/2011)
We have all of our data files on D:\ and our log files L:\.I have to attach around 1k worth of databases and I was curious if anyone had or knew of TSQL/Powershell magic to do this?
Thank you.
I have also had to undertake this task and used this with some success: http://nclsqlclrfile.codeplex.com/ - please read the caveat on http://nclsqlclrfile.codeplex.com/discussions/257085
Essentially this is the code I used:
USE master
go
sp_detach_db 'DBNameGoesHere'
GO
[dbo].[MSPMoveFile]
--(with parameters)
WAITFOR DELAY '00:00:05'
go
--(to allow for the move/copy to complete)
USE master
go
sp_attach_db 'DBNameGoesHere', 'G:\MSSQL\Data\DBNameGoesHere.mdf', 'G:\MSSQL\Data\DBNameGoesHere_1.ldf'
go
USE [DBNameGoesHere]
go
sp_helpfile
go
This came in handy too...
http://support.microsoft.com/kb/224071
gsc_dba
May 26, 2011 at 11:13 am
Ninja's_RGR'us (5/26/2011)
You can use xp_cmdshell to get a list of files but that doesn't tell you from which db it comes from. You'd need to look it up the the system tables.From there on out you just need to figure out the command to do 1 DB and repeat the process for them all in a loop.
Just in the time you waited for an answer you could have done the move manually already. If you don't plan to do it again it "might" not be worth it to spend a lot of time scripting this over!
I appreciate the suggestion. I however have a week or two before the move, so time isn't an issue.
I do have a small window in which I need to relocate these databases across the server and I need to mitigate human error as much as possible -- which for me is very high when I have to move and attach such a large number.
May 26, 2011 at 11:49 am
are you moving the databases across drives on the same server or between servers
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 26, 2011 at 1:40 pm
Between servers
May 26, 2011 at 2:06 pm
use this Script to reverse engineer the SQL to attach the databases on the new server
set quoted_identifier off
go
create proc usp_create_attach_db
as
--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 dbid, name from master.sysdatabases
where dbid > 4
open attach_cursor
fetch next from attach_cursor
into @dbid, @name
while @@fetch_status = 0
begin
select @filename1 = rtrim(filename) from master..sysaltfiles where fileid = 1 and dbid = @dbid
select @filename2 = rtrim(filename) from master..sysaltfiles where fileid = 2 and dbid = @dbid
print 'exec sp_attach_db ' +"'" +@name +"'," +"'" +@filename1 +"',"
print "'" +@filename2 +"'"
select @fileid = max(fileid) from master..sysaltfiles where dbid = @dbid
if @fileid > 2
begin
set @count = 3
while @count !> @fileid
begin
select @filename = rtrim(filename) from master..sysaltfiles where fileid = @count and dbid = @dbid
print ",'" +@filename +"'"
set @count = @count+1
end
end
select 'sp_changedbowner '+"'"+suser_sname(sid)+"'" from master..sysdatabases where dbid = @dbid
print ''
print '-------------------------next database-----------------------------'
print ''
fetch next from attach_cursor
into @dbid, @name
end
close attach_cursor
deallocate attach_cursor
apologies for the SQL2000 type code but I have this to hand and I know it works.
run this to produce the SQL to detach the databases
select 'exec sp_detach_db ' + name from master.sys.databases where database_id > 4
ensure no connections to the databases before detaching and use sp_help_revlogin to copy logins across.
do you have all the other server level objects covered (jobs, SSIS packages etc)
the above presumes the same directory structure on the new server
---------------------------------------------------------------------
May 27, 2011 at 1:12 pm
From Tim Ford's article "Rebuilding the SQL Server master database"
--CODE BEGIN
DECLARE @Command_Detach VARCHAR(1000)
SELECT @Command_Detach = '
IF N''[?]'' NOT IN(''model'' ,''master'', ''msdb'', ''tempdb'')
BEGIN
SELECT ''EXEC usp_KillDBConnections ''''?''''
EXEC sp_detach_db ''''?'''',''''true''''''
END'
EXEC sp_MSforeachdb @Command_Detach
--CODE END
Save the output of this query as 0.sql
Script Attach of all user databases as follows, saving the results as 1.sql
--CODE BEGIN
DECLARE @Command_Attach VARCHAR(1000)
SELECT @Command_Attach = '
IF N''[?]'' NOT IN(''model'' ,''master'', ''msdb'', ''tempdb'')
BEGIN
SELECT ''EXEC sp_attach_db ''''?'''',
'''''' + RTRIM(filename) + '''''',''
FROM [?]..sysfiles
WHERE fileid = (SELECT MIN(fileid) FROM [?]..sysfiles)
UNION ALL
SELECT '''''''' + RTRIM(filename) + ''''''''
FROM [?]..sysfiles
WHERE fileid > (SELECT MIN(fileid) FROM [?]..sysfiles) AND
fileid < (SELECT MAX(fileid) FROM [?]..sysfiles)
UNION ALL
SELECT '''''''' + RTRIM(filename) + ''''''''
FROM [?]..sysfiles
WHERE fileid = (SELECT MAX(fileid) FROM [?]..sysfiles)
END'
EXEC sp_msforeachdb @Command_Attach
--CODE END
May 27, 2011 at 3:43 pm
ryan.macy (5/26/2011)
Between servers
Is the storage on the same SAN - there are a lot of options that could be used from the SAN to simplify this process.
To give you an idea - we recently had to move a lot of servers from an old SAN to a new SAN. The SAN guys setup the storage foundation process which moved everything for us. Basically, they presented new LUNs to match the existing LUNs - implemented SAN replication which copied all of the data from each LUN.
At the scheduled time of the change, we brought down SQL Server - performed a final synchronization and restarted the server. When the server came back up - the old LUNs were swapped out, the new LUNs swapped in and everything came up on the new SAN.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2011 at 7:50 pm
Guys -- Great answers, exactly what I was looking for. I appreciate all of the replies. 😀
June 2, 2011 at 12:49 pm
In the end it looks like I will use a SSIS package and the copy database function in SSMS.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply