Automate daily backup of database

I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I can't create database backup automate/daily basis. So, the only possibilities are I have to use either TSQL or create Job. I don't have much understanding of SQL Job, so I am left with T-SQL only.

Can anyone explain me how to do automate backup of database in T-SQL using stored procedure or is there any other options I have to do that.

Regards.

Since you are using SQL Server Express, you will have to get creative. You don't have any SQL Server native scheduling tool, you can utilize Task Scheduler, and schedule a daily task to run SQLCMD, with query text to backup your database.

I'd recommend creating a script that does some string manipulation to generate a day-unique filename for the BACKUP DATABASE T-SQL.

Your script could resemble something like this:

-- declare the backup filename (with path) without the file extension
declare @DestFile varchar(128) = 'C:\BackupDir\BackupFileName_';

-- get the current date as a nice and sortable string
declare @CurrentDate varchar(64) = '';
set @CurrentDate = replace(convert(varchar(10), getdate(), 111), '/', '');

set @DestFile += @CurrentDate + '.bak';

backup database YourDatabaseName
to disk = @DestFile;
go

Save that T-SQL to a script file (for example's sake, C:\YourScriptDir\BackupDatabase.sql). To run this script using SQLCMD, you could do:

sqlcmd -S YourServerName\YourInstanceName -i C:\YourScriptDir\BackupDatabase.sql

Then just schedule that with Task Scheduler to run daily. It's one giant spaghetti workaround, but that's what you get with a free version.

(the above process would be tailored to a database in Simple Recovery mode. If you are in Full, then you need to consider transaction log backups as well)

StackExchange Database Administrators Q#25292, answer score: 10