SharePoint Move all Databases to another machine

pengjunling

　　参考：
　　Moving SharePoint to a different SQL server：
　　http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?List=56f96349%2D3bb6%2D4087%2D94f4%2D7f95ff4ca81f&ID=255&Web=48e6fdd1%2D17db%2D4543%2Db2f9%2D6fc7185484fc
　　Move all databases (Office SharePoint Server 2007)
　　http://technet.microsoft.com/en-us/library/cc512725(office.12).aspx
　　
　　因为客户需要，需要把SharePoint2007的数据库 SQL2005,迁移到另外一台  Windows server 2008 R2的SQL 2008 R2上
　　官方给了明确的解决方案
　　我采用的就是微软的 Move all databases to a database server that has the same name
　　为了方便备份，写了脚本
　　其中用了split函数
　　http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

View Code

CREATE FUNCTION [dbo].[Split]
(   
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @Iterator INT
    SET @Iterator = 1
    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))
        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END
   
    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData))
    RETURN
END　　
　　备份脚本
　　

View Code

use   master
    -- set condition
    EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;
    --exec master..xp_cmdshell 'net use z: \\rsitwebser\D$ "密码" /user:用户'  
    --exec master..xp_cmdshell 'net use y: \\rsitwebser\C$ "密码" /user:用户'  
   
    --set save position
    declare @savePosition nvarchar(max)
    set @savePosition='C:\'

    --Set backup  DataBase Names
    declare @DBNames nvarchar(max)
    set @DBNames='WSS_Content_SSP;WSS_Content'
begin
    -- Get the count
    declare @sumCount int
    select @sumCount = max(ID)  from [dbo].[Split](@DBNames,';')
    declare @se int
    set @se =1
    while @se <= @sumCount
    Begin
        declare @DBName nvarchar(max)
        select @DBName = [Data] from [dbo].[Split](@DBNames,';') where ID  = @se
        
        declare @fullName nvarchar(max)
        set @fullName =  @savePosition +@DBName +'.bak'
        declare @sql nvarchar(max)
        set @sql=' backup database ' + @DBName +' to disk ='''+@fullName  +''''   
        exec(@sql)      
        set @se =@se +1        
    end
end　　
　　还原脚本
　　

View Code

use   master
    -- set condition
    --EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;
    --exec master..xp_cmdshell 'net use z: \\rsitwebser\D$ "密码" /user:用户'  
    --exec master..xp_cmdshell 'net use y: \\rsitwebser\C$ "密码" /user:用户'  
   
    --set save position
    declare @backupFilePosition nvarchar(max)
    set @backupFilePosition='C:\'

    --Set backup  DataBase Names
    declare @FileNames nvarchar(max)
    set @FileNames='WSS_Content_SSP.bak;WSS_Content.bak'
   
    declare @databasePosition nvarchar(max)
    set @databasePosition='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'

begin
    -- Get the count
    declare @sumCount int
    select @sumCount = max(ID)  from [dbo].[Split](@FileNames,';')
    declare @se int
    set @se =1
    while @se <= @sumCount
    Begin
        declare @FileName nvarchar(max)
        select @FileName = [Data] from [dbo].[Split](@FileNames,';') where ID  = @se
        
        declare @DBName nvarchar(max)
        set @DBName = SUBSTRING(@FileName,0, len(@FileName)-3)
        
        declare @fullName nvarchar(max)
        set @fullName =  @backupFilePosition + @FileName
        declare @sql nvarchar(max)
        set @sql=' RESTORE DATABASE ' + @DBName + ' FROM DISK = '''+ @fullName  +
         ''' WITH MOVE '''    + @DBName +''' to '''  + @databasePosition + @DBName  +'.mdf'' ,' +
         ' Move ''' + @DBName +'_log'' to ''' + @databasePosition + @DBName +'.ldf'''
         print @sql   
        set @se =@se +1        
    end
end