How can I backup a large db?


I need to do a quick and dirty backup of a database 130 GB in size...

What I plan is:

use master
go
alter database BigDB
set single_user with rollback immediate

sp_detach_db 'BigDB'


copy the db files to another server....400 GB free (RAID-5)


then, on the original server:

use master
go
sp_attach_db 'BigDB','d:\Program Files\Microsoft SQL Server\MSSQL\Data\BigDB_Data.mdf'

restore database 'BigDB' with recovery


Will this work?
 I have most of the weekend to do it.
Is there minimal risk of loss to my data?
Is there a way to do this while the db is online?

Any other suggestions? (BTW, I dont have a tape drive large enough...)


LVL 1
TARJrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
is it all data or is some of the 130GB the transaction log?
LowfatspreadCommented:
have you considered transactional replication... your going to take the network traffic hit anyway?

how are you backing this up at the moment ?
Scott PletcherSenior DBACommented:
Excellent point:

What are the data and log sizes respectively?

Also, note that you should *NOT* issue the RESTORE command:

restore database 'BigDB' with recovery  -- *don't* issue this

The attach will put the db back the way it was before the detach.

Finally, note that you have to copy only the data file to another drive/server; if necessary, you could attach only the data file and get a new log file created from scratch.  This could save you significant time is the log file is large.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

TARJrAuthor Commented:

Recovery model: Simple

so we don't care about the trans log...right?

log file size shows: 1.78 GB  (oops...I don't have Auto Shrink checked..can I check it now?)


Scott PletcherSenior DBACommented:
Yep, don't have to worry about, but if it's that relatively small, might as well copy it also.

It will probably be worth the time to make the copy locally, then attach the original db (if you need to get running again asap), then zip the copy, then ship the zipped copy to another location.
TARJrAuthor Commented:

So, I can detach....copy only the .mdf file to another location...(delete the .ldf log)

then attach the original again (with recovery)...will it "look" just like it was when
I detached it?

Scott PletcherSenior DBACommented:
Yes, you can attach just the data file and a new log file will be built, and the db tables, etc., will be exactly the same.

The sp_attach_db is implicitly (automatically) "with recovery".  You will not need to issue an actual RESTORE command, only the attach.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arbertCommented:
Look at SQLLitespeed--cheap product.  Backed up 300gig in about 30minutes and compressed it to about 50gig....

http://www.dbassociatesit.com


Brett

TARJrAuthor Commented:
I looked at SQL LiteSpeed and the GUI doesn't support backup over the network...The next version may.

arbertCommented:
The proc does:

EXEC xp_backup_database @database = 'hwmf_db_idea'
                      ,@filename = '\\dhwidea\c$\backup.bak'
                      ,@init = 1

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.