DOS Batch File to backup a SQL Database

SOTA
SOTA used Ask the Experts™
on
Is there a way to run a DOS batch file that backups a single SQL database?

I can easily run a db backup in SQL Management Studio Express:

BACKUP DATABASE [LivingTreasures] TO  DISK = N'C:\Data\SQL\Backup\lt.bak' WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = N'LivingTreasures-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

But, now I want to have a batch file (backup.cmd) that can do this. Of course, it needs to connect to the db engine (SOTASERVER\SQL2008), supply password (sa/pw) and the db name (LivingTreasures).

Help!

High points for a speedy solution that will work. :-)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Database Administrator
Commented:
Found a post here that claims it works:

http://www.howtogeek.com/50299/batch-script-to-backup-all-your-sql-server-databases/

You can pass username and password by:

sqlcmd -U sa -P pwd

If you don't like the inline command as per the example you could use an input file:

sqlcmd -U sa -P pwd -i c:\backups\my_backup_script.sql -o c:\backups\output_of_commands.txt

Other parameters:

-S parameter passes server name
-d parameter passes DB name

Would highly recommend against putting the sa password in a script like that though for security purposes.

Have not tested this myself though.

As an aside, why do you want it in a script like this ?  If for automatic scheduling, why not use a maintenance plan / SQL Serve Agent ?

Author

Commented:
Thanks, I will try to get this to work.

The reason why I am not using a maintenance plan is because I need to export the dB to a web server to drive a website.

The person doing the export has no knowledge of SQL. Nor do I want her in there behind-the-scenes. Too dangerous!

So, if I can have her click a CMD file and have the export be done for her - that will help immensely.

Author

Commented:
Awesome, got it to work perfectly. I then used 7 ZIP to compress it!!!

Life is good again. :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial