Month: June 2014

Schedule Database Backup in SQL Server

1. In Microsoft SQL Server Management Studio, right-click SQL Server Agent, then click New, then click Job

2. Input Name, “JOB_BACKUP_DATABASE”

3. Click Steps, then click New

4. Input Step Name, “BACKUP DATABASE TO DISK”

5. Paste the following Command

DECLARE @todayText char(8)
SET @todayText = CONVERT(char(8), GETDATE(), 112)

DECLARE @path nvarchar(256)
SET @path = N'D:\DatabaseBackup\DB_NAME.{DATESTAMP}.bak'
SET @path = REPLACE(@path, '{DATESTAMP}', @todayText);

BACKUP DATABASE [DB_NAME]
    TO  DISK = @path
    WITH NOFORMAT, NOINIT,  NAME = N'DB_NAME - Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

6. Click OK

7. Click Schedules, then click New

8. Input Name, “Daily 18:00″

9. Setup the appropriate schedule

10. Click OK

11. Click OK again

Advertisements

Backup SQL Server Database using Script

DECLARE @todayText char(8)
SET @todayText = CONVERT(char(8), GETDATE(), 112)

DECLARE @path nvarchar(256)
SET @path = N'D:\DatabaseBackup\DB_NAME.{DATESTAMP}.bak'
SET @path = REPLACE(@path, '{DATESTAMP}', @todayText);

BACKUP DATABASE [DB_NAME] 
	TO  DISK = @path
	WITH NOFORMAT, NOINIT,  NAME = N'DB_NAME - Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO