Backup DB on Remote SQL Server Express

Question

I need to create a TSQL script to backup a db on a remote SQL Server Express (2005). I have a SQL Server 2005 on another box. Not sure how I can run the script from this SQL Server 2005 to do the backup job.

The script is something like this:

RESTORE DATABASE [myDB] FROM  DISK = N'C:\Tmp\myDB.bak' WITH  FILE = 1,
   NOUNLOAD,  STATS = 10

Actually, I tried this SQL script on the remote SQL Server Express by using SQL Server Management Studio Express and it runs OK. The reason I ask this question is that I can schedule a job on SQL Server 2005, but I cannot create a schedule job on the remote SQL Server Express.

Another way, I think, is to create a SQL SP on the SQL Server Express first. Then I'll write a simple console application to connect to the SQL and run the SP as a Windows Scheduled job.

Answer 1

There is no need to do this by TSQL. SQL Server (also Express) includes a utility called sqlmaint.exe, which allows you to perform backup operations on a local or remote SQL server. Simply write a batch file calling sqlmaint with the correct command line parameters (documentation) and put this batch file in Windows Scheduler.

If you still want to do it by TSQL, SQL Server also contains osql.exe, which allows you to execute arbitrary SQL statements on a local or remote server. Again, you can automate it using simple batch files.

EDIT: If you want to call the TSQL script using your own application, it might be helpful to know about your programming language or data access technology of choice.