SharePoint move all databases to another machine

Refer:

Moving Sharepoint to a different SQL Server:

Http://www.toddklindt.com/blog/Lists/Posts/Post.aspx? List = 56f96349% 2d3bb6% 2d4087% 2d94f4% samples & 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

 

The customer needs to migrate the sharepoint2007 database sql2005 to another Windows Server 2008 R2 SQL 2008 R2.

Officially gave a clear solution

I use Microsoft's move all databases to a database server that has the same name

A script is written to facilitate backup.

The split function is used.

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

 

Backup script

 

View code use master

-- Set Condition
Exec sp_configure 'show advanced options', 1; reconfigure; Exec sp_configure 'xp _ Your shell', 1; reconfigure;
-- Exec master.. xp_mongoshell 'net use Z: \ rsitwebser \ D $ "password"/User: user'
-- Exec master.. xp_cmdshell 'net use Y: \ rsitwebser \ C $ "password"/User: 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

 

Restore script

 

View code use master

-- Set Condition
-- Exec sp_configure 'show advanced options', 1; reconfigure; Exec sp_configure 'xp _ cmdshell', 1; reconfigure;
-- Exec master.. xp_mongoshell 'net use Z: \ rsitwebser \ D $ "password"/User: user'
-- Exec master.. xp_cmdshell 'net use Y: \ rsitwebser \ C $ "password"/User: 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