SharePoint Move all Databases to another machine
参考：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 .
(
@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 backupDataBase Names
declare @DBNames nvarchar(max)
set @DBNames='WSS_Content_SSP;WSS_Content'
begin
-- Get the count
declare @sumCount int
select @sumCount = max(ID)from .(@DBNames,';')
declare @se int
set @se =1
while @se <= @sumCount
Begin
declare @DBName nvarchar(max)
select @DBName = from .(@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 backupDataBase 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 .(@FileNames,';')
declare @se int
set @se =1
while @se <= @sumCount
Begin
declare @FileName nvarchar(max)
select @FileName = from .(@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
页:
[1]