How to get estimated time to backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to get estimated time to backup

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-06 : 11:33:11

Hi Guys

how to calculate estimated time to take backup on dbsize in sql server

I have some list on particular of all dbsizes I nedd to submit estimated time to backup

for example
(Space in MB)

DBName DbSpace DbFreeSpace
PA_FRM 134.0 509.59765625
PA_GLASGOW 294.0 207.37109375
PA_GLOBALADV 325.0 1539.0126953125
PA_GULF 66.0 1071.2646484375
PA_HOLTE 814.0 1608.333984375
PA_KEYNES_LEV 1322.0 1313.44140625
PA_KEYNES_MAST 1266.0 936.9677734375
PA_KEYNESQS_LEV 1061.0 2039.85546875
PA_KEYNESQS_MAST 918.0 1420.0087890625
PA_KOROKAN 258.0 237.90625

Help required

Is there any other way to find all dbsize in one result set
with estimated time calculation

Yogesh V. Desai. | SQLDBA|

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 11:41:20

I ndon't think you can estimtae the time - other than timing a backup from a known database size and using that is a benchmark for MB/minute.

Full Backup is only of used pages (not the size of the LDF file), will be influenced by how much data is in the LDF file, and activity on the server I expect.

Differential backup is only of changed pages, and I reckon that would be pretty hard to predict.

TLog backup is of the used part of the LDF file, and dependent on a number of things, and even hard to predict.

And that's assuming that none of those backups get blocked for any reason ...

This may help as a basis of calculating database sizes:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762&SearchTerms=size

Kristen

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-06 : 12:08:13

that's really nice link thank you very much for sharing the information,

Could you tell me how to get all dbsize, i am trying to build query for this but could not able

Yogesh V. Desai. | SQLDBA|

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 12:21:59

"Could you tell me how to get all dbsize"

You'll need to get the database names from the master.dbo.sysdatabase table, then iterate those getting the size of each database in turn (the code for which is in the link above)

There may be some code in SQL Team, but I didn't find it easily with a quick search

Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 12:25:44

OK, I tried slightly better keywords!

This may help, although it looks a little inconclusive, but you may be able to adapt it:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87867#327297

Kristen

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-07 : 10:46:59

thanks dear friend,I tried it but could not get the expected results will try differnet one.

Yogesh V. Desai. | SQLDBA|

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-08 : 00:04:10

If you have db backup job, just check job duration in msdb..sysjobhistory table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources