How do I schedule a backup database program on windows servers? I have SQL Server 2008 Express on my azure server. It doesn't have any tasking or scheduling tools as it is an express version. Recommendation is to write a SQL server script and invoke it through sqlcmd.exe. Then have a windows scheduler schedule it at regular intervals. By the end of this research I will know how to schedule full backups and also differential backups using command line tools.

SQL Server 2008 task scheduler?

Search for: SQL Server 2008 task scheduler?

How do I schedule a backup database program on windows servers

Search for: How do I schedule a backup database program on windows servers

How to use Windows task scheduler for SQL server backup schedules?

Search for: How to use Windows task scheduler for SQL server backup schedules?

Read this

How to schedule and automate backups of SQL Server databases in SQL Server Express

Here is an SOF reference

Command line options for sqlcmd

Search for: Command line options for sqlcmd

Here is a microsoft reference link


Sqlcmd -E -S SqlServer1\SqlExpress
Sqlcmd -S SqlServer1\SqlExpress
sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword

connect to an instance of SQL Server Express, named SqlExpress, on a server named SqlServer1

The -E argument specifies a trusted connection. This is the default setting for sqlcmd, and so the -E argument can be omitted.

When you connect to a SQL Server Express server, you must specify the server name and, if SQL Server Express is installed in a named instance, the instance name. By default, sqlcmd uses Windows Authentication. If you are connecting to the SQL Server Express server by using SQL Server Authentication, you must also provide the logon information for connecting to the SQL Server Express server.

How do I open a command prompt at a given directory in windows?

This may be useful if you are playing with command line programs!


sqlcmd -S (local)/SQLExpress

That is by specifying the local machine without explicitly providing a domain name.


--******************************************************
--* Full backup
--* You only need to set 2 variables
--*    @dbname
--*    @root_dir
--******************************************************

--******************************************************
--* Declare variables
--******************************************************
--date and filename
declare @d varchar(255), @filename varchar(255);

--Root directory
declare @root_dir varchar(255);

--database name to take backup
declare @dbname varchar(64);

--database name without spaces
declare @dbname_in_path varchar(255);

--database backup label
declare @db_backup_label varchar(255);



--******************************************************
--* Specify database name and root directory
--******************************************************
set @dbname = N'my database name';
set @root_dir = N'c:\my-root';
select @db_backup_label = @dbname + N' Full Database Backup';

--******************************************************
--* Get date and clean it up: Aug__6_2016__4_07PM
--******************************************************
--get date
set @d = getdate();

--replace : in date string with underscores
--replace all empty spaces with underscores as well
select @d = replace(replace(@d,':','_'),' ','_');

--******************************************************
--* Cleanup database name for a path string
--******************************************************
--cleanup db
select @dbname_in_path = replace(@dbname,' ','_');

--******************************************************
--* Calculate final file name
--******************************************************
set @filename = @root_dir + N'\' + @dbname_in_path + N'_Full_'+ @d +'.bak' ;

--produces a name like
--C:\my-root\my-database-name_Full_Aug__6_2016__4_07PM.bak 

--******************************************************
--* Use the backup cmd. It is commented out while you debug
--* Uncomment below and put them on one line if needed
--******************************************************

-- BACKUP DATABASE @dbname TO  DISK = @filename  
-- WITH  RETAINDAYS = 365, NOFORMAT, NOINIT,  
-- NAME = @db_backup_label, NOSKIP, NOREWIND, NOUNLOAD,  STATS = 10;

--******************************************************
--* Test your filename
--******************************************************
select @filename
GO

sqlcmd -E -S (local)\SQLExpress -i fullbackup.sql

C:\my-root\my-database-name_Full_Aug__6_2016__4_07PM.bak

Adjust the code above to figure out a suitable filename for yourself.

meaning it is not scheduled yet. If you run this with the backup uncommented will take ONLY ONE backup.

You need to use windows task scheduled to run every 2 or 3 months along with a differential back up going off on a daily basis.

ms sqlserver backup command syntax

Search for: ms sqlserver backup command syntax

Microsoft reference

What is a backup set in MS SQLServer?

Search for: What is a backup set in MS SQLServer?

SQL Server Backup Terminology Part 1: Media Sets & Backup Sets

From: Bob Pusateri, SQL Server and Kindred Subjects

A backupset is just a backup!!

Above can be a file containing multiple backups

whats the meaning of retaindays in SQLServer backup?

Search for: whats the meaning of retaindays in SQLServer backup?

if I am backing up to a different file every time what is the use of RETAINDAYS in SQLServer?

Search for: if I am backing up to a different file every time what is the use of RETAINDAYS in SQLServer?

1. if I am taking a full backup to a file that is used only once using a date as part of that name, what is the point of using a) retaindays b) noinit (meaning append) c) noformat (meaning preserver header)

2. For differential backups, should I use ONE file or should I also use multiple files one for each date

Note: I am using a windows task manager to schedule these tasks.

How do you specify a FULL backup option to the backup command in SQLServer?

Search for: How do you specify a FULL backup option to the backup command in SQLServer?

A reasonable reference on how to use backup command: from ms


USE AdventureWorks2008R2;
GO
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2008R2';
GO

Apparently there is a table called backupset in MS SQLServer

backupset table in MS SQLServer

Search for: backupset table in MS SQLServer


BACKUP DATABASE name-of-db 
TO  DISK = @filename  
    WITH  RETAINDAYS = 365,  -- Don't think this matters
    NOFORMAT,                -- append (shouldn't matter)
    NOINIT,                  -- append (shouldn't matter)
    NAME = 'your database label you want', 
    NOSKIP,                  -- check for exp date (shouldnt matter)
    NOREWIND,                -- tape thing
    NOUNLOAD,                -- tape thing
    STATS = 10;              -- % complete notification

BACKUP DATABASE name-of-db 
TO  DISK = @filename  
    WITH NAME = 'your database label you want',

Apparently sqlserver has issues with google or other network drives

How to use directory or file names with spaces in windows command line programs?

Search for: How to use directory or file names with spaces in windows command line programs?


@rem *********************************************************
@rem notice the space in Google Drive directory!!!
@rem If an option don't use spaces in folder or filenames
@rem *********************************************************

set from-dir="C:\Users\...\Google Drive\sane-dir-name"
set to-dir=C:\satya\some-other-sane-name

copy %from-dir%\*.*  %to-dir%

Does my differential backup set tell me what my corresponding full backup set is?

Search for: Does my differential backup set tell me what my corresponding full backup set is?


Location on the local drive where backups are taking place
corresponding scripts directory
Equivalent scripts directory on google drive
Equivalent backup location on google drive to sweep to
Name of the windows task

What if windows task scheduler password changes?

Search for: What if windows task scheduler password changes?

If running a batch file you may want to the directory where the scripts reside. Sometimes your cmd file may need another file to invoke.

Go with absolute path names to save files to


BACKUP DATABASE name-of-db 
TO  DISK = @filename  
    WITH DIFFERENTIAL,
    NAME = 'your database label you want';

What is history tab on Windows task scheduler and how do I enable it?

Search for: What is history tab on Windows task scheduler and how do I enable it?

What happens if password changes to scheduled jobs in windows task scheduler?

Search for: What happens if password changes to scheduled jobs in windows task scheduler?

full backup: sqlcmd file: Wrote a backup command in a sqlcmd script to take a full backup

windows batch file: Wrote a windows batch file to execute and take a backup on the local drive

Scheduled a monthly task to run the windows batch file

differential backup: sqlcmd file: Wrote a backup command in a sqlcmd script to take a differential backup

windows batch file: Wrote a windows batch file to execute and take a backup on the local drive every day

Scheduled a monthly task to run the windows batch file

Sweep: windows batch file to sweep: wrote a batch file to sweep the local backup files to google drive

Scheduled this batch file to run every day to sweep files to google drive

Send emails: powershell script to send emails: wrote a powershell script if the xcopy to google drive fails

Setup an admin gmail account to send the emails from the powershell script

wrote a windows command file to invoke the powershell script with the right execution permissions to send the email

Included this sendmail script in the xcopy sweep batch file if the xcopy fails to receive an email

Here is how you create a new gmail account

Here is how you send emails from azure servers

Here is how you use powershell to help with sending emails

Here is how you use xcopy to sweep files


sqlcmd: How to write sql scripts in sqlcmd
    invoking sqlcmd from command files

backup: understand options of sql backup cmd
    fullbackup and differential backups

Windows task scheduler

xcopy
    dependencies on google drive
    sweep files to google drive
    dealing with xcopy errors

powershell to send emails
    basics
    its help system, online
    cmdlets, new-objects

Sending emails from windows servers
    powershell
    setting up admin google accounts

16 hours: At least 16 hours with research
3 to 4 days: May be a week

How can I schedule a task to run under the system account in windows?

Search for: How can I schedule a task to run under the system account in windows?

There seem to be an answer here

Go to Start > Administrative Tools > Task Scheduler

In the Task Scheduler window click "Create Task" on the right hand bar under the "Actions" pane

In the "Create Task" dialog click the "Change User or Group" button

Make sure "From this location" is set to the local machine name (to change click "Locations" button and select the local computer name)

Type "SYSTEM" in the text box and press ok . Under "When running the task, use the following user account:" you should see "NT AUTHORITY\SYSTEM".

At least I was able to change the login credentials. This user SYSTEM credential also seem to match the other frequently running tasks such as Google updater etc.

Revisiting in 2020 again

Understanding SQL server backups from sqlshack

Differential backups are documented here at MS