Detach and reattach query

  • [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]

  • 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

  • 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

  • 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

  • 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'

  • rpalacios 19022 (10/4/2010)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'''

    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

  • Duh!!!!

    I guess having a good, nutricious lunch makes a difference. Thanks for pointing out the obvious.

    -rp

  • 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

  • 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