DOS Batch File to backup a SQL Database
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.ba
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. :-)
I can easily run a db backup in SQL Management Studio Express:
BACKUP DATABASE [LivingTreasures] TO DISK = N'C:\Data\SQL\Backup\lt.ba
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. :-)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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_scrip
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 ?
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_scrip
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 ?
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial