Sql Auto grow: task

Showing posts with label task. Show all posts

Thursday, March 29, 2012

Automation of Excel to SQL Server 2005

Hi all,

I have a task which needs to be automated.

1) Excel spreadsheet load into sql server database as a table.

2) Then , there will be some quering of data joining this table ( imported from excel) with other tables.

3) Then, output should be back into excel spreadsheet.

All these steps 1,2,3 need to be automated.

Could anybody give me the different steps(if not,, any links which guide me doing so), for doing this and any pre setup required for this.

Thanks for all your help..

You can do this using distributed quires...

Here the sample,

Code Snippet

--To fetch the data from the Excel

select * from

OpenDataSource('Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

--To store the data back to the Excel

insert into

OpenDataSource('Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

Values('11','Name11')

Automation of database restoration

We have sql server standard edition , I want to automate the task of data
restoration on a daily basis .
How can I automate copying and restoration of the data on standalone PC .
Kindly suggest me the best option for the same .
Regards,
SwatiI assume that you have two database servers and you want to keep them in
synch.
You have to do two things
BACKUP your database(s) on your primary server
RESTORE your backups to your standby server.
It is simple to set up a job which issues BACKUP commands. In fact you
should be doing that now as part of your backup routine.
The RESTORE is easy. Assuming that the account that SQL Server is running
as on your second server has rights to the machine where your primary
backups are stored you can do something like this: RESTORE DATABASE foo FROM
DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
If you have a share on the server you can access the share:
RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak' WITH
REPLACE
If your database servers have a different disk layout you might have to use
the WITH MOVE option to move the physical files to a location that works on
your secondary machine.
Once you are able to issue the appropriate BACKUP and RESTORE commands the
next thing you have to do is create a couple of jobs which will automate the
process for you. You can start a job (on another server) from your main
server, but the easiest method might be to have the jobs be independent of
each other. Schedule your backup job to run at, say 10pm. Lets say that it
takes 30 minutes. You could schedule the restore job on the other server to
start at 10:45pm.
You may find these articles helpful:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
Keith
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> We have sql server standard edition , I want to automate the task of data
> restoration on a daily basis .
> How can I automate copying and restoration of the data on standalone PC .
> Kindly suggest me the best option for the same .
>
> Regards,
> Swati
>
>|||Keith,
Thanks for response.
I agree that we can write manual commands for restoring the database on
standby server . Let me explain you our backup stategy
Full backup : 12:00 AM
Diff backup : evry 2 hours
Txn log backup : every 15 minute .this is done using DB maintenance plan .
so every time we get a new file. e.g mydb_db_200410260000.bak
Now I want to restore my full backup and diff backup which is done at
interval of 2 hours . How can I restore my full db backup and diff backup.
Regards,
Swati
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> I assume that you have two database servers and you want to keep them in
> synch.
> You have to do two things
> BACKUP your database(s) on your primary server
> RESTORE your backups to your standby server.
> It is simple to set up a job which issues BACKUP commands. In fact you
> should be doing that now as part of your backup routine.
> The RESTORE is easy. Assuming that the account that SQL Server is running
> as on your second server has rights to the machine where your primary
> backups are stored you can do something like this: RESTORE DATABASE foo
FROM
> DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> If you have a share on the server you can access the share:
> RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak' WITH
> REPLACE
> If your database servers have a different disk layout you might have to
use
> the WITH MOVE option to move the physical files to a location that works
on
> your secondary machine.
> Once you are able to issue the appropriate BACKUP and RESTORE commands the
> next thing you have to do is create a couple of jobs which will automate
the
> process for you. You can start a job (on another server) from your main
> server, but the easiest method might be to have the jobs be independent of
> each other. Schedule your backup job to run at, say 10pm. Lets say that
it
> takes 30 minutes. You could schedule the restore job on the other server
to
> start at 10:45pm.
> You may find these articles helpful:
>
> 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> http://support.microsoft.com/?id=314515
> 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
> Paper)
> http://support.microsoft.com/?id=323135
> 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> http://support.microsoft.com/?id=325220
> 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> http://support.microsoft.com/?id=821786
> 321247 HOW TO: Configure Security for Log Shipping
> http://support.microsoft.com/?id=321247
> 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> Errors
> http://support.microsoft.com/?id=329133
>
> --
> Keith
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> > We have sql server standard edition , I want to automate the task of
data
> > restoration on a daily basis .
> > How can I automate copying and restoration of the data on standalone PC
.
> > Kindly suggest me the best option for the same .
> >
> >
> > Regards,
> > Swati
> >
> >
> >
> >
>|||swati
Create a job that does RESTORE full and DIFF database
For more details please refer to the BOL
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:upWyC21uEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Keith,
> Thanks for response.
> I agree that we can write manual commands for restoring the database on
> standby server . Let me explain you our backup stategy
> Full backup : 12:00 AM
> Diff backup : evry 2 hours
> Txn log backup : every 15 minute .this is done using DB maintenance plan .
> so every time we get a new file. e.g mydb_db_200410260000.bak
> Now I want to restore my full backup and diff backup which is done at
> interval of 2 hours . How can I restore my full db backup and diff backup.
> Regards,
> Swati
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> > I assume that you have two database servers and you want to keep them in
> > synch.
> >
> > You have to do two things
> > BACKUP your database(s) on your primary server
> > RESTORE your backups to your standby server.
> >
> > It is simple to set up a job which issues BACKUP commands. In fact you
> > should be doing that now as part of your backup routine.
> > The RESTORE is easy. Assuming that the account that SQL Server is
running
> > as on your second server has rights to the machine where your primary
> > backups are stored you can do something like this: RESTORE DATABASE foo
> FROM
> > DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> > If you have a share on the server you can access the share:
> > RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak'
WITH
> > REPLACE
> >
> > If your database servers have a different disk layout you might have to
> use
> > the WITH MOVE option to move the physical files to a location that works
> on
> > your secondary machine.
> >
> > Once you are able to issue the appropriate BACKUP and RESTORE commands
the
> > next thing you have to do is create a couple of jobs which will automate
> the
> > process for you. You can start a job (on another server) from your main
> > server, but the easiest method might be to have the jobs be independent
of
> > each other. Schedule your backup job to run at, say 10pm. Lets say
that
> it
> > takes 30 minutes. You could schedule the restore job on the other
server
> to
> > start at 10:45pm.
> >
> > You may find these articles helpful:
> >
> >
> > 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> > http://support.microsoft.com/?id=314515
> >
> > 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping
(White
> > Paper)
> > http://support.microsoft.com/?id=323135
> >
> > 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> > http://support.microsoft.com/?id=325220
> >
> > 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> > http://support.microsoft.com/?id=821786
> >
> > 321247 HOW TO: Configure Security for Log Shipping
> > http://support.microsoft.com/?id=321247
> >
> > 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> > Errors
> > http://support.microsoft.com/?id=329133
> >
> >
> > --
> > Keith
> >
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> > > We have sql server standard edition , I want to automate the task of
> data
> > > restoration on a daily basis .
> > > How can I automate copying and restoration of the data on standalone
PC
> .
> > > Kindly suggest me the best option for the same .
> > >
> > >
> > > Regards,
> > > Swati
> > >
> > >
> > >
> > >
> >
>|||If you want to use the second server for Disaster Recovery, you should also
write a script to export your logins from the source SQL Server to the
Destination SQL Server.
--
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Uri Dimant" wrote:
> swati
> Create a job that does RESTORE full and DIFF database
> For more details please refer to the BOL
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:upWyC21uEHA.3276@.TK2MSFTNGP15.phx.gbl...
> >
> > Keith,
> > Thanks for response.
> >
> > I agree that we can write manual commands for restoring the database on
> > standby server . Let me explain you our backup stategy
> > Full backup : 12:00 AM
> > Diff backup : evry 2 hours
> > Txn log backup : every 15 minute .this is done using DB maintenance plan .
> > so every time we get a new file. e.g mydb_db_200410260000.bak
> >
> > Now I want to restore my full backup and diff backup which is done at
> > interval of 2 hours . How can I restore my full db backup and diff backup.
> >
> > Regards,
> > Swati
> >
> > "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> > news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> > > I assume that you have two database servers and you want to keep them in
> > > synch.
> > >
> > > You have to do two things
> > > BACKUP your database(s) on your primary server
> > > RESTORE your backups to your standby server.
> > >
> > > It is simple to set up a job which issues BACKUP commands. In fact you
> > > should be doing that now as part of your backup routine.
> > > The RESTORE is easy. Assuming that the account that SQL Server is
> running
> > > as on your second server has rights to the machine where your primary
> > > backups are stored you can do something like this: RESTORE DATABASE foo
> > FROM
> > > DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> > > If you have a share on the server you can access the share:
> > > RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak'
> WITH
> > > REPLACE
> > >
> > > If your database servers have a different disk layout you might have to
> > use
> > > the WITH MOVE option to move the physical files to a location that works
> > on
> > > your secondary machine.
> > >
> > > Once you are able to issue the appropriate BACKUP and RESTORE commands
> the
> > > next thing you have to do is create a couple of jobs which will automate
> > the
> > > process for you. You can start a job (on another server) from your main
> > > server, but the easiest method might be to have the jobs be independent
> of
> > > each other. Schedule your backup job to run at, say 10pm. Lets say
> that
> > it
> > > takes 30 minutes. You could schedule the restore job on the other
> server
> > to
> > > start at 10:45pm.
> > >
> > > You may find these articles helpful:
> > >
> > >
> > > 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> > > http://support.microsoft.com/?id=314515
> > >
> > > 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping
> (White
> > > Paper)
> > > http://support.microsoft.com/?id=323135
> > >
> > > 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> > > http://support.microsoft.com/?id=325220
> > >
> > > 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> > > http://support.microsoft.com/?id=821786
> > >
> > > 321247 HOW TO: Configure Security for Log Shipping
> > > http://support.microsoft.com/?id=321247
> > >
> > > 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> > > Errors
> > > http://support.microsoft.com/?id=329133
> > >
> > >
> > > --
> > > Keith
> > >
> > >
> > > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > > news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> > > > We have sql server standard edition , I want to automate the task of
> > data
> > > > restoration on a daily basis .
> > > > How can I automate copying and restoration of the data on standalone
> PC
> > .
> > > > Kindly suggest me the best option for the same .
> > > >
> > > >
> > > > Regards,
> > > > Swati
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
>sql

Automating SQL Server Service Pack / Patch / Hot fix apply

In one of my task I have to apply the latest SQL Server Service Pack /
Patch / Hot fix on all the SQL Server Boxes in our client environment. There
are more than 400 SQL Server boxes with SQL Server version 6.5, 7.0 and 2000
running on Window NT, 2000 and 2003 Platforms. We have been given deadline of
one month to apply the SQL Server patches access all the box.
It is also being advised to rollout this task Quarterly on every year
as the Service pack / Patch are being released.
Hereby I am looking for some advise on
1. The best process to follow on for this task:
2. Automating this task:
Is anyone used any third party tools like "Opsware" to automate this
process?
3. Issues and Risks:
Issues and Risks which to be faced as part of this task .
4. Misc:
Any sort of information that would help with this task.
Thanks
S SIVAPRASAD
Have you considered Microsoft Systems Managment Server. It's really designed
just for change and configuration management. It's a great tool for applying
service packs & hotfixes accross the enterprise.
See http://www.microsoft.com/smserver/ev.../default.mspx.
Mike
This posting is provided "AS IS" with no warranties, and confers no rights."
When you include scripts, it should also include "Use of included script
samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in
message news:1C8805CA-3E70-452E-944A-30419598693D@.microsoft.com...
> In one of my task I have to apply the latest SQL Server Service Pack /
> Patch / Hot fix on all the SQL Server Boxes in our client environment.
> There
> are more than 400 SQL Server boxes with SQL Server version 6.5, 7.0 and
> 2000
> running on Window NT, 2000 and 2003 Platforms. We have been given deadline
> of
> one month to apply the SQL Server patches access all the box.
> It is also being advised to rollout this task Quarterly on every year
> as the Service pack / Patch are being released.
> Hereby I am looking for some advise on
> 1. The best process to follow on for this task:
> 2. Automating this task:
> Is anyone used any third party tools like "Opsware" to automate this
> process?
>
> 3. Issues and Risks:
> Issues and Risks which to be faced as part of this task .
>
> 4. Misc:
> Any sort of information that would help with this task.
> --
> Thanks
> S SIVAPRASAD
sql

Tuesday, March 20, 2012

Automatic synchronization of lookup columns

Data Flow:

Step 1: I use a OleDb Source Task to read data from a table (say table A) with 5 columns.

Step 2: Then I use a lookup task to add a sixth column (resulting from lookup on table B).

Step 3: Then I save the 6 columns to table C using a OleDb Destination Task.

Now, I go to underlying database and modify the tables like:

- remove 1 column from table A (not the column used for lookup later).
- remove 1 column from table B (not the columns used for lookup and not the columns retrieved after lookup)
- remove 1 column from table C (same as the one removed from table A).

If I get back to SSIS designer, it is nice enough to show warnings and ask whether I want to fix the metadata mismatch automatically. But, it does this for OleDb Source and Destination tasks only and NOT for lookup task. If I open the lookup task and hit the columns tab, it still shows old coulmn set for table B. It will be nice if these can be fixed automatically too. Or is it too much to ask for?

thanks,
Nitesh

Yes it'd be nice :)
You will notice similar behaviors also in other trasformations in the data flow (like Union All eg.). If you modify the structure of your data sources, you do need to check almost all of the sources, tasks and destinations involved.
Some months ago i asked in the beta place for a "Synchronize All" botton in the Data Flow editor form. But perhaps there will be better solutions then mine.
|||

Can you open a DCR for this?

thanks

Wednesday, March 7, 2012

automate sql scripting of jobs in sql agent

In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
I can manaully script out all exisiting sql agent jobs, and keep for backup.
How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
TIAHI
Use SQL-DMO:
'_______________________________________________________
Dim conServer
Dim fso, fsoF
Dim iFile, iFolder
Dim oJB
Dim strJob
Dim strCat
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "<>Your Server Name><" 'Minden felhasznÃ¡lÃ³i Job itt
talÃ¡lhatÃ³
strPath = "\\<<Your UNC path>>\Jobs\"
strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy Ã¡llomÃ¡ny kÃ©szÃ¼l a
dÃ¡tummal megjelÃ¶lve
For Each oJB In conServer.JobServer.Jobs
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
if strCat =0 then 'A replikÃ¡ciÃ³s Job-ok nem kerÃ¼lnek a szkriptbe
strJob = strJob & "----" &
vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "----" &
vbCrLf
strJob = strJob & oJB.Script(4) & vbCrLf
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
End If
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoF = CreateObject("Scripting.FileSystemObject")
Set iFolder = fsoF.getfolder(strPath)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Set FileContainer = iFolder.Files 'A harminc napnÃ¡l rÃ©gebbi Ã¡llomÃ¡nyokat
letÃ¶rÃ¶ljÃ¼k.
For Each f1 in FileContainer
if (f1.DateCreated < (Date - 30)) Then
f1.delete
end if
Next
Set fsoF = Nothing
'__________________________________________________________________
Andras Jakus MCDBA
"pk" wrote:
> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>|||PK, you may use that script provided by Andras to script out SQL jobs on qa
monthly basis or/and you may also want to look at other options:
You may also use SCRPTXFR that comes along with SQL Server 2000 which will
allow you to script out all the object in text files.
You may also backup MSDB database on a monthly basis (This will ensure not
just Jobs but alos DTS packages, alerts, etc to be backed-up)
You may also BCP out all the SysJobs table every month and incase of issues
you may just load back the data in the MSDB tables.
HTH.
Saleem@.sqlnt.com
"Andras Jakus" wrote:
> HI
> Use SQL-DMO:
> '_______________________________________________________
> Dim conServer
> Dim fso, fsoF
> Dim iFile, iFolder
> Dim oJB
> Dim strJob
> Dim strCat
> Dim strFilename
> Dim strPath
> Const ioModeAppend = 8
> Set conServer = CreateObject("SQLDMO.SQLServer")
> conServer.LoginSecure = True
> conServer.Connect "<>Your Server Name><" 'Minden felhasznÃ¡lÃ³i Job itt
> talÃ¡lhatÃ³
> strPath = "\\<<Your UNC path>>\Jobs\"
> strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy Ã¡llomÃ¡ny kÃ©szÃ¼l a
> dÃ¡tummal megjelÃ¶lve
> For Each oJB In conServer.JobServer.Jobs
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> if strCat =0 then 'A replikÃ¡ciÃ³s Job-ok nem kerÃ¼lnek a szkriptbe
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & oJB.Script(4) & vbCrLf
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> End If
> Next
> Set conServer = Nothing
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoF = CreateObject("Scripting.FileSystemObject")
> Set iFolder = fsoF.getfolder(strPath)
> Set iFile = fso.CreateTextFile(strFilename, True)
> iFile.Write (strJob)
> iFile.Close
> Set fso = Nothing
> Set FileContainer = iFolder.Files 'A harminc napnÃ¡l rÃ©gebbi Ã¡llomÃ¡nyokat
> letÃ¶rÃ¶ljÃ¼k.
> For Each f1 in FileContainer
> if (f1.DateCreated < (Date - 30)) Then
> f1.delete
> end if
> Next
> Set fsoF = Nothing
> '__________________________________________________________________
> Andras Jakus MCDBA
> "pk" wrote:
> > In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> > I can manaully script out all exisiting sql agent jobs, and keep for backup.
> >
> > How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> >
> > TIA
> >

automate sql scripting of jobs in sql agent

In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
I can manaully script out all exisiting sql agent jobs, and keep for backup.
How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
TIA
HI
Use SQL-DMO:
'_________________________________________________ ______
Dim conServer
Dim fso, fsoF
Dim iFile, iFolder
Dim oJB
Dim strJob
Dim strCat
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
található
strPath = "\\<<Your UNC path>>\Jobs\"
strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
dátummal megjel?lve
For Each oJB In conServer.JobServer.Jobs
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
strJob = strJob & "----" &
vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "----" &
vbCrLf
strJob = strJob & oJB.Script(4) & vbCrLf
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
End If
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoF = CreateObject("Scripting.FileSystemObject")
Set iFolder = fsoF.getfolder(strPath)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
let?r?ljük.
For Each f1 in FileContainer
if (f1.DateCreated < (Date - 30)) Then
f1.delete
end if
Next
Set fsoF = Nothing
'_________________________________________________ _________________
Andras Jakus MCDBA
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>
|||PK, you may use that script provided by Andras to script out SQL jobs on qa
monthly basis or/and you may also want to look at other options:
You may also use SCRPTXFR that comes along with SQL Server 2000 which will
allow you to script out all the object in text files.
You may also backup MSDB database on a monthly basis (This will ensure not
just Jobs but alos DTS packages, alerts, etc to be backed-up)
You may also BCP out all the SysJobs table every month and incase of issues
you may just load back the data in the MSDB tables.
HTH.
Saleem@.sqlnt.com
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> Use SQL-DMO:
> '_________________________________________________ ______
> Dim conServer
> Dim fso, fsoF
> Dim iFile, iFolder
> Dim oJB
> Dim strJob
> Dim strCat
> Dim strFilename
> Dim strPath
> Const ioModeAppend = 8
> Set conServer = CreateObject("SQLDMO.SQLServer")
> conServer.LoginSecure = True
> conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
> található
> strPath = "\\<<Your UNC path>>\Jobs\"
> strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
> dátummal megjel?lve
> For Each oJB In conServer.JobServer.Jobs
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & oJB.Script(4) & vbCrLf
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> End If
> Next
> Set conServer = Nothing
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoF = CreateObject("Scripting.FileSystemObject")
> Set iFolder = fsoF.getfolder(strPath)
> Set iFile = fso.CreateTextFile(strFilename, True)
> iFile.Write (strJob)
> iFile.Close
> Set fso = Nothing
> Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
> let?r?ljük.
> For Each f1 in FileContainer
> if (f1.DateCreated < (Date - 30)) Then
> f1.delete
> end if
> Next
> Set fsoF = Nothing
> '_________________________________________________ _________________
> Andras Jakus MCDBA
> "pk" wrote:
|||Have look at
http://www.nigelrivett.net/DMOScriptAllDatabases.html
It shows how to schedule scripting most objects in the database and save
them in sourcesafe (although I haven't got round to automating that yet -
suspect I will soon).
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>

Automate restore

hello people!

I am very new to the SQL World. Currently I work on DB2 and ORACLE and
this is a new "fruit" for me.

I have the task to automate the following scenario:
- backup database A
- restore database A into database B (overwrite database B)
- perform the above every night at 23:00.

Can anyone indicate me where do i find info how to perform the above?
I know that you can do it through the Enterprise Manager but I need to
script the above (probably).

Thanks"Demetris" <DMina@.laiki.com> wrote in message
news:c5285638.0412030136.955c184@.posting.google.co m...
> hello people!
> I am very new to the SQL World. Currently I work on DB2 and ORACLE and
> this is a new "fruit" for me.
> I have the task to automate the following scenario:
> - backup database A
> - restore database A into database B (overwrite database B)
> - perform the above every night at 23:00.
> Can anyone indicate me where do i find info how to perform the above?
> I know that you can do it through the Enterprise Manager but I need to
> script the above (probably).
> Thanks

Demetris,

whilst I've restored databases I've never scripted it, so i can't help
directly. But, if your objective is to have a second database that is made
available for reporting purposes then have you considered using replication
instead? Replication with Oracle was a pig (it may be better now, I used it
with Oracle 8), but with SQL Server its a doddle. That way you could keep
your database B either almost up to date or you could schedule the
replication to only occur once every night.

Just an idea.

all the best with it,

Brian.

www.cryer.co.uk/brian|||Most operations you perform via Enterprise Manager can be performed directly
using Tranact-SQL scripts. This is handy for scheduling or performing
repetitive tasks.

You can schedule scripts like the examples below using a SQL Agent job. See
the Books Online for reference of the commands used in these scripts.

BACKUP DATABASE DatabaseA
TO DISK='C:\Backups\DatabaseA.bak'
WITH INIT

ALTER DATABASE DatabaseB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE DatabaseB
FROM DISK='C:\Backups\DatabaseA.bak'
WITH
MOVE 'DatabaseA' TO 'C:\DataFiles\DatabaseB.mdf',
MOVE 'DatabaseA_Log' TO 'D:\LogFiles\DatabaseB_Log.ldf',
REPLACE

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Demetris" <DMina@.laiki.com> wrote in message
news:c5285638.0412030136.955c184@.posting.google.co m...
> hello people!
> I am very new to the SQL World. Currently I work on DB2 and ORACLE and
> this is a new "fruit" for me.
> I have the task to automate the following scenario:
> - backup database A
> - restore database A into database B (overwrite database B)
> - perform the above every night at 23:00.
> Can anyone indicate me where do i find info how to perform the above?
> I know that you can do it through the Enterprise Manager but I need to
> script the above (probably).
> Thanks|||Thanks for your reply.
Do you have any links to give me on where to find out about the
replication on SQL Server?

"Brian Cryer" <brianc@.127.0.0.1.activesol.co.uk> wrote in message news:<1102073395.85278.0@.doris.uk.clara.net>...
> "Demetris" <DMina@.laiki.com> wrote in message
> news:c5285638.0412030136.955c184@.posting.google.co m...
> > hello people!
> > I am very new to the SQL World. Currently I work on DB2 and ORACLE and
> > this is a new "fruit" for me.
> > I have the task to automate the following scenario:
> > - backup database A
> > - restore database A into database B (overwrite database B)
> > - perform the above every night at 23:00.
> > Can anyone indicate me where do i find info how to perform the above?
> > I know that you can do it through the Enterprise Manager but I need to
> > script the above (probably).
> > Thanks
> Demetris,
> whilst I've restored databases I've never scripted it, so i can't help
> directly. But, if your objective is to have a second database that is made
> available for reporting purposes then have you considered using replication
> instead? Replication with Oracle was a pig (it may be better now, I used it
> with Oracle 8), but with SQL Server its a doddle. That way you could keep
> your database B either almost up to date or you could schedule the
> replication to only occur once every night.
> Just an idea.
> all the best with it,
> Brian.
> www.cryer.co.uk/brian|||Brian Cryer (brianc@.127.0.0.1.activesol.co.uk) writes:
> whilst I've restored databases I've never scripted it, so i can't help
> directly. But, if your objective is to have a second database that is
> made available for reporting purposes then have you considered using
> replication instead? Replication with Oracle was a pig (it may be better
> now, I used it with Oracle 8), but with SQL Server its a doddle. That
> way you could keep your database B either almost up to date or you could
> schedule the replication to only occur once every night.

I guess that depends on the purpose of this database copying is. If there
is no need to have today's data in the copy database, the backup/restore
strategy is definitely preferrable. While replication may be simpler than
in Oracle, it still adds complexity to your system that is unnecessary if
not wanted.

As Dan demonstrated, using BACKUP/RESTORE commands is a real breeze, and
it can easily be added to an Agent job.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Demetris" <DMina@.laiki.com> wrote in message
news:c5285638.0412030136.955c184@.posting.google.co m...
> hello people!
> I am very new to the SQL World. Currently I work on DB2 and ORACLE and
> this is a new "fruit" for me.
> I have the task to automate the following scenario:
> - backup database A
> - restore database A into database B (overwrite database B)
> - perform the above every night at 23:00.

Google for Log Shipping.

You can do this as a single job.

I have a backup job on Server A that I created with a normal maintenance
plan and then added a second step.

That step is basically exec ServerB.master.dbo.restore_FOO

Restore_FOO looks like

CREATE procedure restore_FOO as

declare @.backup_file as varchar(255)

select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from serverA.msdb.dbo.backupset where database_name='FOO')

print @.backup_file

restore database FOO from disk=@.backup_file with
move 'FOO_Data' to 'e:\sql_data\FOO_data.mdf',
move 'FOO_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'FOO_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace

However, if I could, I'd simply do this as a log-shipping job and ship over
the logs every 1/2 hour or so. That would be far faster and easier. But
given the way the DB is (and it's SQL 7.0) I don't have that choice here.

> Can anyone indicate me where do i find info how to perform the above?
> I know that you can do it through the Enterprise Manager but I need to
> script the above (probably).
> Thanks|||"Demetris" <DMina@.laiki.com> wrote in message
news:c5285638.0412031035.1d3ef489@.posting.google.c om...
> Thanks for your reply.
> Do you have any links to give me on where to find out about the
> replication on SQL Server?

The SQL Server books online (which you should have installed) is a very good
source of information. My own notes on replication are online at
http://www.cryer.co.uk/brian/sqlser...replication.htm, but they are a
little long in the tooth because they were written around SQL Server 7 and
not 2000 (but I doubt much has changed).

Hope this helps,

Brian.

Automate Oracle import to SQL

We are looking at setting up a SQL report server which automates the task of importing Oracle tables. However it seems to be an unknown how to reliablily and automatically import tables from Oracle into SQL. The total size is 500Meg now but will probably grow to a couple of gigs before long. Importing needs to not take too long and would be good if people could still query the data while the update is occuring. So I believe we need to append tables only.

Any suggestions for this noob.

Thanks,Well, the basic approach would be to create a set of stored procedures which would append the SQL tables from the Oracle tables based on simple outer joins. They could be kicked off periodically as a SQL Server Agent job.

DTS (Data Transformation Services) allows you do do the same thing, but gives you a nice GUI for configuring everything. You add a server connection for the Oracle server, a server connection for your SQL Server, and define individual transformation steps for each of your tables. You can customize the source SQL to include only new rows (again, outer joins based on table keys), and transform the data along the way if youd like. Again, the DTS package can be kicked off on a schedule by SQL Server Agent.

I haven't done a lot with DB Replication, but I think it's pretty much SQL to SQL, so I'm not sure how much that would help. The first two approaches would give you everything you are looking for, I would think.
I use the first approach to warehouse data from nine Informix servers every 5 minutes. Works pretty well.

-b|||Well ... the best approach would be to create a linked server and a repository of the tables you will need to transfer. Then you can write a stored procedure to dynamically make queries to transfer the data from oracle to SQL server.

Thursday, March 29, 2012

Automation of Excel to SQL Server 2005

Automation of database restoration

Automation of database restoration

Automation of database restoration

Automating SQL Server Service Pack / Patch / Hot fix apply

Tuesday, March 20, 2012

Automatic synchronization of lookup columns

Wednesday, March 7, 2012

automate sql scripting of jobs in sql agent

automate sql scripting of jobs in sql agent

Automate restore

Automate Oracle import to SQL

Sql Auto grow

Blog Archive

About Me