[Solucionado] Crear un Trabajo de Respaldo (backup Job) en SQL Server y guarde en una carpeta con el nombre de la fecha del respaldo

Algo muy importante en un servidor es realizar respaldos automáticos, a continuación un ejemplo paso a paso para: realizar una tarea programada que realice un trabajo a una hora determinada, cree una carpeta con la fecha con formato YYYYMMDD (Y=Año/Year , M=Mes/Month , D=Día/Day) y dentro de la misma guardar el respaldo de la base de datos.

1. Ingresar en Microsoft SQL Server Management Studio.

2. En el explorador de objetos (Object Explorer), hacer clic con el botón derecho sobre Trabajo (Job), clic en Nuevo Trabajo (New Job...).

3. Escriba el nombre que tendrá el trabajo en el campo Nombre (Name), luego seleccione el Propietario (Owner).

4. Haga clic en la página de Pasos (Steps) y luego clic en el botón Nuevo...(New).

5. Escriba el nombre del paso (Step Name), todos los demás campos se pueden dejar igual, en el comando debe escribir los siguientes comandos para poder crear una carpeta utilizando comandos TSQL.

declare @MD varchar(100), @wk_no varchar(100)

set @wk_no = 'C:\Respaldos\' + Convert(VarChar(4),DatePart(Year,GetDate()))+Right('0'+Convert(VarChar(2),DatePart(Month,GetDate())),2)+Right('0'+Convert(VarChar(2),DatePart(Day,GetDate( ))),2)

SET @MD = ' mkdir ' + @wk_no

EXEC xp_cmdshell @MD, no_output

6. Hacer clic en Aceptar (Ok), yse debe hacer clic en el botón de Nueva Consulta, para ejecutar un comando para permitir utilizar el xp_cmdshell.

7. Se debe ejecutar la siguiente sentencia SQL para permitir realizar cambios, de lo contrario aparecería un error.
EXEC sp_configure 'allow updates', 0;
RECONFIGURE;

El error que aparecería si omitimos este paso sería:
Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.

8. Escribir EXEC sp_configure 'xp_cmdshell', 1 y luego clic en el botón de Ejecutar. 

En el cuadro de mensajes aparecerá: Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Si hemos omitido el paso anterior a este pudiera aparecer el siguiente mensaje:

Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.


9. Una vez realizado lo indicado anteriormente, se procede a crear un nuevo paso (Step). Para ello dar doble click sobre el nombre del Trabajo (Job), luego clic en Pasos (Steps) y clic en el botón Nuevo (New). 

10. Escriba el siguiente comando para realizar el respaldo de una base de datos dentro de la carpeta con la fecha del día que se está ejecutando.


Declare @Ruta VarChar(100)
SET @Ruta = 'C:\Respaldos\'  + Convert(VarChar(4),DatePart(Year,GetDate()))+Right('0'+Convert(VarChar(2),DatePart(Month,GetDate())),2)+Right('0'+Convert(VarChar(2),DatePart(Day,GetDate( ))),2) + '\'
Set @Ruta=@Ruta+'prueba_'+Convert(VarChar(4),DatePart(Year,GetDate()))+Right('0'+Convert(VarChar(2),DatePart(Month,GetDate())),2)+Right('0'+Convert(VarChar(2),DatePart(Day,GetDate( ))),2)

BACKUP DATABASE nombrebasededatosprueba TO DISK = @Ruta WITH INIT


11. Clic en Aceptar (Ok).


Si al tratar de ejecutar el comando EXEC sp_configure 'xp_cmdshell', 1 llega a aparecer el siguiente error:
SQL 2005 Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51  The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

Debes ejecutar la siguiente consulta en SQL:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO


NOTA:
Para ahorrar espacio cuando se hacen los respaldos se puede activar la compresión (sólo aplica a partir de SQL Sver 2008 en adelante):

sp_configure 'backup compression',1
go
reconfigure;
go