Here is a small SQL Server backup script sample from some of my DBA work. These are usually run on-demand to create custom on-off backups before and after special software upgrades.
The first part is a simple batch file that uses the “sqlcmd” command to run your backup. This is intended to be run by hand to view any error output with the “pause” command. If you plan to schedule a task, you would need absolute path names and a way to automatically clear out old backup files to minimize space used by backup files.
REM ==== cmd batch file name "run_backup.bat" ==== sqlcmd -i backup_db.sql -o log_backup_db.txt pause REM ==== remove "pause" if you're scheduling this job ====
The second part is the actual T-SQL commands to backup the database. The batch file above depends on this SQL file to do the actual backup. Note that the date and time are used as a dynamic prefix for the file name along with the database name to keep the backup file-names unique. A “copy only” backup is used to prevent issues with automated full/differential sets.
-- SQL file name "backup_db.sql" -- DECLARE @myDate VARCHAR(100) DECLARE @dbName VARCHAR(50) DECLARE @fileName VARCHAR(MAX) DECLARE @folder VARCHAR(MAX) -- Set a location to store your database backup files SELECT @folder = 'C:/SQL_Backup/' -- Change Date-Time to filename-compatible format SELECT @myDate = REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),' ','_'),':',''); PRINT 'Start at ' + CONVERT(VARCHAR,GETDATE(),120) PRINT '' -- blank line SELECT @dbName = 'Your_Database_Name' SELECT @fileName = @folder + @myDate + '_' + @dbName + '_full.bak' PRINT 'Backup Database "' + @dbName + '" to "' + @fileName + '"' BACKUP DATABASE @dbName TO DISK = @fileName WITH COPY_ONLY, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 ; PRINT '' -- blank line PRINT 'Finish at ' + CONVERT(VARCHAR,GETDATE(),120)