Back up Query help

Question

Post reply

Back up Query help

pandeysharp

Newbie

Points: 9
More actions
December 11, 2007 at 1:30 am

#154011

I need to take back up of very large DB. Please help me with the Query.

Viewing 15 posts - 1 through 15 (of 20 total)

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

Mark Hemelrijk Old Hand Points: 394 More actions · Answer 1

BACKUP DATABASE [name] TO DISK=N'[path+filename]' WITH NOINIT

and make sure the extension of the back-up file is .BAK

Clive Strong SSChampion Points: 11222 More actions · Answer 2

Additionally, I would suggest that you take a little time to read BOL about backups and restores.

I appreciate this is a simple query and process for me, but if you are in a position to be responsible for backups and restores, you should try to understand the technology behind the queries and time permitting, play about...test backups and restores so you feel comfortable with the process. Trust me, when the day comes when you need a restore, you'll be happy you know the process!!!

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 3

After taking up the backup, I verify my backups using :

RESTORE VERIFYONLY FROM DISK ='C:\YourBackupfile.bak'

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

Adding to the previous replies if you really backing up a very large database, you might want to backup to more than one file. Depending on your disk subsystem this can shorten your backup (and restore) time considerably.

[font="Verdana"]Markus Bohse[/font]

pandeysharp Newbie Points: 9 More actions · Answer 5

Thanks for the reply. I tried the query that you suggested. It worked fine when I executed it on the server machine. I tried to fire the same query from VC++ application that uses DSN configured to the Server IP and the application is on client machine, but the query failed.The data base is huge.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

ODBC connections time out after 30000 ms. You have to alter your DSN connection driver to accomodate the long running query.

This link has more information:

http://www.dbforums.com/showthread.php?t=436735

My blog: http://jahaines.blogspot.com

Clive Strong SSChampion Points: 11222 More actions · Answer 7

Alternatively, create a scheduled job with the T-SQL code to backup and execute the scheduled job instead. This way you won't need to play around with timeouts. You just fire off the job within SQL Server and you can disconnect.

Clive Strong SSChampion Points: 11222 More actions · Answer 8

Sorry, for clarity, use sp_start_job from your application to start the scheduled job off.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

Great point Clive :cool:.

The only potential problem I see is the application cannot inform the user that the backup has completed or failed. This can cause a problem if the user believes that the backup is taking place, while in fact it has failed.

My blog: http://jahaines.blogspot.com

Clive Strong SSChampion Points: 11222 More actions · Answer 10

I did think of that too. You could always have some kind of e-mail process at the end to give a notification of completion/failure.

Either that or just poll SQL Server for a status update on the backup.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 11

I like your method more than altering timeout values. Altering timeout values can lead to some serious problems, especially if you do not set it back to the default. You could have another piece of the application stuck because it will not timeout properly.

I would personally go with the email notification as it adds less overhead to the app and gets the message accross.

My blog: http://jahaines.blogspot.com

Clive Strong SSChampion Points: 11222 More actions · Answer 12

I agree. Also, when you are setting timeout values for a backup to complete, you'll be chasing your tail. You could come across network or disk issues which could slow the process down and you'd get the same timeout. Not to mention a backup will grow in size and time of backup over time!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Adam Haines (12/12/2007)
Great point Clive :cool:.
The only potential problem I see is the application cannot inform the user that the backup has completed or failed. This can cause a problem if the user believes that the backup is taking place, while in fact it has failed.

Perhaps not - but that's what the job operator notification is for. Define the job to send completion info via e-mail to the user (defined as the operator or one of the operators).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 14

And I might add - even though you said the database is huge, I would make a strong effort to restore it to a test box just to be sure.