---- We just do a FILEGROUP backup, not a FULL backup. This is the main
----point.
----
----You basically:
----a) Move your NCIXs to another filegroup
----b) Change your full db backup script to only backup the primary filegroup.
----
----That's it!
set nocount on
go
--create testdb
create database fgtest_prod
go
--create secondary NCIX_FG and file
use [master]
go
alter database [fgtest_prod]
add filegroup [ncix_fg]
go
alter database [fgtest_prod]
add file (
name = N'fgtest_prod_ncix'
, filename = N'd:\fgtest\fgtest_prod_ncix.ndf'
, size = 3072kb
, filegrowth = 1024kb ) to filegroup [ncix_fg]
go
use fgtest_prod
go
--create & populate test object
create table dbo.t1 (
c1 int not null primary key clustered
)
go
insert into dbo.t1 values (1)
go
--create ncix on NCIX_FG
create nonclustered index t1_ncix_1
on dbo.t1 (c1) on [ncix_fg]
go
--perform a full bak before FG bak is allowed
backup database fgtest_prod
to disk='d:\fgtest\fullbackup.bak'
with init
go
--backup primary FG
backup database fgtest_prod
filegroup='primary'
to disk='d:\fgtest\primary_fg.bak'
with init
go
--partially restore primary FG
restore database fgtest_restore
filegroup='primary'
from disk='d:\fgtest\primary_fg.bak'
with recovery, partial,
move 'fgtest_prod' to 'd:\fgtest\fgtest_restore.mdf',
move 'fgtest_prod_ncix' to 'd:\fgtest\fgtest_restore_ncix.ndf',
move 'fgtest_prod_log' to 'd:\fgtest\fgtest_restore_log.ldf'
go
use [fgtest_restore]
go
--add another ncix FG
alter database [fgtest_restore]
add filegroup [ncix_fg2]
go
alter database [fgtest_restore]
add file (
name = N'fgtest_prod_ncix2'
, filename = N'd:\fgtest\fgtest_prod_ncix2.ndf'
, size = 3072kb
, filegrowth = 1024kb ) to filegroup [ncix_fg2]
go
--rename existing index in offline FG
exec sp_rename 't1.t1_ncix_1', 't1.t1_ncix_1b'
go
--re-create index in new FG
create index t1_ncix_1 on t1 (c1) on [ncix_fg2]
go
--run query with index hint
select c1 from t1 with (index=t1_ncix_1)
go
--cleanup
use master
go
drop database fgtest_restore
go
drop database fgtest_prod
go
Best Regards,Uri Dimant SQL Server MVP,
http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting:
Large scale of database and data cleansing
Remote DBA Services:
Improves MS SQL Database Performance
SQL Server Integration Services:
Business Intelligence
