November 14, 2017 at 9:31 am
Hello,
A have to restaure a huge database (PROD) on a smaller environment (STAGE).
My database is split in filegroups, I know I can use the RESTAURE WITH MOVE syntax to set differents locations for some specific filegroups, but is there a way to restaure a filegroup to nothing (indeed, to skip a filegroup) ?
Thanx for your help (and sorry for my poor english, I'll put that in my signature).
Johan Jezequel
** Sorry for my english, I promess you I do my best **
November 14, 2017 at 10:41 am
Johan_1975 - Tuesday, November 14, 2017 9:31 AMHello,A have to restaure a huge database (PROD) on a smaller environment (STAGE).
My database is split in filegroups, I know I can use the RESTAURE WITH MOVE syntax to set differents locations for some specific filegroups, but is there a way to restaure a filegroup to nothing (indeed, to skip a filegroup) ?
Thanx for your help (and sorry for my poor english, I'll put that in my signature).
The database would need to be in a consistent state after the restore so generally trying to find a way to not restore all data would lead to database inconsistency.
Is the issue the space needed for the database or the time it takes to do the restore?
If it's time, you may want to look at the problems from the backup and what options you have there. Things such as third party backups, SAN technology can offer different ways to backup and restore quickly.
If space is the issue and you can't get more disk space then you probably need to think about ways to get the least amount of data in the database over to the other environment but enough so that they can test or validate what is needed. One option would be to script the database (just the database objects, not the data) and then create the database with no data in the lower environment. Then take the time to figure out what data you can load from production into the database you just created which would include things like what do the users need, what are the relationships between tables that would affect what data and what order to move into the lower environment. You could do that through SSIS, bcp, etc.
So I guess some of what you do depends upon what problem you are trying to solve. And solutions can depend upon what is available in your environment (such as backup software).
Sue
November 14, 2017 at 1:03 pm
off course you can, play with the code.
CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'y:\tmp\test.mdf' , SIZE = 5MB , FILEGROWTH = 1024KB ),
FILEGROUP [RO_FG]
( NAME = N'test2', FILENAME = N'y:\tmp\test2.ndf' , SIZE = 5MB , FILEGROWTH = 1024KB ),
FILEGROUP [RW_FG]
( NAME = N'test3', FILENAME = N'y:\tmp\test3.ndf' , SIZE = 5MB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'y:\tmp\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM
GO
USE [test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'RW_FG')
ALTER DATABASE [test] MODIFY FILEGROUP [RW_FG] DEFAULT
GO
create table abc(col1 int)
insert into abc(col1) values (1)
create table xyz(col1 int) on RO_FG
insert into xyz(col1) values (9)
use master
ALTER DATABASE [test] MODIFY FILEGROUP [RO_FG] READONLY
use test
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
GO
-- FULL filegroup backup
use master
backup database test
filegroup = 'Primary'
TO DISK = 'y:\tmp\test_FG_Primary.bak' WITH INIT
-- Full filegroup backup
backup database test
filegroup = 'RO_FG'
TO DISK = 'y:\tmp\test_FG_RO.bak' WITH INIT
-- Full filegroup backup
backup database test
filegroup = 'RW_FG'
TO DISK = 'y:\tmp\test_FG_RW.bak' WITH INIT
-- MUST TAKE LOG BACKUP
BACKUP LOG [test] TO DISK = N'y:\tmp\TLOG.bak' WITH INIT
GO
use master
drop database test
go
>>>>>>>>>>>>>>>>>>>>>> Piecemeal Restore
-- restore full file group primary
restore database test
filegroup = 'Primary'
from DISK = 'y:\tmp\test_FG_Primary.bak'
with PARTIAL, norecovery
-- restore full file read write
restore database test
filegroup = 'RW_FG'
from DISK = 'y:\tmp\test_FG_RW.bak'
WITH NORECOVERY
-- restore full file read only
restore database test
filegroup = 'Ro_Fg'
from DISK = 'y:\tmp\test_FG_RO.bak'
WITH NORECOVERY
restore DATABASE TEST FROM DISK = 'y:\tmp\tlOG.bak'
November 14, 2017 at 1:09 pm
goher2000 - Tuesday, November 14, 2017 1:03 PMoff course you can, play with the code.CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'y:\tmp\test.mdf' , SIZE = 5MB , FILEGROWTH = 1024KB ),
FILEGROUP [RO_FG]
( NAME = N'test2', FILENAME = N'y:\tmp\test2.ndf' , SIZE = 5MB , FILEGROWTH = 1024KB ),
FILEGROUP [RW_FG]
( NAME = N'test3', FILENAME = N'y:\tmp\test3.ndf' , SIZE = 5MB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'y:\tmp\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM
GO
USE [test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'RW_FG')
ALTER DATABASE [test] MODIFY FILEGROUP [RW_FG] DEFAULT
GOcreate table abc(col1 int)
insert into abc(col1) values (1)
create table xyz(col1 int) on RO_FG
insert into xyz(col1) values (9)use master
ALTER DATABASE [test] MODIFY FILEGROUP [RO_FG] READONLY
use testSELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
GO-- FULL filegroup backup
use master
backup database test
filegroup = 'Primary'
TO DISK = 'y:\tmp\test_FG_Primary.bak' WITH INIT-- Full filegroup backup
backup database test
filegroup = 'RO_FG'
TO DISK = 'y:\tmp\test_FG_RO.bak' WITH INIT
-- Full filegroup backupbackup database test
filegroup = 'RW_FG'
TO DISK = 'y:\tmp\test_FG_RW.bak' WITH INIT-- MUST TAKE LOG BACKUP
BACKUP LOG [test] TO DISK = N'y:\tmp\TLOG.bak' WITH INIT
GOuse master
drop database test
go>>>>>>>>>>>>>>>>>>>>>> Piecemeal Restore
-- restore full file group primary
restore database test
filegroup = 'Primary'
from DISK = 'y:\tmp\test_FG_Primary.bak'
with PARTIAL, norecovery
-- restore full file read write
restore database test
filegroup = 'RW_FG'
from DISK = 'y:\tmp\test_FG_RW.bak'
WITH NORECOVERY-- restore full file read only
restore database test
filegroup = 'Ro_Fg'
from DISK = 'y:\tmp\test_FG_RO.bak'
WITH NORECOVERYrestore DATABASE TEST FROM DISK = 'y:\tmp\tlOG.bak'
Which filegroup are you skipping on the restore?
Sue
November 14, 2017 at 1:12 pm
(any execpt primary) .. thats why i said play with the code
to make it more clear to understand..
-- restore full file group primary
--MUST RESTORE PRIMARY
restore database test
filegroup = 'Primary'
from DISK = 'y:\tmp\test_FG_Primary.bak'
with PARTIAL, norecovery
-- restore full file read write
-- SKIP IT IF YOU WISH
restore database test
filegroup = 'RW_FG'
from DISK = 'y:\tmp\test_FG_RW.bak'
WITH NORECOVERY
-- restore full file read only
-- SKIP IT IF YOU WISH
restore database test
filegroup = 'Ro_Fg'
from DISK = 'y:\tmp\test_FG_RO.bak'
WITH NORECOVERY
-- MUST RESTORE THE TLOG
restore DATABASE TEST FROM DISK = 'y:\tmp\tlOG.bak'
November 14, 2017 at 4:03 pm
Thanx a lot to both of you, Sue and Gother, I gonna try that code tomorrow and let you know if that works.
Sue, it's for storage reasons that I want to skip some filegroups (historicized tables in fact).
I could succeed the restore handling the backup (restoring all the database, then deleting the parts I don't want to keep, and then restoring the others databases playing like this), but if I must do this everytime I restore the staging environment, it will become painful...
I don't want to go deeply in the business part, searching which tables must be there to make the applications work, or which tables would be needed by the developers, because the needs of the developers change quickly. Adding some disk could be an option, I'll check tomorrow if we got some additionals slots 🙂
Johan Jezequel
** Sorry for my english, I promess you I do my best **
November 14, 2017 at 4:16 pm
Johan_1975 - Tuesday, November 14, 2017 4:03 PMThanx a lot to both of you, Sue and Gother, I gonna try that code tomorrow and let you know if that works.
Sue, it's for storage reasons that I want to skip some filegroups (historicized tables in fact).I could succeed the restore handling the backup (restoring all the database, then deleting the parts I don't want to keep, and then restoring the others databases playing like this), but if I must do this everytime I restore the staging environment, it will become painful...
I don't want to go deeply in the business part, searching which tables must be there to make the applications work, or which tables would be needed by the developers, because the needs of the developers change quickly. Adding some disk could be an option, I'll check tomorrow if we got some additionals slots 🙂
Not sure what edition you are on but for partial restore/partial database availability you need to be on enterprise edition.
Sue
November 14, 2017 at 4:22 pm
Sue_H - Tuesday, November 14, 2017 4:16 PMJohan_1975 - Tuesday, November 14, 2017 4:03 PMThanx a lot to both of you, Sue and Gother, I gonna try that code tomorrow and let you know if that works.
Sue, it's for storage reasons that I want to skip some filegroups (historicized tables in fact).I could succeed the restore handling the backup (restoring all the database, then deleting the parts I don't want to keep, and then restoring the others databases playing like this), but if I must do this everytime I restore the staging environment, it will become painful...
I don't want to go deeply in the business part, searching which tables must be there to make the applications work, or which tables would be needed by the developers, because the needs of the developers change quickly. Adding some disk could be an option, I'll check tomorrow if we got some additionals slots 🙂
Not sure what edition you are on but for partial restore/partial database availability you need to be on enterprise edition.
Sue
I'm on the Enterprise Edition, no problem 🙂
Johan Jezequel
** Sorry for my english, I promess you I do my best **
November 15, 2017 at 9:09 am
@Johan,
Are the filegroups you're talking about part of a "Paritioned Table"?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2017 at 9:28 am
Jeff Moden - Wednesday, November 15, 2017 9:09 AM@Johan,Are the filegroups you're talking about part of a "Paritioned Table"?
@jeff : No.
They were partitionned, but I unpartitionned the tables when I historized the table to save some space.
The technique suggered by Goher works fine, I have an error message when I try to open the table non restored, but it's fine.
However, it's a bit harsh to backup / restore with filegroups because I have a lot of partitions (and then a lot of filegroups, circa 2000. I don't want to script 2000 restorations of filegroups, and I didn't found a built-in script to do it).
Johan Jezequel
** Sorry for my english, I promess you I do my best **
November 15, 2017 at 12:06 pm
lucky you ..
set nocount on
use test
select 'backup database ' + cast(db_name() as varchar) + ' filegroup = ''' + name + ''' to disk = ''y:\tmp\' + name + '_backup.bak'' with compression, init' from sys.filegroups
select 'backup log [' + cast(db_name() as varchar) + '] to disk =''' + cast(db_name() as varchar) + '.trn'' with init'
select 'restore database ' + cast(db_name() as varchar) + ' filegroup = ''' + name + ''' from disk = ''y:\tmp\' + name + '_backup.bak'' '+
case name
when 'PRIMARY' THEN 'with partial, norecovery'
else 'with norecovery'
end
from sys.filegroups
select 'restore log [' + cast(db_name() as varchar) + '] from disk =''' + cast(db_name() as varchar) + '.trn'' with norecovery'
select 'restore database ' + cast(db_name() as varchar)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply