How to backup my DB with sqlcmd and a batch file

Can someone show me how I can back up my database using sqlcmd and a batch file? Also, what file path should I use?

What I want to do is have my home server run the batch file each night which will back up my database, then ftp the backup to my home server.

Thanks,
Jay

 

Thanks!

I didn't know I could back it up using your sql tools. That should work fine.

Thanks!
Jay

 

Error running SQL backup--any ideas?

So, I set this up and when I run the app, I get the following error:

Server Error in '/SQLBackup' Application.

The process cannot access the file 'E:\web\voterbraind\htdocs\backupLog.txt' because it is being used by another process.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IO.IOException: The process cannot access the file 'E:\web\voterbraind\htdocs\backupLog.txt' because it is being used by another process.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[IOException: The process cannot access the file 'E:\web\voterbraind\htdocs\backupLog.txt' because it is being used by another process.]
System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) +9718246
System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath) +1142
System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) +78
System.IO.FileStream..ctor(String path, FileMode mode) +71
JJS.CrystalCMS.DBBackup.Global.Application_BeginRequest(Object sender, EventArgs e) in C:\Users\Jay\Desktop\JJS.CrystalCMS.DBBackup\JJS.CrystalCMS.DBBackup\Global.asax.cs:22
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +148
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

 

A few things to note:

1. It looks like you're running on old version of this code. The forum thread includes details about a later version that includes backup zip functionality.
2. The code in global.asax sets up a log file for trace purposes. It was envisaged that the single web page in this application would be hit very intermittently - probably once a day since its sole purpose in life is to perform a database backup. The exception you've posted indicates the log file was locked when a new request was made and this will be because a second request was made whilst a first was still processing.
3. I've attached a copy of my sanitized log from this application running in my account on the DASP server. It's hit once a day and has been performing automated db backups since mid 2009.
4. Feel free to hack about with the code - do what you like with it. It was a proof of concept to prove that a database backup could be scheduled and performed with the DASP scheduler.

 

A different problem

Okay. I grabbed the new code, tweaked the web.config file, and now the code can't find my database. I double-checked that the database name is correct. Any idea why that would be?

An error occurred : System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> DiscountASP.net.UserErrorException: Database 'SQL2008R2_813081_vbd' could not be found.

 

It looks like your database is hosted on a SQL Server 2008 R2 instance. There are different web service API methods for backing up each of the SQL Server instance types on the DASP SQL Servers. The code as-is, only supports the Sql2008CreateBackup method which won't work with an R2 instance - you need to call Sql2008R2CreateBackup.

To get it working you're going to have to update the web reference and then change the web service call. When you're in there you could also make a change to the logging / tracing code if this isn't working for you.

 

Rockin'...

I'm making headway. After tweaking the Web Service to call the R2 backup routine, it's now complaining that I don't have access to "C:\." Why is it trying to save there? I would've thought MapPath would've mapped to the physical path where my website is stored (e:\web\voterbraind\htdocs). My understanding of how MapPath works is limited, so I'm not sure what's going on.

 

Any ideas why this is trying to save to the root directory on the hard drive?

 

Where are you running this? On local or on the DASP server? Post your exception stack trace so we can see the offending line of code and exception being thrown.