Automating db Backups

Is there a way to automate (schedule) backups of the databases in SQL Express? Similar to Maintenance Plans in SQL2000....Hi

Mark McFarlane,

If you come accross anything reagrding automating a schedule backup,Pls let me know,
Have been on the look out for this some time now.
Will do likewise.
email : papali4@.hotmail.com
Tnx

|||

hi,

not directly as SQL Server Agent is not provided, but you can workarond that using the OS provided native scheduler (AT or SCHTASKS)..

you can write down a cmd file like

<backup.cmd>

REM scheduled backup

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup executions started at - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'backup database [db_name]'; PRINT '';" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"BACKUP DATABASE [db_name] TO DISK = N'D:\BackupFolder\db_name.Bak' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup terminated at - ' + CONVERT(varchar, GETDATE());" >>d:\YourCheckFolder\ScheduledBCK.txt
</backup.cmd>

then you can schedule it as desired... the script will backup the db as required and will output the result of the task to a text file, d:\YourCheckFolder\ScheduledBCK.txt, you can later review to verify the performed operation...

in my own scenarios, I do add another "features".. I wrote a CLR assmbly exporting a stored procedure to "mimic" database mail feature (not present in SQLExpress) so that the "d:\YourCheckFolder\ScheduledBCK.txt" will be automatically sent to a defined list of recipients (sysadmins, dba or myself as well).. the component (amDBObj) is free and can be downloaded from http://www.asql.biz/en/Download2005.aspx .. feedback is apprecieted

the resulting script can be modified adding the

<add this>

REM adding SMTP mail to sysadmins, dba, etc of the backup operation result..

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing backup result of - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK-mailing.txt

SqlCmd -E -S(Local) -Q"SET NOCOUNT ON; DECLARE @.ret int;EXEC @.ret = [db_hosting_the_CLR_assembly].[dbo].[amSMTPmail] @.Server = N'your_mail_server', @.Sender = N'the_SQLsender@.sender.com', @.AddressesTO = N'me@.me.com', @.AddressesCC = N'further_recipients@.domain.com', @.AddressesCCN = NULL, @.AttachFiles = N'd:\YourCheckFolder\ScheduledBCK.txt', @.Subject = N'Backup performed', @.MessageBody = N'Backup performed'; SELECT @.ret AS [Execution result];" >>d:\YourCheckFolder\ScheduledBCK-mailing.txt

</add this>

or whatever required change to the original cmd file...

regards

|||

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs. Also, what about SSIS in express edition. Do have to install them seperately. Thanks

|||

UMAR DAR wrote:

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs.

hy, perhaps you can have a look at a great artice (and tool) by Jasper Smith, SQL Server MVP, at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29, based on WinNT native scheduler as well... but all these are not SQL Server jobs, as SQLExpress does not provide SQL Server Agent...

UMAR DAR wrote:

Also, what about SSIS in express edition. Do have to install them seperately. Thanks

SSIS are not available as well, in SQLEpress edition.. http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

|||

I have a similar solution posted here that's not incredibly elegant but it works just fine. It goes through and backs up all databases in a given instance and there is an included batch file to schedule it. The video deployment instructions can be seen here:

http://www.jumpstarttv.com/Media.aspx?vid=30

or written instructions here:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

-- Brian