can i calculate a database backup size?

Hi,
I've a database with multiple millions of rows in a small set of tables.
Most of this data is historical, so I want to split the data in multiple
databases, make backups of about 600 MB each and burn this to cd roms. I
want to split the data based on dates stored inside the tables. Is there a
way to calculate the amount of bytes inside a database backup file that will
be produced ba a diven select statement when it uses all rows of a table?
SvenBackups aren't caused by select statements. They backup all of the physical
data in the database unless you do file or filegroup backups. So if you
want to get an idea of the size the backup will be you only have to look at
how much actual data (not including free space) there is in the db and it
will be approximately that. You can compress backups quite easily with
WINZIP or similar utilities and fit much more on a cd.
--
Andrew J. Kelly
SQL Server MVP
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:eJ3BY7XZDHA.1832@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a database with multiple millions of rows in a small set of tables.
> Most of this data is historical, so I want to split the data in multiple
> databases, make backups of about 600 MB each and burn this to cd roms. I
> want to split the data based on dates stored inside the tables. Is there a
> way to calculate the amount of bytes inside a database backup file that
will
> be produced ba a diven select statement when it uses all rows of a table?
> Sven
>|||Good point Dan. Be sure to run UPDATE USAGE or use the Updateusage option
when running it.
--
Andrew J. Kelly
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23Cs0mbZZDHA.2572@.TK2MSFTNGP12.phx.gbl...
> To add to Andrew's response, the reserved space reported by sp_spaceused
> can provide a rough estimate of database backup space requirements.
> --
> 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
> --
> "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> news:eJ3BY7XZDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I've a database with multiple millions of rows in a small set of
> tables.
> > Most of this data is historical, so I want to split the data in
> multiple
> > databases, make backups of about 600 MB each and burn this to cd roms.
> I
> > want to split the data based on dates stored inside the tables. Is
> there a
> > way to calculate the amount of bytes inside a database backup file
> that will
> > be produced ba a diven select statement when it uses all rows of a
> table?
> >
> > Sven
> >
> >
>|||Sven,
That's a really awkward way of going about it. Why not just BCP out the
data to flat files (I suggest native mode) and load them onto the CD(s).
You can use normal select statements to break them up into what ever you
want. It will be much cleaner and easier than attempting to do this with
backups. As for the size, it would be difficult to tell exactly unless all
your columns are of the fixed size (no varchars, text etc.). I think with a
few test selects you should be able to get a pretty good idea though.
--
Andrew J. Kelly
SQL Server MVP
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:u9WCdCkZDHA.2520@.TK2MSFTNGP09.phx.gbl...
> I plan to split up database. 99% of the size of my database is caused by
> "Table1". What I need to know is: How many rows do I have to delete to be
> able to produce a backup os ~600MByte. I need functional database backups
to
> be able to restore data from a specific date.
> The idea was to have a second database with all the data but Table1, then
> - do a move of x rows from the original database to the new database
> - then make a backup
> - truncate Table1 of the backed up database
> - repeat these steps until all data is backed up.
> Now I need to know what type of relationship is there between space used
in
> a database and space used in a backup file. I don't want to split one huge
> database backup file to burn it on cdroms, because then I need to restore
> the complete database from 100 CDs just to lookup one row - that's not
what
> I had in mind ;).
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eQAY7jYZDHA.3444@.tk2msftngp13.phx.gbl...
> > Backups aren't caused by select statements. They backup all of the
> physical
> > data in the database unless you do file or filegroup backups. So if you
> > want to get an idea of the size the backup will be you only have to look
> at
> > how much actual data (not including free space) there is in the db and
it
> > will be approximately that. You can compress backups quite easily with
> > WINZIP or similar utilities and fit much more on a cd.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> > news:eJ3BY7XZDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > I've a database with multiple millions of rows in a small set of
tables.
> > > Most of this data is historical, so I want to split the data in
multiple
> > > databases, make backups of about 600 MB each and burn this to cd roms.
I
> > > want to split the data based on dates stored inside the tables. Is
there
> a
> > > way to calculate the amount of bytes inside a database backup file
that
> > will
> > > be produced ba a diven select statement when it uses all rows of a
> table?
> > >
> > > Sven
> > >
> > >
> >
> >
>