shanj
asked on
SQL 2008
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
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
Can you elaborate a bit more?
ASKER
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)..
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)..
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;
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;
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.
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.
commands to backup and restore Filegroups:
BACKUP DATABASE DBName
FILEGROUP = 'FGName '
TO DISK = 'C:\MySQLServer\Backups\FG Backup.bak '
RESTORE DATABASE DBName FILEGROUP = 'FGName ' FROM DISK = 'E:\FGBackup.bak'
BACKUP DATABASE DBName
FILEGROUP = 'FGName '
TO DISK = 'C:\MySQLServer\Backups\FG
RESTORE DATABASE DBName FILEGROUP = 'FGName ' FROM DISK = 'E:\FGBackup.bak'
ASKER
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)
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
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks it worked 100%, with a few adjusments.
Bye
Bye
glad! Welcome to EE,shanj! Hope you enjoy and benefit here.
to close the question, you can click 'accept as solution'.
ASKER
v.v.good
(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.