172.文件及文件组的备份及还原

谁践踏了优雅 2024-01-20 06:44 31阅读 0赞

--以下代码简单地演示了如何进行文件组的备份及还原（在还原时，模拟了丢失第二次文件组备份文件的情况）。
    --创建测试数据库
    CREATE DATABASE db
    ON PRIMARY(
    	NAME='db_data',
    	FILENAME= 'c:\db_data.mdf'),
    FILEGROUP db_fg1(
    	NAME = 'db_fg1_data',
    	FILENAME = 'c:\db_fg1_data.ndf'),
    FILEGROUP db_fg2(
    	NAME = 'db_fg2_data',
    	FILENAME = 'c:\db_fg2_data.ndf')
    LOG ON(
    	NAME='db_log',
    	FILENAME ='c:\db.ldf')
    GO
    
    --在文件组db_fg1上创建表，并单独创建该文件组的备份
    CREATE TABLE db.dbo.tb(id int) ON db_fg1
    BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1.bak' WITH FORMAT
    GO
    
    在其他文件组上创建表 
    CREATE TABLE db.dbo.ta(id int) ON [PRIMARY]
    CREATE TABLE db.dbo.tc(id int) ON db_fg2
    INSERT db.dbo.tb SELECT id FROM sysobjects
    --备份每个文件组，并且备份事务日志
    BACKUP DATABASE db FILEGROUP='PRIMARY' TO DISK='c:\db_primary.bak' WITH FORMAT
    BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1_new.bak' WITH FORMAT
    BACKUP DATABASE db FILEGROUP='db_fg2' TO DISK='c:\db_fg2.bak' WITH FORMAT
    BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT
    GO
    
    --删除数据库
    DROP DATABASE db
    GO
    
    --从文件组备份中恢复数据
    RESTORE DATABASE db FILEGROUP='PRIMARY' FROM DISK='c:\db_primary.bak' WITH NORECOVERY,REPLACE
    RESTORE DATABASE db FILEGROUP='db_fg1' FROM DISK='c:\db_fg1.bak' WITH NORECOVERY
    RESTORE DATABASE db FILEGROUP='db_fg2' FROM DISK='c:\db_fg2.bak' WITH NORECOVERY
    RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY
    SELECT COUNT(*) FROM db.dbo.tb
    GO
    
    --删除测试数据库
    DROP DATABASE db