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.
satya - 8/6/2016, 1:19:49 PM
SQL Server 2008 task scheduler?
SQL Server 2008 task scheduler?
satya - 8/6/2016, 1:20:03 PM
How do I schedule a backup database program on windows servers
How do I schedule a backup database program on windows servers
Search for: How do I schedule a backup database program on windows servers
satya - 8/6/2016, 3:28:44 PM
How to use Windows task scheduler for SQL server backup schedules?
How to use Windows task scheduler for SQL server backup schedules?
Search for: How to use Windows task scheduler for SQL server backup schedules?
satya - 8/6/2016, 3:30:28 PM
How to schedule and automate backups of SQL Server databases in SQL Server Express
Read this
How to schedule and automate backups of SQL Server databases in SQL Server Express
satya - 8/6/2016, 4:08:24 PM
Command line options for sqlcmd
Command line options for sqlcmd
satya - 8/6/2016, 4:19:45 PM
Examples
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.
satya - 8/6/2016, 4:22:07 PM
How do I open a command prompt at a given directory in windows?
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!
satya - 8/6/2016, 4:24:36 PM
You could also do this apparently
sqlcmd -S (local)/SQLExpress
That is by specifying the local machine without explicitly providing a domain name.
satya - 8/6/2016, 4:46:51 PM
Here is a .sql file to take a full backup of a database
--******************************************************
--* 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
satya - 8/6/2016, 4:48:14 PM
Here is how you run this in a command file
sqlcmd -E -S (local)\SQLExpress -i fullbackup.sql
satya - 8/6/2016, 4:48:36 PM
Here is the filename it will print
C:\my-root\my-database-name_Full_Aug__6_2016__4_07PM.bak
satya - 8/6/2016, 4:48:50 PM
Adjust the code above to figure out a suitable filename for yourself.
Adjust the code above to figure out a suitable filename for yourself.
satya - 8/6/2016, 4:50:29 PM
Note: This program produces ONLY ONE backup
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.
satya - 8/7/2016, 10:42:38 AM
ms sqlserver backup command syntax
ms sqlserver backup command syntax
satya - 8/7/2016, 10:47:56 AM
What is a backup set in MS SQLServer?
What is a backup set in MS SQLServer?
satya - 8/7/2016, 10:59:03 AM
SQL Server Backup Terminology Part 1: Media Sets & Backup Sets
SQL Server Backup Terminology Part 1: Media Sets & Backup Sets
From: Bob Pusateri, SQL Server and Kindred Subjects
satya - 8/7/2016, 11:03:45 AM
For my purpose a media set can be a file
A backupset is just a backup!!
Above can be a file containing multiple backups
satya - 8/7/2016, 11:03:59 AM
whats the meaning of retaindays in SQLServer backup?
whats the meaning of retaindays in SQLServer backup?
Search for: whats the meaning of retaindays in SQLServer backup?
satya - 8/7/2016, 11:08:52 AM
if I am backing up to a different file every time what is the use of RETAINDAYS in SQLServer?
if I am backing up to a different file every time what is the use of RETAINDAYS in SQLServer?
satya - 8/7/2016, 11:16:51 AM
I am seeking answers for
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.
satya - 8/7/2016, 11:35:00 AM
How do you specify a FULL backup option to the backup command in SQLServer?
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?
satya - 8/7/2016, 11:37:10 AM
A reasonable reference on how to use backup command: from ms
A reasonable reference on how to use backup command: from ms
satya - 8/7/2016, 11:38:10 AM
One example from above
USE AdventureWorks2008R2;
GO
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.Bak'
WITH FORMAT,
MEDIANAME = 'Z_SQLServerBackups',
NAME = 'Full Backup of AdventureWorks2008R2';
GO
satya - 8/7/2016, 11:41:56 AM
Apparently there is a table called backupset in MS SQLServer
Apparently there is a table called backupset in MS SQLServer
satya - 8/7/2016, 11:42:09 AM
backupset table in MS SQLServer
backupset table in MS SQLServer
satya - 8/7/2016, 11:47:51 AM
So full backup options for ONE file per backup set
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
satya - 8/7/2016, 11:49:30 AM
Given that following may be a simplified one
BACKUP DATABASE name-of-db
TO DISK = @filename
WITH NAME = 'your database label you want',
satya - 8/7/2016, 12:33:58 PM
Apparently sqlserver has issues with google or other network drives
Apparently sqlserver has issues with google or other network drives
satya - 8/7/2016, 12:50:53 PM
How to use directory or file names with spaces in windows command line programs?
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?
satya - 8/7/2016, 12:56:21 PM
Ok, double quotes are allowed. example
@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%satya - 8/7/2016, 1:50:14 PM
Does my differential backup set tell me what my corresponding full backup set is?
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?
satya - 8/7/2016, 1:51:29 PM
Key things to note are
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
satya - 8/7/2016, 1:51:49 PM
What if windows task scheduler password changes?
What if windows task scheduler password changes?
Search for: What if windows task scheduler password changes?
satya - 8/7/2016, 1:53:31 PM
Key things to note with windows task scheduler
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
satya - 8/7/2016, 1:56:40 PM
Here is the equivalent command for a differential backup
BACKUP DATABASE name-of-db
TO DISK = @filename
WITH DIFFERENTIAL,
NAME = 'your database label you want';
satya - 8/8/2016, 4:19:11 PM
What is history tab on Windows task scheduler and how do I enable it?
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?
satya - 8/8/2016, 4:19:35 PM
What happens if password changes to scheduled jobs in windows task scheduler?
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?
annonymous - 8/10/2016, 10:38:27 AM
So what I have done so far
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
annonymous - 8/10/2016, 10:45:20 AM
Here is how you create a new gmail account
annonymous - 8/10/2016, 10:46:07 AM
Here is how you send emails from azure servers
annonymous - 8/10/2016, 10:46:35 AM
Here is how you use powershell to help with sending emails
annonymous - 8/10/2016, 10:47:27 AM
Here is how you use xcopy to sweep files
annonymous - 8/10/2016, 10:52:35 AM
Number of things I needed to know do this
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
annonymous - 8/10/2016, 10:54:01 AM
How long did it take?
16 hours: At least 16 hours with research
3 to 4 days: May be a week
satya - 8/13/2016, 4:19:15 PM
How can I schedule a task to run under the system account in windows?
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?
satya - 8/13/2016, 4:21:19 PM
Here is the summary
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".
satya - 8/13/2016, 4:26:49 PM
Yes. that seem to work
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.
satya - 7/25/2020, 1:51:00 PM
Revisiting in 2020 again
Revisiting in 2020 again
satya - 7/25/2020, 1:51:17 PM
Understanding SQL server backups from sqlshack
satya - 7/25/2020, 1:51:35 PM
A key image from SQLShack
satya - 7/25/2020, 1:55:21 PM
Differential backups are documented here at MS