Sql Server 2000 backup

Question

Post reply

Sql Server 2000 backup

assh1122

SSC-Addicted

Points: 449
More actions
April 24, 2010 at 8:21 am

#219608

Hi All,
I am trying to take backup on a host machine shared folder. Sql Server 2000 is running on a virtual PC. The size of the database is 121 G.B Virtual PC does not have enough space to do backup.When I tried to do backup on a host machine on shared folder, but got the following error "The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere."
My question is , can I take database backup by detaching and copying on to host machine and again attach the database back to original location? If so then could anyone please send me T-SQL script or any script to do this task? I want to schedule this job since I need to take backup every day. Your help is greatly appreciated.
Thank you,
Sam

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

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

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

yes u can do that

read more on

sp_deach_db

and

sp_attach_db

tosscrosby SSCoach Points: 18045 More actions · Answer 2

You do realize that doing a dettach will effectively end all connections to the database until the copy is complete, correct? Being a virtual PC, can you allocate more disk space to it, perform the backup locally and then copy the backup file to a network share?

-- You can't be late until you show up.

assh1122 SSC-Addicted Points: 449 More actions · Answer 3

Hi All,

Thank you all for your suggestions on database backup. I can successfully do the database backup on weekly basis. I scheduled the batch file in the windows task scheduler. Now I need to notify the manager once the backup is done through email. What is the best approach to do this? The email notification task should be executed once the backup is over. Any help is greatly appreciated.

Thank you,

Assh

tosscrosby SSCoach Points: 18045 More actions · Answer 4

Is there a reason you are running it in Windows Task Scheduler as opposed to within SQL? SQL can email for you, on completion or error, with some set-up. Personally, I only email on failures so that you don't become some de-sensitized to the emails, you miss the one that says failure.

-- You can't be late until you show up.

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 5

I concur with the question about Windows Scheduler above. The notification, error handling, etc. is much more robust in SQL Server Agent.