Michael's Blog

罗马不设防

博客园 首页 新随笔 联系 订阅 管理

首先要谈谈SPS数据备份的概念,SPS自带站点备份和恢复功能,可以通过后台管理或stsadm命令使用。

今天这里说的是文档库(Document Library)数据的备份,是指把数据备份到常规的文件服务器。首先看下文档库的结构,文件库名称Projects,里面是以项目名字命名的文件夹,每个文件夹下有几十G数据,其中有不少上百MB的文件,整个WSS_Content数据库大小将近1TB。

当某个项目结束时,自动把项目文件从SPS挪到文件服务器,这是老板半年前布置的任务,可是一直没有头绪,特别是SPS对大文件(>50MB)支持不好,而我们经常会有上百MB的文件,点击下载都会出错,通过WEBDAV或者SPS Object Model编程备份文件的可靠性可想而知。

前几天突然想到绕过SPS,直接去数据库取数据,为了验证下自己的想法,特地去Google了下,果然,已经有人这么做过了。于是拿来主义,备份的问题基本上解决了(测试通过)。

后来,又出现了新的需求,把某一项目下的文件复制到另一个项目,同样,可以根据上面的思路解决,特此贴上代码,稍加注释。

文档库启用了版本管理,这里的备份只备份当前版本。

备份:

            string libraryURL = "http://sharepoint/Projects/MyProject/";
           
using (SqlConnection cn = new SqlConnection("server=sharepoint; Uid=sa; PWD=sa; database=WSS_Content;"))
            {
               
if (string.IsNullOrEmpty(libraryURL))
                   
return;
               
string backupURL = libraryURL.Substring(libraryURL.IndexOf("Projects/"));
                MessageBox.Show(backupURL);
                backupURL
= backupURL.ToLower();
               
string basePath = string.Empty;
               
// Back up project folder to the specified file server \\fs\projects\archive\
               
// The account running this code should have full control of this file share.
                basePath = @"\\sharepoint\temp";
                cn.Open();
                SqlCommand cmd
= new SqlCommand();
                cmd.CommandText
= "select distinct docstreams.id, dirname, leafname from docs inner join docstreams on docstreams.id=docs.id where dirname like 'Projects/MyProject/%' and iscurrentversion=1  order by dirname";
                cmd.Connection
= cn;
                SqlDataReader dataReader
= cmd.ExecuteReader();
               
// get all files
                while (dataReader.Read())
                {
                   
string DirName = dataReader.GetString(1);
                    DirName
= basePath + DirName.Replace("Projects", string.Empty).Replace("/", @"\");
                   
if (!Directory.Exists(DirName))
                    {
                        Directory.CreateDirectory(DirName);
                    }
                   
string LeafName = dataReader.GetString(2);
                   
// get file content and save to back up location
                    using (SqlConnection cn2 = new SqlConnection("server=sharepoint; Uid=sa; PWD=sa; database=WSS_Content;"))
                    {
                        cn2.Open();
                        SqlCommand fileCmd
= new SqlCommand();
                        fileCmd.Connection
= cn2;
                        fileCmd.CommandText
= "select Content from docstreams where id='" + dataReader.GetGuid(0).ToString() + "'";
                        SqlDataReader fileReader
= fileCmd.ExecuteReader();
                       
if (fileReader.Read())
                        {
                            FileStream fs
= new FileStream(DirName + "\\" + LeafName, FileMode.Create, FileAccess.Write);
                            BinaryWriter writer
= new BinaryWriter(fs);
                           
int bufferSize = 3082240;// chunk size 30MB
                            long startIndex = 0;
                           
long retval = 0;
                           
byte[] outByte = new byte[bufferSize];
                           
do
                            {
                                retval
= fileReader.GetBytes(0, startIndex, outByte, 0, bufferSize);
                                startIndex
+= bufferSize;
                                writer.Write(outByte,
0, (int)retval);
                                writer.Flush();
                            }
                           
while (retval == bufferSize);
                            writer.Close();
                            fs.Close();
                        }
                        fileReader.Close();
                        cn2.Close();
                    }
                }
                dataReader.Close();
                cn.Close();
            }
            MessageBox.Show(
"Back up FINISHED");
           
// delete this project folder totally.
            SPSite fab = new SPSite(libraryURL);
            SPWeb fabweb
= fab.OpenWeb();
            SPFolder folder
= fabweb.GetFolder(libraryURL);
            folder.Delete();
            MessageBox.Show(
"Folder DELETED");

复制:

            string source = textBox1.Text;
           
string target = textBox2.Text;
            source
= source.Trim();
            target
= target.Trim();
           
if (!(source.EndsWith("/") && target.EndsWith("/")))
            {
                MessageBox.Show(
"URL must end with /");
               
return;
            }
            label3.Text
= "Result: COPYING";
           
string sourceName = "/" + source.Split(new char[] { '/' })[source.Split(new char[] { '/' }).Length - 2] + "/";
           
string targetName = "/" + target.Split(new char[] { '/' })[target.Split(new char[] { '/' }).Length - 2] + "/";
            SPSite site
= new SPSite(source);
            SPWeb web
= site.OpenWeb();
           
using (SqlConnection cn = new SqlConnection("server=sharepoint; Uid=sa; PWD=sa; database=WSS_Content;"))
            {
               
string DBUrl = source.Substring(source.IndexOf("Projects/"));
                cn.Open();
                SqlCommand cmd
= new SqlCommand();
                cmd.CommandText
= "select distinct docstreams.id, dirname from docs inner join docstreams on docstreams.id=docs.id where dirname like '" + DBUrl + "%' and iscurrentversion=1 order by dirname";
                cmd.Connection
= cn;
                SqlDataReader dataReader
= cmd.ExecuteReader();
               
// get all files
                while (dataReader.Read())
                {
                    SPFile file
= web.GetFile(dataReader.GetGuid(0));
                   
string folderPath = "http://sharepoint/Projects/" + file.Url.Replace(sourceName, targetName).Substring(0, file.Url.Replace(sourceName, targetName).LastIndexOf("/")) + "/";
                    SPFolder folder
= web.GetFolder(folderPath);
                   
while (!folder.Exists)
                    {
                       
string[] folders = folderPath.Split(new string[] { "/" }, StringSplitOptions.RemoveEmptyEntries);
                       
int index = 0;
                       
for (int i = 4; i < folders.Length; i++)
                        {
                           
// take care, urls like /123/123 and 123/rr/123 will result in endless loop.
                           
// so record the position you are creating.
                            index = folderPath.IndexOf("/" + folders[i] + "/", index);
                           
if (folders[i] == folders[i - 1])
                            {
                                index
+= 1;
                            }
                           
if (!web.GetFolder(folderPath.Substring(0, folderPath.IndexOf("/" + folders[i] + "/", index)) + "/" + folders[i] + "/").Exists)
                            {
                                web.GetFolder(folderPath.Substring(
0, folderPath.IndexOf("/" + folders[i - 1] + "/", index - folders[i - 1].Length -2)) + "/" + folders[i - 1] + "/").SubFolders.Add(folders[i]);
                            }
                        }
                    }
                   
try
                    {
                       
// do not overwrite
                        file.CopyTo(file.Url.Replace(sourceName, targetName), false);
                    }
                   
catch (Exception exp)
                    {
                       
//skip when file type is blocked or file is checked out
                        continue;
                    }
                }
            }
            web.Dispose();
            site.Dispose();
            label3.Text
= "Result: Copied Successfully";
posted on 2008-12-26 21:49  Michael Zhang  阅读(884)  评论(0编辑  收藏  举报