partial backup/restore

最新推荐文章于 2025-08-10 13:24:43 发布

转载最新推荐文章于 2025-08-10 13:24:43 发布 · 106 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/stswordman/archive/2010/12/25/1916656.html

文章标签：

#数据库

本文介绍如何使用SQL Server的部分数据库可用性功能来进行针对性恢复。通过创建包含多个文件组的示例数据库并备份整个数据库，演示了如何仅恢复主文件组和其他特定文件组，最后将数据库恢复到所需的时间点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12771/Using-Partial-Database-Availability-for-Targeted-Restores.aspx

Listing 1: Code to Create a Database with Multiple File Groups

CREATE DATABASE ExampleDB;
GO

ALTER DATABASE ExampleDB ADD FILEGROUP TestFileGroup1;
ALTER DATABASE ExampleDB ADD FILEGROUP TestFileGroup2;
GO

ALTER DATABASE ExampleDB ADD FILE (
      NAME = TestFile1,
      FILENAME = ' C:\SQLskills\TestFile1.ndf ' ,
      SIZE = 5MB)
TO FILEGROUP TestFileGroup1;
ALTER DATABASE ExampleDB ADD FILE (
      NAME = TestFile2,
      FILENAME = ' C:\SQLskills\TestFile2.ndf ' ,
      SIZE = 5MB)
TO FILEGROUP TestFileGroup2;
GO

CREATE TABLE ExampleDB..t1 (c1 INT ) ON TestFileGroup1;
CREATE TABLE ExampleDB..t2 (c1 INT ) ON TestFileGroup2;
GO

INSERT INTO ExampleDB..t1 VALUES ( 1 );
INSERT INTO ExampleDB..t2 VALUES ( 2 );
GO

BACKUP DATABASE ExampleDB TO DISK = ' c:\SQLskills\ExampleDB.bck ' ;

--BACKUP DATABASE Sales FILEGROUP = 'primary' TO DISK = 'C:\SQLskills\ExampleDBPrimaryFG.bck'-- you can only backup the primary filegroup which contains all the definiton of objects,users...
GO

Now on a different system you can restore just the primary file group and the first file group. The key is to restore the primary file group first using WITH PARTIAL to let SQL Server know you aren’t restoring the entire database, as shown in Listing 2.
Listing 2: Restoring the Primary File Group Using WITH PARTIAL

RESTORE DATABASE ExampleDB
FILEGROUP = ' primary '
FROM DISK = ' c:\SQLskills\ExampleDB.bck '
WITH PARTIAL, NORECOVERY;
GO

RESTORE DATABASE ExampleDB
FILEGROUP = ' TestFileGroup1 '
FROM DISK = ' c:\SQLskills\ExampleDB.bck '
WITH NORECOVERY;
GO

Next, you can restore any differential and/or transaction log backups to get the restored file groups to the desired point in time, and then bring the database online using the following code:
RESTORE DATABASE ExampleDB WITH RECOVERY;
If I query the database using the code in Listing 3, it will show me what’s online.

Listing 3: Code Used to Determine Which File Groups Are Online

Now, of course, I can only use the portions of the database that I’ve restored. If I try to access anything in an offline file group, I’ll get an error similar to the following:

http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12771/Using-Partial-Database-Availability-for-Targeted-Restores.aspx

转载于:https://www.cnblogs.com/stswordman/archive/2010/12/25/1916656.html