sqlserver多文件组数据库的备份和还原实战

发布于 2019-09-26 作者 风铃 9次 浏览 版块 前端

    <h1 class=\"postTitle\">
        <a id=\"cb_post_title_url\" class=\"postTitle2\" href=\"http://www.cnblogs.com/l1pe1/p/5029656.html\">sqlserver多文件组数据库的备份和还原实战</a>
    </h1>
    <div class=\"clear\"></div>
    <div class=\"postBody\">
        <div id=\"cnblogs_post_body\"><p>数据库文件过大时就要进行数据分区,就是讲数据库拆分到多个文件组中。已方便数据文件管理,提高数据库的读取效能,多文件组如何进行数据库的备份和还原呢,今天主要做多文件组数据库的备份和还原实验。<br/><br/><br/>第一步<br/><br/>创建数据库qhw_test 数据库包括一个userinfo 数据表,userinfo数据表根据id做分区 包括一个主分区<br/><br/>,五个次分区,主分区包括qhw_test,data2两个文件,<br/><br/>数据表脚本如下<br/><br/>CREATE TABLE [dbo].[userinfo](<br/>    [Id] [int] IDENTITY(1,1) NOT NULL,<br/>    [UserName] [varchar](50) NOT NULL,<br/>    [CreateTime] [datetime] NOT NULL,<br/> CONSTRAINT [PK_userinfo] PRIMARY KEY CLUSTERED <br/>(<br/>    [Id] ASC<br/>)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, <br/><br/>ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)<br/>)<br/><br/>GO<br/><br/>SET ANSI_PADDING OFF<br/>GO<br/><br/>ALTER TABLE [dbo].[userinfo] ADD  CONSTRAINT [DF_userinfo_UserName]  DEFAULT (&#39;&#39;) FOR <br/><br/>[UserName]<br/>GO<br/><br/>ALTER TABLE [dbo].[userinfo] ADD  CONSTRAINT [DF_userinfo_CreateTime]  DEFAULT (getdate()) <br/><br/>FOR [CreateTime]<br/>GO<br/><br/><br/>往数据表中写入数据<br/><br/>第二部<br/><br/>然后执行数据库备份<br/><br/>使用完整备份模式一次备份数据库文件,先备份主分区,一次备份其他次分区,最后做事务日志备份<br/><br/>备份完成后,将备份文件转移至另一台机器 <br/><br/><br/>第三部<br/><br/>进行还原操作,先还原主分区,依次还原次分区,还原事务日志,注意NORECOVERY,<br/><br/><br/><br/>RESTORE DATABASE  qhw_test FILEGROUP = &#39;PRIMARY&#39;  FROM DISK = &#39;D:\\bak\\bak\\qhw_test1.bak&#39; <br/><br/>WITH FILE = 1,<br/>move &#39;qhw_test&#39; to &#39;d:\\bak\\qhw_test.mdf&#39;,<br/>move &#39;data2&#39; to &#39;d:\\bak\\data2.ndf&#39;,<br/>move &#39;qhw_test_log&#39; to &#39;d:\\bak\\qhw_test_log.ldf&#39;<br/>,<br/>NORECOVERY,REPLACE,STATS = 10<br/><br/><br/><br/>RESTORE DATABASE  qhw_test FILEGROUP = &#39;g1&#39;  FROM DISK = &#39;D:\\bak\\bak\\g1.bak&#39; WITH FILE = 1,<br/>move &#39;g1&#39; to &#39;d:\\bak\\g1.ndf&#39;,<br/> <br/><br/><span style=\"color: #ff0000;\">NORECOVERY,REPLACE,STATS = 10</span><br/><br/><br/><br/><br/>RESTORE DATABASE  qhw_test FILEGROUP = &#39;g2&#39;  FROM DISK = &#39;D:\\bak\\bak\\g2.bak&#39; WITH FILE = 1,<br/>move &#39;g2&#39; to &#39;d:\\bak\\g2.ndf&#39;,<br/> <br/><br/><span style=\"color: #ff0000;\">NORECOVERY,REPLACE,STATS = 10</span><br/><br/><br/><br/>RESTORE DATABASE  qhw_test FILEGROUP = &#39;g3&#39;  FROM DISK = &#39;D:\\bak\\bak\\g3.bak&#39; WITH FILE = 1,<br/>move &#39;g3&#39; to &#39;d:\\bak\\g3.ndf&#39;,<br/> <br/><br/><span style=\"color: #ff0000;\">NORECOVERY,REPLACE,STATS = 10</span><br/><br/><br/>RESTORE DATABASE  qhw_test FILEGROUP = &#39;g4&#39;  FROM DISK = &#39;D:\\bak\\bak\\g4.bak&#39; WITH FILE = 1,<br/>move &#39;g4&#39; to &#39;d:\\bak\\g4.ndf&#39;,<br/><span style=\"color: #ff0000;\">NORECOVERY,REPLACE,STATS = 10</span><br/><br/><br/>RESTORE DATABASE  qhw_test FILEGROUP = &#39;g5&#39;  FROM DISK = &#39;D:\\bak\\bak\\g5.bak&#39; WITH FILE = 1,<br/>move &#39;g5&#39; to &#39;d:\\bak\\g5.ndf&#39;,<br/><span style=\"color: #ff0000;\">NORECOVERY,REPLACE,STATS = 10</span><br/><br/>RESTORE LOG  qhw_test<br/>FROM DISK =  &#39;D:\\bak\\bak\\log.bak&#39;<br/>WITH NORECOVERY<br/>GO<br/><br/>RESTORE DATABASE  qhw_test<br/>WITH <span style=\"color: #ff0000;\">RECOVERY</span><br/><br/><br/>RESTORE DATABASE [&#39; + @DataBaseName + &#39;]<br/>WITH RECOVERY</p></div><div id=\"MySignature\"></div>

    </div>
<div class=&#34;postDesc&#34;>posted @ <span id=&#34;post-date&#34;>2015-12-08 17:07</span> <a href=&#34;http://www.cnblogs.com/l1pe1/&#34;>桃花雪</a> 阅读(<span id=&#34;post_view_count&#34;>…</span>) 评论(<span id=&#34;post_comment_count&#34;>…</span>) <a href=&#34;http://i.cnblogs.com/EditPosts.aspx?postid=5029656&#34; rel=&#34;nofollow&#34;>编辑</a> <a href=&#34;#&#34; onclick=&#34;AddToWz(5029656);return false;&#34;>收藏</a></div>

收藏
暂无回复