I have a production database consisting of 3 filegroups,
file group1 contains system data
file group2 contains user data
file group3 contains log data
I would like to restore this database onto a test server, the problem is
file group2 is larger than the largest disk on the test server, is there a
way on the restore to split file group2 on to 2 different disk or any way to
do this without restructuring the production database?
Thanks
Jim WileThis is a multi-part message in MIME format.
--=_NextPart_000_06A5_01C35D8A.DE0E6060
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I would add a file to group2 then shrink the existing file in group 2 to =half of its current size. This will push data from the existing file to =the new file. Then, when you restore the database, each file can go to =a separate drive.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"jim wile" <jimwile@.mopac.com> wrote in message =news:#FPXataXDHA.1204@.TK2MSFTNGP12.phx.gbl...
Hello
I have a production database consisting of 3 filegroups,
file group1 contains system data
file group2 contains user data
file group3 contains log data
I would like to restore this database onto a test server, the problem is
file group2 is larger than the largest disk on the test server, is there =a
way on the restore to split file group2 on to 2 different disk or any =way to
do this without restructuring the production database?
Thanks
Jim Wile
--=_NextPart_000_06A5_01C35D8A.DE0E6060
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I would add a file to group2 then =shrink the existing file in group 2 to half of its current size. This will =push data from the existing file to the new file. Then, when you restore the =database, each file can go to a separate drive.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"jim wile"
--=_NextPart_000_06A5_01C35D8A.DE0E6060--|||You can use the WITH MOVE option to specify alternate locations for
individual files. This is independent of filegroup membership. Your
text server will still need space to accommodate the largest single file
since files cannot be split during a restore.
If a file is too large for your test server disk configuration, you'll
need to change the prod database file allocations. To do this, add
addition files to the filegroup and shrink the large file using DBCC
SHRINKFILE. This will migrate data to the new files and release space
from the large file.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"jim wile" <jimwile@.mopac.com> wrote in message
news:%23FPXataXDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hello
> I have a production database consisting of 3 filegroups,
> file group1 contains system data
> file group2 contains user data
> file group3 contains log data
> I would like to restore this database onto a test server, the problem
is
> file group2 is larger than the largest disk on the test server, is
there a
> way on the restore to split file group2 on to 2 different disk or any
way to
> do this without restructuring the production database?
> Thanks
> Jim Wile
>
>|||Thanks for all your help!
JIm Wile
"jim wile" <jimwile@.mopac.com> wrote in message
news:#FPXataXDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hello
> I have a production database consisting of 3 filegroups,
> file group1 contains system data
> file group2 contains user data
> file group3 contains log data
> I would like to restore this database onto a test server, the problem is
> file group2 is larger than the largest disk on the test server, is there a
> way on the restore to split file group2 on to 2 different disk or any way
to
> do this without restructuring the production database?
> Thanks
> Jim Wile
>
>
No comments:
Post a Comment