August 31, 2012 at 7:34 am
Hello --
I have been able to successfully interactive run backups of our databases to a remote server via Studio as well as T-SQL commands. I want to automate the process, and I my plan is to use Task Scheduler on the Windows 7 host system.
My plan was to create a project that contains the T-SQL command syntax for each of the backups, and then create a batch file that will utilize sqlcmd to run the project.
I wanted to know if this is the correct way of doing this, and if so, what would the correct syntax be for sqlcmd to run the batch file.
If this is not the correct path, can someone point me into the right direction?
Thanks.
August 31, 2012 at 7:42 am
Yep, you plan is right in theory.
You would pass in a sql file to SQLCMD using the -i switch and then wrap the whole SQLCMD call into a bat file which you schedule using task scheduler.
August 31, 2012 at 11:15 am
Pretty much. From http://www.brianmadden.com/blogs/guestbloggers/archive/2007/05/07/how-to-automate-the-backup-of-a-sql-server-2005-express-data-store.aspx. Just chage the path to point to the proper location of sqlcmd that belongs to your local install. Do not skip the part about getting a copy of the backup to a second location, i.e. off the machine where the database is hosted.
Automating the Database Backup
You can automate the backup process by creating two Scheduled Tasks.
SQLCMD Scheduled Task
First, create a Scheduled Task to automate the .SQL script created above. Use the Scheduled Task Wizard and when asked to select a program browse to use browse to C:\Program Files\Microsoft SQL Server\90\Tools\binn\ SQLCMD.exe. Define the Schedule Task parameters accordingly and click “Finish”.
Go the properties of the newly created Scheduled Task and edit the Run command as such.
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S .\CITRIX_METAFRAME -i "C:\Program Files\Microsoft SQL Server\DatastoreBackup.sql"
Copy MF20.bak Scheduled Task
Next, create a simple batch file to copy the MF20.bak from the local server to a network share located on server being backed up regularly. For example, create a file named, “CopyMF20bak.cmd”, with the following contents.
copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MF20.bak" "\\<servername>\<sharename>\"
Lastly, create a simple Command Prompt Scheduled Task and configure accordingly. (Make sure to run this Scheduled Task after the SQLCMD Scheduled Task.) Go to the properties of the newly created Scheduled Task and edit the Run command to point to the location of CopyMF20bak.cmd (or your respective batch file name).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply