May 29, 2009 at 6:23 am
Looking for some help. I've got a sitiuation were our developers want a copy of our production database to code hotfixes against. Now our database is almost a TB in size and it will be costly to add the space required to accomplish this request. We have 3 tables, history\Archive, that take up some 500g of space.
Is it possible to move these tables to their own filegroup and restore ONLY the primary filegroup to our development server? This will cut the required space in half and would help out.
I'm looking at BOL and some blogs that don't really say if this is possible. I know I can do it, but it looks like the database would be in nonrecovery mode? Doesn't make sense to me.
May 29, 2009 at 5:01 pm
sorry it is not possible to do partial restores in that way from one database to another.
You need to look into doing a logical copy of the database to the new database, omitting those 3 tables. As you still have 500GB of data to move BCP would be by far the fastest but you need the space to store the unloaded tables, so you will probably have to go the 'select into' or SSIS route. Referential Integrity could be a problem. Probably be cheaper to buy the disk then the cost of manpower doing the logical copy.
Some third party tools I believe offer the possibility of restoring at a table level from backups. They can certainly compress the backups which maybe will buy you enough space?
---------------------------------------------------------------------
May 31, 2009 at 11:29 pm
Hi,
As you mentioned that the size of DB is 1 TB and 3 od the tables
history
Archive
(3rd one you have not metioned)
which aer of 500 GB and want only them to restore.
Now the thing is if these tables are part of PRIMARY File Group then you can able to restore it partially.
eg:
CREATE DATABASE [TRACKARCHIVE] ON PRIMARY
( NAME = N'TRACKARCHIVE', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TRACKARCHIVE.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [SECONDARY]
( NAME = N'TRACKARCHIVE_SECOND', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TRACKARCHIVE_SECOND.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TRACKARCHIVE_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TRACKARCHIVE_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
USE [TRACKARCHIVE]
GO
/****** Object: Table [dbo].[orderhistory1] Script Date: 04/02/2009 10:43:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[orderhistory1](
[nid] [int] NULL,
[desc1] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[orderhistory2] Script Date: 04/02/2009 10:43:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[orderhistory2](
[nid] [int] NULL,
[desc1] [varchar](20) NULL
) ON [SECONDARY]
GO
SET ANSI_PADDING OFF
GO
insert into orderhistory1 VALUES (1,'HELLO')
insert into orderhistory1 VALUES (2,'TATA')
insert into orderhistory2 VALUES (1,'OK')
insert into orderhistory2 VALUES (2,'bye')
BACKUP DATABASE trackarchive FILEGROUP ='PRIMARY' TO DISK = 'c:\primary.bkp' WITH INIT , NOUNLOAD , NAME = 'TRACKARCHIVE', NOSKIP , STATS = 10,NOFORMAT
BACKUP DATABASE trackarchive FILEGROUP ='SECONDARY' TO DISK = 'c:\orderhistory1.bkp' WITH INIT , NOUNLOAD , NAME = 'p1', NOSKIP , STATS = 10,NOFORMAT
BACKUP log trackarchive to disk = 'c:\orderhistory1log3.bak' with INIT
RESTORE DATABASE V8ARCHIVE FROM disk='c:\primary.bkp' WITH RECOVERY,REPLACE,PARTIAL, MOVE 'trackarchive' TO 'c:\primary.mdf' ,MOVE 'TRACKARCHIVE_log' TO 'c:\TRACKARCHIVE_log.ldf'
--Now after LAST RESTORE your database will be up but it will only be
having information about table ORDERHISTORY1 as we have not
restored 2nd file group and we will be good with 1 table only.
--Now in case other tables (which are required) ORDERHISTORY2 in our
case is there in 2nd file group so that will not be accessible.
--More over if we run above restore command with little difference.
RESTORE DATABASE V8ARCHIVE FROM disk='c:\primary.bkp' WITH NORECOVERY,REPLACE,PARTIAL, MOVE 'trackarchive' TO 'c:\primary.mdf' ,MOVE 'TRACKARCHIVE_log' TO 'c:\TRACKARCHIVE_log.ldf'
--After execution of above command database will be in RESTORING
MODE as its not fully recovered.
I hope this should answer your question.
Regards
GURSETHI
June 1, 2009 at 5:25 am
Gursethi, nice one, I did not realise that would work onto a new database.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply