detach-copy-attach vs Copy Database Wizard

Instead of running the Copy Database Wizard, if I
1 detached the source database
2 copied the files from the source to the destination server
3 attached the files to the destination server
does it accomplish the same thing? The source will be SQL 7 and the
destination will be SQL 2000, so a database upgrade is involved.
I want to be able to get a copy of the detached .mdf and .ldf files copied
to the destination server that I can use to test the upgrade from SQL 7 to
2000
numerous times if needed. I don't want to use the wizard repeatedly, since
it
requires that the source db be in single user mode or have no users
connected to it.
Thanks,
johnYour method works. You might want to look into BACKUP and RESTORE as =another method to "move" databases. One benefit with this method: you =can use the Transact-SQL command 'BACKUP' to backup your database =without having to take it offline (as you do with detach_db). Another =method is that you can simply come along with your favorite backup =utility and simply backup a file (instead of trying to backup an open =database).
-- Keith
"john" <jgorman@.humanitees.com> wrote in message =news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> > 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> > does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> > I want to be able to get a copy of the detached .mdf and .ldf files =copied
> to the destination server that I can use to test the upgrade from SQL =7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly, =since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> > Thanks,
> john
> >|||Will a restore of a SQL 7 database to a SQL 2000 database result in an
upgrade of that database to SQL 2000?
john
Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
Your method works. You might want to look into BACKUP and RESTORE as
another method to "move" databases. One benefit with this method: you can
use the Transact-SQL command 'BACKUP' to backup your database without having
to take it offline (as you do with detach_db). Another method is that you
can simply come along with your favorite backup utility and simply backup a
file (instead of trying to backup an open database).
--
Keith
"john" <jgorman@.humanitees.com> wrote in message
news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> I want to be able to get a copy of the detached .mdf and .ldf files copied
> to the destination server that I can use to test the upgrade from SQL 7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly,
since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> Thanks,
> john
>|||Yes.
--
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
--
"john" <jgorman@.humanitees.com> wrote in message
news:%23tOae2ZmDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Will a restore of a SQL 7 database to a SQL 2000 database result in an
> upgrade of that database to SQL 2000?
> john
>
> Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
> news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
> Your method works. You might want to look into BACKUP and RESTORE as
> another method to "move" databases. One benefit with this method: you
can
> use the Transact-SQL command 'BACKUP' to backup your database without
having
> to take it offline (as you do with detach_db). Another method is that
you
> can simply come along with your favorite backup utility and simply
backup a
> file (instead of trying to backup an open database).
> --
> Keith
>
> "john" <jgorman@.humanitees.com> wrote in message
> news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > Instead of running the Copy Database Wizard, if I
> >
> > 1 detached the source database
> > 2 copied the files from the source to the destination server
> > 3 attached the files to the destination server
> >
> > does it accomplish the same thing? The source will be SQL 7 and the
> > destination will be SQL 2000, so a database upgrade is involved.
> >
> > I want to be able to get a copy of the detached .mdf and .ldf files
copied
> > to the destination server that I can use to test the upgrade from
SQL 7 to
> > 2000
> > numerous times if needed. I don't want to use the wizard
repeatedly,
> since
> > it
> > requires that the source db be in single user mode or have no users
> > connected to it.
> >
> > Thanks,
> > john
> >
> >
>

Designing Aggregations

I have a fairly large partition (100M rows, 30 measures) and am attempting to create aggregations through the Aggregation Design Wizard. Regardless of the option I select, either percent or file size, the wizard always completes after designing only about 50 aggregtions for 0% and only 200kb. I cannot believe that to be right. On a much smaller partition I created about 600 aggregations to 30% for 2GB.

I've double checked to ensure I have all of my dimension attribute relationships defined.

Any ideas why I cannot get the large partition to build the aggregations or why it completes after building about 50? I have plenty of disk space and and cube processing time is not an issue, so I would like to create up to 25-30GB of aggregations.

Thanks

Have you established your attribute relationships?

WIthout them the system does not know that natural hierarchies may be available and by default it only creates aggregations along natural hierarchies.

_-_-_ Dave

|||

Thanks Dave, but I have established the attribute relationships. What confuses me is that the wizard shows that it will create about 250 aggregations, 0%, 125kb. I use the option to manually stop the aggregation design (but never do. I let if finish on its own) and it always comes out roughly the same.

When I process the partition I can see that the aggregations are being created. When I look in the file system I see a bunch of .tmp files being created (e.g. AggMerge_5800_54_8s64d_28.tmp), all of them for about 1.5GB. I eventually run out of space on the drive and the processing fails.

All together these .tmp files take about 150GB. The source database is only about 200GB.

Any thoughts? Ideas? The aggregation design wizard tells me the aggregations will take up about 125kb, but the .tmp files tell me something different.