Is it possible to schedule "Generate SQL script"?

Is it possible to schedule "Generate SQL script"?

Post by Preciou » Sat, 25 Jan 2003 14:03:39



Everyday, I used to run "Generate SQL Script" from Entp. Manager manually to
take backup of an important database. Is it possible to automate/schedule
this task?

TIA

J Justin

 
 
 

Is it possible to schedule "Generate SQL script"?

Post by Dejan Sark » Sat, 25 Jan 2003 15:36:46


You can use VB script and SQL DMO (Distributed Management Objects), or you
can create an EXE using DMO and put that in a job. Here is a sample script:

Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String

Sub Script()
    Set oSS = New SQLDMO.SQLServer
    Set oT = New SQLDMO.Transfer
    oSS.Connect "server", "user", "password"  'Connect to the server
    Set oDb = oSS.Databases("pubs")
    oT.CopyAllTables = True
    oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> Everyday, I used to run "Generate SQL Script" from Entp. Manager manually
to
> take backup of an important database. Is it possible to automate/schedule
> this task?

> TIA

> J Justin