Need some help before taking the backup

Question

Need some help before taking the backup

Bagath

SSCrazy

Points: 2409
More actions
July 7, 2007 at 4:00 am

#117251

Hello all....
Can any one give me some suggesions that what are the things we have to do before taking the database backup like removing the unused space...etc?
I have to take around 60gb database backup...
This will be very helpful to me....

Thanks,
Bagath

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

Firstly, use a good backup tool such as SQL Litespeed; secondly, check the integrity of the database using DBCC CHECKDB before doing the backup. Please be aware that the check process is *very* resource and log intensive, so perform these actions during 'quiet times' and make sure that you have sufficient hard disk space for your log file. Lastly, perform regular restores of your backups to a secondary server. I test my backups daily, due to the fact that I have a VLDB, and thus executing DBCC CHECKDB is not an option.

HTH

Paul

Bagath SSCrazy Points: 2409 More actions · Answer 2

Thanks for your valuable suggestions.....

I will try to implement....

Thanks once again...:-)

Bagath.......

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

You can use the maintenance plan that gives yuo lot of option and also helps to back up databases.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Vivien Xing SSChampion Points: 12297 More actions · Answer 4

Something need to think about more than backup operation itself:

Where (media) do you keep the backup? How many days backup do you need to keep on the local disk? Do you have enough space for the backup? How big is the database? Capacity planning for both database and backups? How many years/months for backup retention? What is your tolerant loss of data? Do you need differential backup and/or transaction log backup? What is the window for backup?

Bagath SSCrazy Points: 2409 More actions · Answer 5

Thanks to one and all for your valuable suggestions......

Bagath

Michael Meierruth SSChampion Points: 10051 More actions · Answer 6

This is a pretty standard sequence I use when, for example, I need to copy a database from one instance to another. However, make sure you know the implications of every step.

truncate table table-name

...for tables that you know are big and are not needed

delete from table table-name where...

...for data in tables that you know is not needed

backup log DBNAME with truncate_only

dbcc shrinkfile(LOGICALNAME,1)

...repeat for all MDF and LDF files.

backup database DBNAME

to disk='c:\wherever\DBNAME.BAK'

with stats=1