"Mike Labosh" <mlabosh@hotmail.com> wrote in message
news:u9OclgfyEHA.1204@TK2MSFTNGP10.phx.gbl...
>I have completed a gigantor import batch (migration project) of several
>dozen Access databases to SQL Server to the tune of about 12 gigs via 83
>DTS Packages. I want to store a backup point that's *real tight* since I
>am doing all my dev work on a little mousy laptop [friggen corporate
>america won't spend money on a dev-server]. Here's what I want to do
>[pcode] with two cursors:
>
> USE This_DB
> GO
>
> For Each Table In This_DB -- Cursor 1
>
> For Each Index on This_Table -- Cursor 2
> DBCC INDEXDEFRAG (This_DB, This_Table, This_Index)
> Next
>
> Next
>
> DBCC SHRINKDATABASE(This_DB, 0)
>
> ...And then do a backup so that the backup is as tight and small as
> possible.
>
> What I have so far is a script that tries to use a cursor over
> INFORMATION_SCHEMA.TABLES to loop over the table names and for each table,
> get another cursor on sp_HelpIndex and run EXEC DBCC INDEXDEFRAG on each
> one, but I can't seem to nail down the phrasing. It's gotta be some goofy
> thing like the number of quotes or whether you can SELECT xxx FROM (EXEC
> sp_HelpInex) x or something like that.
>
> I wish I had a DDL of my script, but after about 45 hours straight of
> bashing my head on this project, in a fit of exhaused fury, I deleted the
> nonfunctional script. I nned a nap. oo and some help would be cool too!
>
> I can't even fathom how these folks *ever* got as far as they did with
> Access / Excel / FoxPro with their stupid 3.7 million "Contacts" records.
> I have two words: "Quantum Ridiculosity"
>
> --
> Peace & happy computing,
>
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
>

> You might want to use DBCC DBREINDEX instead as it will do all the indexes
> for each table at one time and get the cleanest results.

> But then if you shrink the database you will fragment it again.

> A backup is only as large as the actual data (plus a little bit extra) and
> not the size of the database files.