October 4, 2010 at 9:53 am
[font="Courier New"]Hello guys,
I have to migrate over 170 databases from one drive to another in the same SQL server. Does anyone have a script/query I can use to move (detach and reattach) these db's in bulk?
Thanks in advance,
-rp[/font]
October 4, 2010 at 9:59 am
If you are just moving them to another drive and keeping the same folder structure using ALTER DATABASE may be less overhead. See this link.
--This is from the link above
ALTER DATABASE database_name
MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
Once you execute the code above, just stop SQL Server services and move the database files. Then start SQL Server back up.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 4, 2010 at 11:00 am
Hello Shawn,
Thank you for the reply. Your recommended approach is for moving them 1 at a time. I'm looking for a way to either read from a file or a temporary table. I'd like to move more tha one at a time.
Thanks,
-rp
October 4, 2010 at 12:10 pm
You would create the script as the example shows, creating a command for each database. It would work the same way if you script out the dettach/attach process.
You can pull each section of the command from system views/tables and build the script in that manner if you wish. Your database name can be pulled from sys.databases. Your logical name and phyiscal file name can be pulled from sys.database_files
That is pretty much all you need.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 4, 2010 at 1:10 pm
Hello Shawn,
I guess someone else posted a similar query here a while back. User vidhya sagar provided a script to that essentially fetches all the user databases and sets them offline (below).
I have tested the script and even though it completes successfully, the databases are still online. I have doubled-checked the syntax and reviewed the sql and windows logs for any errors. There aren't any.
Any ideas?
Script:
set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in
('model','master','msdb','tempdb') and filename like 'S:\Data\Test%'
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
print 'ALTER DATABASE ' + @dbname + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'
print 'go'
print 'print ''Setting of ' + upper(@dbname) + ' database to OFFLINE successfully completed'''
print 'go'
PRINT ' '
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully set OFFLINE'''
Message Output:
ALTER DATABASE Test01 SET OFFLINE WITH ROLLBACK IMMEDIATE
go
print 'Setting of TEST01 database to OFFLINE successfully completed'
go
ALTER DATABASE Test02 SET OFFLINE WITH ROLLBACK IMMEDIATE
go
print 'Setting of TEST02 database to OFFLINE successfully completed'
go
print 'SERVER NAME : SNA9SVASA2I1--> All databases successfully set OFFLINE'
October 4, 2010 at 1:37 pm
rpalacios 19022 (10/4/2010)
set nocount ondeclare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in
('model','master','msdb','tempdb') and filename like 'S:\Data\Test%'
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
print 'ALTER DATABASE ' + @dbname + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'
print 'go'
print 'print ''Setting of ' + upper(@dbname) + ' database to OFFLINE successfully completed'''
print 'go'
PRINT ' '
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully set OFFLINE'''
The script is just going to output the actual script you need to run to accomplish the task. The task this is accomplishing is to simply change the databases to offline.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 4, 2010 at 2:45 pm
Duh!!!!
I guess having a good, nutricious lunch makes a difference. Thanks for pointing out the obvious.
-rp
October 5, 2010 at 8:34 am
Instead of all the PRINT statements (and cursor) this is what I had in mind.
exec sp_MSforeachdb '
USE ?
SELECT "ALTER DATABASE"
, DB_NAME()
, "MODIFY FILE ( Name ="
, name
, ", FILENAME = ''"
, physical_name
, "'')"
FROM sys.database_files
'
Of course it does return the code to move the system databases, but those lines could just be deleted.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 6, 2010 at 6:56 am
I have had to do this similar move recently. Below is my code and it borrows from a multitude of different gurus. After you run the script initially to get the script for changing file location and setting each user database offline then comment out the print @setofflinecmd and print @setnewpathcmd and uncomment print @setonlinecmd and rerun for the set online script. Please note that the script creates sql commands for all databases so you will need to go through and remove any databases you do not want to move(especially system!!!!).
----Declare variables
Use master
go
Set NoCount On
DECLARE @Query VARCHAR(2000)
DECLARE @Query2 VARCHAR(500)
DECLARE @max_counter INT
DECLARE @counter INT
DECLARE @dbname sysname
DECLARE @logfilename nvarchar(260)
DECLARE @datafilename nvarchar(260)
DECLARE @logpath nvarchar(200)
DECLARE @datapath nvarchar(200)
DECLARE @newlogpath nvarchar(200)
DECLARE @newdatapath nvarchar(200)
DECLARE @logicaldataname nvarchar(200)
DECLARE @logicallogname nvarchar(200)
DECLARE @setnewpathcmd nvarchar(2000)
DECLARE @setofflinecmd nvarchar(2000)
DECLARE @setonlinecmd nvarchar(2000)
-----Set new drive paths
SET @logpath = 'O:\OLOGS_901'
SET @datapath = 'E:\EDATA_606'
---Create TempTable to hold Physical File Info
CREATE TABLE #tmp_DBInfo
(
DBName sysname,
Logical_Filename sysname,
PhysicalFile_Path nvarchar(260),
PhysicalFile_Name nvarchar(260),
FileType nvarchar(4)
)
---Create TempTable to hold database names
Create Table #tmpdb_name(Counter int IDENTITY(1,1), name sysname)
---End of Create Table Statements
----Set Query variables 1 and 2 equal to select statements
Set @Query ='
select a.name DBname, b.name Logical_Filename,b.physical_name PhysicalFile_Path, right(b.physical_name,charindex(''\'',reverse(b.physical_name))-1) PhysicalFile_Name,
FileType = case b.type
when ''0'' then ''Data''
when ''1'' then ''Log''
Else ''Unknown''
End
from
sys.databases a join sys.master_files b
on a.database_id = b.database_id
Order by a.name'
Set @Query2 = 'Select distinct DBname
from #tmp_DBInfo'
-----Load both temp tables with database info
Insert into #tmp_DBInfo
(
DBName,
Logical_Filename,
PhysicalFile_Path,
PhysicalFile_Name,
FileType
)
Exec(@Query)
Insert into #tmpdb_name(name)
Exec(@Query2)
--------------------------------------Work Section-----------------------------------------------------
SET @max_counter = (SELECT Max(Counter) FROM #tmpdb_name)
----set counter at 1
SET @counter = 1
WHILE @counter > 0 AND @counter <= @max_counter
Begin
---load varables
Select @dbname = name from #tmpdb_name where Counter = @counter
Select @datafilename = PhysicalFile_Name from #tmp_DBInfo where DBName = @dbname and FileType = 'Data'
Select @logfilename = PhysicalFile_Name from #tmp_DBInfo where DBName = @dbname and FileType = 'Log'
Select @logicaldataname = Logical_Filename from #tmp_DBInfo where DBName = @dbname and FileType = 'Data'
Select @logicallogname = Logical_Filename from #tmp_DBInfo where DBName = @dbname and FileType = 'Log'
Set @newdatapath = @datapath+'\'+@datafilename
Set @newlogpath = @logpath+'\'+@logfilename
Set @setofflinecmd = 'Alter Database ['+@dbname+'] set offline with rollback immediate'+CHAR(13)+CHAR(10)+'GO'
Set @setnewpathcmd = 'Alter Database ['+@dbname+'] modify file (Name= '+@logicaldataname+', Filename= '''+@newdatapath+''')'+CHAR(13)+CHAR(10)+'GO'++CHAR(13)+CHAR(10)+'Alter Database ['+@dbname+'] modify file (Name= '+@logicallogname+', Filename= '''+@newlogpath+''')'+CHAR(13)+CHAR(10)+'GO'
Set @setonlinecmd = 'Alter Database ['+@dbname+'] set online'+CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
-----------------------------------------------------Uncomment or comment which script you need
print @setofflinecmd
print @setnewpathcmd
--print @setonlinecmd
---increment @counter by 1
SET @counter = @counter + 1
End
-----Test section
--Select * from #tmp_DBInfo
--Select * from #tmpdb_name
Drop table #tmp_DBInfo
Drop table #tmpdb_name
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply