Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2008

Posted on 2011-02-23
12
Medium Priority
?
350 Views
Last Modified: 2012-06-22
Hei!
      I would like to restore a 250GB database (data Mart) to a development enviroment.
I know u cannot choose tables in Restore ?
 some tables are in a filegroup, and it is absolutely ok that i exclude them?
thanks
0
Comment
Question by:shanj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 6

Expert Comment

by:anushahanna
ID: 34961829
Yes, you cannot choose to restore only a few tables.
(alternative is you can use SSIS Import/Export to just move the tables that you need to the new server; but if you have many servers, it becomes a hassle).

You can do backup restore at
*Database Level
*File/Filegroup level

If your backup is at File Level, then you can restore at File level; if your backup is at database level, then you cannot restore at file level.
0
 
LVL 4

Expert Comment

by:rocky_lotus_newbie
ID: 34961846
Can you elaborate a bit more?
0
 

Author Comment

by:shanj
ID: 34962013
Hello

 i would like to restore a prod. DB to a development enviroment everyday (at midnight) via a script.
It shold include not all tables.

or

how do i retore a DB without taking with me filegroups. ( is it possible)..
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 6

Expert Comment

by:anushahanna
ID: 34964015
can you explain what do you mean by "without taking with me filegroups."

what you will need to do is create a file group and move all the tables you want to move to Dev every night, into that file group. then just backup and restore that filegroup (or filegroups) to dev. Hopefully this will be much smaller than 250 GB and will server your purpose.

The syntax to add a filegroup is

ALTER DATABASE DBName
ADD FILEGROUP FGName;

then you have to add a file to the filegroup.

ALTER DATABASE DBName
ADD FILE
(
NAME = DBName_FG_DATA,
FILENAME = 'C:\DBName\DBName_Data.ndf',
SIZE = 6MB,
MAXSIZE = 180MB,
FILEGROWTH = 1
)TO FILEGROUP FGName;

you can even make the above filegroup your default one:

ALTER DATABASE DBName
  MODIFY FILEGROUP FGName DEFAULT;
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34964044
once you create your filegroup, then just ship your preferred tables to them:

CREATE UNIQUE CLUSTERED INDEX 'ClustedIndexName' ON TABLENAME(ColName) WITH (DROP_EXISTING = on) ON OVAMS_FG

so the clustered index tables will just move totally from primary group to the filegroup.
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34964096
commands to backup and restore Filegroups:

BACKUP DATABASE DBName
   FILEGROUP = 'FGName '
   TO DISK = 'C:\MySQLServer\Backups\FGBackup.bak'


RESTORE DATABASE DBName FILEGROUP = 'FGName ' FROM DISK = 'E:\FGBackup.bak'
0
 

Author Comment

by:shanj
ID: 34967688
Hello Anushahanna!

your last comment  :"commands to backup and restore Filegroups"

I tried this but i got a heap of errors fks:
"Directory lookup for the file "D:\DATA\STAGE_T_ORDERS]_1.ndf" failed with the operating system error 2(The system cannot find the file specified.)"

1. The reason is quite obvious, i have taken a whole backup of the DB and then i aam trying to restore
the backup without the given filgroups?

my question
1. HOw do i take backup without the filegroups ( syntax) if possible. and if i take some filegroups?
2 and than i have to restore it)
0
 
LVL 6

Accepted Solution

by:
anushahanna earned 1000 total points
ID: 34970119
shanj, the following will work- use caution when you use REPLACE. if possible test in the test box before taking to Dev.

1) This will do a regular backup- it backs up everything
BACKUP DATABASE [STAGE_T_ORDERS] TO  DISK = N'C:\RegFULL.bak' WITH  DESCRIPTION = N'Reg Full', INIT,  NAME = N'STAGE_T_ORDERS-Full Database Backup', STATS = 10
2)This restores what you took in Step #1. When you use replace option, it will just go over what is already in Dev.
RESTORE DATABASE [STAGE_T_ORDERS] FROM  DISK = N'C:\RegFULL.bak' WITH  REPLACE,  STATS = 10
3)This backs up only one File Group, called "STAGE_T_ORDERS_FileGroup_OnlyCertainTables"
BACKUP DATABASE [STAGE_T_ORDERS] FILEGROUP = N'STAGE_T_ORDERS_FileGroup_OnlyCertainTables' TO  DISK = N'c:\FG_Only.bak' WITH INIT,  NAME = N'STAGE_T_ORDERS-Full Filegroup Backup', STATS = 10
4)Now use the backup from Step #3 to replace the filegroup table data only in the desination dev box.
RESTORE DATABASE [STAGE_T_ORDERS] FILE = N'STAGE_T_ORDERS_FileGroup_OnlyCertainTables_DATA' FROM  DISK = N'C:\FG_Only.bak' WITH REPLACE, STATS = 10
0
 

Author Comment

by:shanj
ID: 34977663
Thanks it worked 100%, with a few adjusments.

Bye
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34982382
glad! Welcome to EE,shanj! Hope you enjoy and benefit here.
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 34982457
to close the question, you can click 'accept as solution'.
0
 

Author Closing Comment

by:shanj
ID: 34998013
v.v.good
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question