Sql – Backup DB on Remote SQL Server Express

sql-server-2005sql-server-express

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.

Best Answer

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.

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql-server – Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql-server – Check if table exists in SQL Server

Related Topic