Instead of writing the query in the command line, I would prefer to put it into a stored procedure or in a .sql file and pass it into sqlcmd

In a more modern way, it is possible to use a script inside PowerShell, with 2 problems : - the requirement to know SMO ( SQL Server Management Objects ) - the obligation to check whether the classes have not been moved from a namespace to another one ( it is what has happened for backup/restore ) An advantage, you can script the generated statements ( to be sure that you have done no errors ) PowerShell seems to be used mainly by System Administrators since the release of Windows Server 2003.Be careful the V2 version is really pleasure in comparison of the 1.0 version. But this article was extremely interesting, it permits me to review some T-SQL keywords i am using scarcely ( the q parameter or Replace )

I find that it's better to split this into two parts. Firstly a batch file that is called by the scheduler and secondly a .sql file that is processed by the batch file.

The reason for this is that it gives you a lot more flexibility, it's easier to read, and you can make the .sql file back up multiple databases easily without running up against the command line character limit.

I also recommend that you put 'with init' into the SQL command. Without this, the backups append to the file and it can quickly grow very large.

I create a SQL Login with only the db_backupoperator role mapped to each database to be backed up.

Here's an example:

Sqlbackup.bat file contents:

@echo off

Rem Backup batch file created by Phil Haddock March 3rd 2008

Rem Change the variables below to suit your database

Rem Also need to change the variables in the sqlbackup.sql file

Rem Enter SQL Server name on next line nb. Server name is CASE SENSITIVE

set server=server\InstanceName

Rem Enter location of sqlbackup.sql script file on next line

set BackScript=C:\temp\sqlbackup.sql

Rem Enter Username and password with rights on the sql server.

Rem If someone sees this batch file they will have a sql login and password so recommend creating a limited login

set UserName=TESTBackupUser

set PWD=amt

echo Server = "%server%"

echo BackScript = "%backscript%"

echo Username = "%username%"

echo PWD = "%pwd%"

@echo on

sqlcmd -S %server% -U %UserName% -P %PWD% -i %BackScript%

sqlbackup.sql contents:

declare @DBName as varchar(80) declare @BackupPath as varchar(80)

-- Enter the database name to be backed up and the location to store the backup files

-- Repeat the next three lines for each database if there is more than one to be backed up.

set @DBName = 'TestDatabase'

set @BackupPath = 'c:\temp\SQLBackup\TestBackup.bak'

backup database @dbname to disk = @backuppath with init