Backup database job with SQL Server Agent

Sometimes I receive a request for instructions on how to set up a SQL Server Agent Job  to backup a database.

Setting up a SQL Server Agent Job  is easy with the GUI. Follow these instructions to set up a basic SQL Server Agent Job.

 Once you’ve set up this job to manage a backup , you can schedule a job or add custom error handling , for example, sending an email on success or failure or SQL Server - BackUp All Databases 

 Creating a backup can cause a high level of IO . Consider the impact of creating a backup on a server read more on BACKUPIO and sql database backup

Create a SQL Server Agent Job

Open SQL Server Management Studio (SSMS ) and connect to the SQL Server Instance. Expand the SQL Server Agent node and right click on Jobs , and click on New Job

Type the name

Type a name for the SQL Server Agent Job. Also make sure the owner is OK and has sufficient permissions

Click on Steps

Create the job step

Click on the New button

Add the code

 

Add a Step name and then add the code. The code in this example is to backup a database to disk . Use the script on SQL Server - BackUp All Databases to backup all databases. Customise the code for your requirements.

 Click OK

Click OK to create this job

 

Start the job

Expand the SQL Server Agent Job , right click on your job and click the Start Job option

Process Dialog

 Once you’ve started the job , a Process dialog will appear , reporting on in- progress job status

Check View History

 Expand the Jobs node , right click on the Job and View History.

 

 

Read More

 

SQL Server - Display sql backup history for a single database

BACKUP LOG with TRUNCATE_ONLY is discontinued

Slow SQL Server Backup and Restore with DBCC TRACEON (3004, 3605, -1)

 

DataFileType will be assumed to be widechar because the data file has a Unicode signature

A SQL Server Agent Job started failing with the “DataFileType will be assumed to be widechar because the data file has a Unicode signature” error.  

 

LogfileI:\mypath\MSSQL$INST1\LOG\bulk.log [SQLSTATE 01000] (Message 0)  Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature. [SQLSTATE 01000] (Message 4830)  Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature. [SQLSTATE 01000] (Message 4830)   [SQLSTATE 42000] (Error 50000).  The step failed.

 

I fixed the problem by adding the DATAFILETYPE = ‘widechar’ to the BULK INSERT statement . According to SQL Server BOL :

 

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

Specifies that BULK INSERT performs the import operation using the specified data-file type value.

Widechar = Unicode characters

 

BULK INSERT #File From 'I:\mypath\MSSQL$INST1\LOG\bulk.log ' WITH(DATAFILETYPE = 'widechar',ROWTERMINATOR = '\n');

 Read More

SQL Server Agent Job Steps

SQL Server agent fixed roles

SQL Server - Send sql server agent job history through email notification

Sometimes you cannot be in front of your laptop, therefore you  rely on email notifications to your handheld , such as Blackberry. It is useful in those circumstances to receive all or part of the SQL Server Agent Job History Failure error message as part of an email message. Using the information , you can contact a colleague to log on and apply a fix.

 I execute the query as part of a sp_send_dbmail using SQL Server Database Mail. This process is a Job Step – the job goes to  “On failure action”.   As mentioned , I do not rely just on this method , as the error message is also logged onto an Output file, which can be investigated.   I’ve been in many situations where SMTP is unavailable and the job status email hasn’t arrived – therefore relying on the Output file

 The sysjobhistory and sysjobs table supply the history information. In the query below, the result is searching for the latest instance of an error for the particular job. The recordset generated by the query on the @query parameter, is appended to the email.

 

--query returning the latest error message on a sql server agent job
use msdb
go
DECLARE @job_name nvarchar(128)
SET @job_name = 'test'

select top 1 sjs.name, msdb.dbo.agent_datetime(run_date,run_time), [message] 
from dbo.sysjobhistory  sjh
INNER JOIN sysjobs sjs ON sjs.job_id = sjh.job_id
WHERE sjs.name = 'test' and sql_message_id > 0 
order by instance_id desc

 

--sample of sending an email executing the query
EXEC msdb.dbo.sp_send_dbmail 
     @profile_name = 'Main',
     @body = 'write test',
     @subject = 'write test fail' ,
     @recipients = 'jack@sqlserver-dba.com',
   @execute_query_database = 'msdb',
     @query = 'select top 1 sjs.name, msdb.dbo.agent_datetime(run_date,run_time), [message] 
from dbo.sysjobhistory  sjh
INNER JOIN sysjobs sjs ON sjs.job_id = sjh.job_id
WHERE  sjs.name = ''test'' and sql_message_id > 0 
order by instance_id desc'
     
go

 Read More

SQL Server Agent Job Steps - SQL Server DBA

List failed SQL server Jobs with Powershell - SQL Server DBA

SQL Agent Jobs – Schedule in seconds - SQL Server DBA

SQL Server – Erase Phantom System Health Records – reason 193

Question: In SQL Server 2008 there is a SQL Server Agent Job called syspolicy_purge_history . Step 3 - Erase Phantom System Health Records is failing with reason 193. I have a daily Powershell script - List failed SQL server Jobs with Powershell which reports the failure

How can I fix?

Answer:Look at the command in the Job Step. It will be something like:

 It’s attempting a mapping to the SQL Server Instance Path. Which is probably something like:

 (Get-Item SQLSERVER:\SQLPolicy\MyServer\Instance).EraseSystemHealthPhantomRecords()

 C:\Program Files\xxxx\xxx

 Look at the C:\ , is there a file called “Program” ? Not a directory , but a file. If so , rename it and rerun the job. It should fix the error .

If this solution does not fix the reason 193 issue on Erase Phantom System Health Records, contact me and I’ll help you out

Related Posts

How to request SQL Server troubleshooting

SQL Server Agent Job Steps

List failed SQL server Jobs with Powershell

 

SQL Server – Find the maintenance plan log file path

Question: I’m  attempting to change the maintenance plan log file path . The aim is to change the path to a non-critical path. The non-critical path won’t have any database files.  I can view the Agent logs -  but am struggling to locate the maintenance plan log files.

Can you tell me how to find the path?

Answer :  Open the SQL Server Management Studio (SSMS). Open the Management | Maintenance Plan. Select the Maintenace Plan – Right Click and select Modify. At the   top , next to Management Connections ,  there is a little icon that looks like two reports. Click the icon , you should see the Reporting and Logging dialog. box where you can change the path.