You are here:Home » sqlcmd » Perform database backup using SQLCMD utility in SQL Server

Perform database backup using SQLCMD utility in SQL Server

I just went through the SQLCMD command line utility to perform database backup in SQL server where it does not have SQL agent. Below is the command which I placed in a batch file and schedule with windows task scheduler.
    Sqlcmd -UUserName -PPassword -SServerName -dDatabaseName -q"DECLARE @backupFilePath varchar(100); SET @backupFilePath = 'D:\BackupPath\DBName\'+'DBName_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') + '.bak'; Backup database DBName to disk = @backupFilePath "
    
Above command perform a full database backup, but it does not quit from utility after performing it. So I have changed with slight change and placed -Q in place of -q for cmdline query. Otherwise next schedule will be skipped as of current schedule never come out from sqlcmd utility and keep running
    Sqlcmd -UUserName -PPassword -SServerName -dDatabaseName -Q"DECLARE @backupFilePath varchar(100); SET @backupFilePath = 'D:\BackupPath\DBName\'+'DBName_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') + '.bak'; Backup database DBName to disk = @backupFilePath "
    
This will perform backup and quit from the sqlcmd utility.