"Faking" Maintenance Plans for SQL Server Express

[reporting]

An application that we use uses SQL Server Express 2005 as the database. Unlike SQL Server 2000 and 2005, we cannot create Maintenance Plans for SQL Server Express.

Is there a relatively easy way to "fake" a maintenance plan using SQL scripts and the Windows Scheduler?

Thanks very much,

John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[ptheriault]

Yes,
Create script files and execute them using Windows task scheduler.

The first file you need is a ".bat" file. Here is an example

SQLCMD -Usa -Ppassword -SServer1 -i "d:\Scripts\your_script.sql" -o "d:\ScriptsOut\output_results.out"

then you need the ".sql" which is where you put your maint commands.

use your_database
go
ALTER INDEX ...
or 
USE master
GO
BACKUP DATABASE

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[reporting]

Thanks very much Paul!

The maintenance plans I setup for our SQL Server puts the backup files in a subdirectory of the same name as the database and produces backup files in the format
SQL_Database_Name_db_yyyymmddhhmm.BAK and backups of the transaction logs in the format SQL_Database_Name_tlog_yyyymmddhhmm.TRN.

Can that also be done in the SQL script? If so, can you please give me a piece of code that will do that?


John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[ptheriault]

here is an example of appending the datetime to a backup file. Make sure to change the path. You would create a .sql file for each db or log file you wanted to backup then execute them through windows scheduled tasks.

Declare @SQL nVarChar(4000)
Set @SQL = N'BACKUP DATABASE [b][yourDB][/b] TO DISK = ''M:\backups\[b]db_name[/b]_' + convert(char(8),getdate(),112) + '.BAK'''
EXEC (@SQL)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[reporting]

Thanks very much Paul. I'll try that and report back!

John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca