1

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.

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Read the doc. What is PlantName or PlantID? How can I define one? My understanding is that a plan defines a list of databases (in my case is for a list of dbs in a remote SQL Server Express.
You don't need PlanName or PlanID. Have a look a the last example at the very end of the page.
So, in your case, you would use something like "sqlmaint -S myServer -D myDB -BkUpDB C:\Tmp". Note, however, that the name of the backup file is auto-generated (see BkUpDB on that page). If you need a fixed file name, consider the osql solution.
Thank you @Heinzi for the information. By using -S -D, I can do one backup from one server. Not sure if I can use Plan to backup a list of dbs from list of servers? By the way, does sqlmaint.exe work for SQL Server Express? The MSDN says it is for SQL Server 2005.
I can run the exe but I got an error. Anything missing? Here is the er: [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.