WSS文档库备份及复制

首先要谈谈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";