Sql Auto grow: automate

Showing posts with label automate. Show all posts

Thursday, March 29, 2012

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 XML from Web to SQL Server

I have a web service, which returns XML to an http request. I need to automate the insert of this XML into SQL server.
I have already designed a procedure which used OPENXML to insert the XML packet, but I need to figure out a way to automate going to the page and retrieving the XML and calling the procedure.
I lack experience on the application development side of SQL Server, and would appriciate any quick & simple ways to perform this. The simpler the better...
Thanks in advance for your ideas.
Michael D.
"sorengi" <sorengi@.discussions.microsoft.com> wrote in message
news:5EEC9F63-7457-4343-A145-5FDCC3615AB3@.microsoft.com...
>I have a web service, which returns XML to an http request. I need to
>automate the insert of this XML into SQL server.
> I have already designed a procedure which used OPENXML to insert the XML
> packet, but I need to figure out a way to automate going to the page and
> retrieving the XML and calling the procedure.
The easiest way to do this would be to create a DTS package in SQL Server
that retrevies the XML and passes it into your procedure. You could then
schedule the package to execute at a specific interval.
See the SQL Server Books Online for information about DTS packages and
scheduling. A simple example of DTS can be found here:
http://sqlxml.org/faqs.aspx?faq=10
Bryant

automating the dumping of databaser in sql server 6.5

I don't happen to have any sql server 6.5 books. I need to know how
to automate the dumping of databases at a preset interval. I can do
it manually and do so each day but it would be nice if it would do it
on its own.
thanksHi,
You can Schedule this using the "Schedule Tasks" available in SQL Server 6.5
Enterprise manager.
THis schedule tasks will be ran by the service "SQL Executive", so ensure
that the service is up all the time.
How to do:-
1. Open enterprise manager
2. COnnect to the server
3. In the menu ,choose Server option and select "Scheduled tasks"
4. Click the first Icon (New Task)
5. Give the name as "Backup" and then select the type as "TSQL".
6. In the command type your "DUMP DATABASE COMMAND"
7. In the schedule option click "Recurring"
8. Click the change command button and select the intervals and days to
schedule
9. CLick ok
Thanks
Hari
MCDBA
<kg7poe@.yahoo.com> wrote in message
news:lcala0t54hin158dlsqtq9bka6k4f5b5ke@.4ax.com...
> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>sql

automating the dumping of databaser in sql server 6.5

I don't happen to have any sql server 6.5 books. I need to know how
to automate the dumping of databases at a preset interval. I can do
it manually and do so each day but it would be nice if it would do it
on its own.
thanks
Hi,
You can Schedule this using the "Schedule Tasks" available in SQL Server 6.5
Enterprise manager.
THis schedule tasks will be ran by the service "SQL Executive", so ensure
that the service is up all the time.
How to do:-
1. Open enterprise manager
2. COnnect to the server
3. In the menu ,choose Server option and select "Scheduled tasks"
4. Click the first Icon (New Task)
5. Give the name as "Backup" and then select the type as "TSQL".
6. In the command type your "DUMP DATABASE COMMAND"
7. In the schedule option click "Recurring"
8. Click the change command button and select the intervals and days to
schedule
9. CLick ok
Thanks
Hari
MCDBA
<kg7poe@.yahoo.com> wrote in message
news:lcala0t54hin158dlsqtq9bka6k4f5b5ke@.4ax.com...
> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>
|||"kg7poe@.yahoo.com" wrote:

> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>

automating the dumping of databaser in sql server 6.5

I don't happen to have any sql server 6.5 books. I need to know how
to automate the dumping of databases at a preset interval. I can do
it manually and do so each day but it would be nice if it would do it
on its own.
thanksHi,
You can Schedule this using the "Schedule Tasks" available in SQL Server 6.5
Enterprise manager.
THis schedule tasks will be ran by the service "SQL Executive", so ensure
that the service is up all the time.
How to do:-
1. Open enterprise manager
2. COnnect to the server
3. In the menu ,choose Server option and select "Scheduled tasks"
4. Click the first Icon (New Task)
5. Give the name as "Backup" and then select the type as "TSQL".
6. In the command type your "DUMP DATABASE COMMAND"
7. In the schedule option click "Recurring"
8. Click the change command button and select the intervals and days to
schedule
9. CLick ok
Thanks
Hari
MCDBA
<kg7poe@.yahoo.com> wrote in message
news:lcala0t54hin158dlsqtq9bka6k4f5b5ke@.
4ax.com...
> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>|||"kg7poe@.yahoo.com" wrote:

> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>

automating taking data from sql server 2000 onto excel 2003 spreasheet

I would like to know recommendations on automate the following:

1. I want to know how to take data from various sql server 2000 data queries and load the data onto excel spreadsheets in an automated method. Some of the data loaded into the excel spreadsheets load detail data and some load data into pivot tables.

Is there any way that the data can be taken from sql server to the excel spreadsheets using odbc connections?

2. Some of the data taken from sql server 2000 database is loaded into pdf files. Is there any way this process can be automated?

Thanks!

You can use macros in Excel to do this task.

Have you checked out Reporting Services? This allows you to export to a number of different formats including PDF.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

automating SSMS reports

Is there a way to automate some of the reports available in Sql Server Mgmt
Studio? I really like several of them, such as the Schema Changes History,
and think they would be helpful from a "compliance" perspective. So, I've
been exporting a few to PDF or Excel on a regular basis... so it's pretty
obvious that the next step would be to have a job that does automatically on
a scheduled basis (Daily, Weekly, whatever).
Any insight on if this doable w/out too many hoops?
-- Margo Noreen
Hello M,
You can download the reports here
http://blogs.msdn.com/sqlrem/archive/2006/08/30/SSMS_Reports_3.aspx
You will need to put them in RS and schedule them
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Is there a way to automate some of the reports available in Sql Server
> Mgmt Studio? I really like several of them, such as the Schema
> Changes History, and think they would be helpful from a "compliance"
> perspective. So, I've been exporting a few to PDF or Excel on a
> regular basis... so it's pretty obvious that the next step would be to
> have a job that does automatically on a scheduled basis (Daily,
> Weekly, whatever).
> Any insight on if this doable w/out too many hoops?
>
|||So that assumes one has Reporting Services setup and configured... which we
do not.
No other way?
-- Margo Noreen
"Simon Sabin" wrote:
[vbcol=seagreen]
> Hello M,
> You can download the reports here
> http://blogs.msdn.com/sqlrem/archive/2006/08/30/SSMS_Reports_3.aspx
> You will need to put them in RS and schedule them
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
|||RS is needed if you want the same cool look you currently have in Management
Studio. If you're just after the data, you can pull the queries into a
sproc, tweak it to output to a persistent table and schedule a job to run it
at whatever frequency you need.
joe.
"M Noreen" <MNoreen@.discussions.microsoft.com> wrote in message
news:3D38CBF0-A6EC-435F-B1EC-309BAEF92BC5@.microsoft.com...[vbcol=seagreen]
> So that assumes one has Reporting Services setup and configured... which
> we
> do not.
> No other way?
> --
> -- Margo Noreen
>
> "Simon Sabin" wrote:
|||We have Reporting Services installed on some servers. Can we run these
reports against any other SQL Servers in our network.
thanks,
ktm
"Joe Yong" wrote:

> RS is needed if you want the same cool look you currently have in Management
> Studio. If you're just after the data, you can pull the queries into a
> sproc, tweak it to output to a persistent table and schedule a job to run it
> at whatever frequency you need.
>
> joe.
> "M Noreen" <MNoreen@.discussions.microsoft.com> wrote in message
> news:3D38CBF0-A6EC-435F-B1EC-309BAEF92BC5@.microsoft.com...
>
>
|||You can run these reports against any SQL Server 2005 instance.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
"ktmd" <ktmd@.discussions.microsoft.com> wrote in message
news:3F316342-FAB9-4E5A-802A-320713FD9E39@.microsoft.com...[vbcol=seagreen]
> We have Reporting Services installed on some servers. Can we run these
> reports against any other SQL Servers in our network.
> thanks,
> ktm
> "Joe Yong" wrote:

Automating SQL Setup.

Is there a way that I can Automate SQL Setup via C#, PERL or VB?Hi

When you install SQLServer one of the Advanced options is to record a setup
script. This will allow you to do unattanded installs. There would be no
pressing reason to use a program to run this unless but you could probably
do it by spawning a process.

John

"KP" <snazystyl@.hotmail.com> wrote in message
news:dc2b0830.0310141453.3d489efb@.posting.google.c om...
> Is there a way that I can Automate SQL Setup via C#, PERL or VB?|||snazystyl@.hotmail.com (KP) wrote in message news:<dc2b0830.0310141453.3d489efb@.posting.google.com>...
> Is there a way that I can Automate SQL Setup via C#, PERL or VB?

See the sections "Performing an Unattended Installation" and
"Installing Desktop Engine" in Books Online, depending on whether you
want to install the full version of MSSQL, or MSDE.

Simon

automating restore

Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jj
jj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>

automating restore

Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>

automating restore

Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>

Automating record selection parameters

Hi,

I need some advise on how to automate record selection on a stored
proc. Here is my situation. I have a stored proc that I used on
Crystal reports with two parameters - Acctcode and Subacct. When a
user enters ' *' on these parameter, it means to report on all
accounts otherwise, report only on specific account.

Here is my select statement with line numbers:

Create proc rb_SubledgerRpt
@.Acctcode varchar(4), @.SubAcct varchar(3)

3 Select AcctCode, SubAcct
4 From GLDetails
5 Where SubAcct <> ' '
6 and AcctCode = @.Acctcode -- for specific acctcode
7 and SubAcct = @.SubAcct -- for specific subacct

8 Go

-- If a user wants to see all Acctcode, and all Subacct, how do I
disable lines 6 and 7?

Thank you in advance for your help.

EdgarHi Edgar,

How about:

Create proc rb_SubledgerRpt
@.Acctcode varchar(4), @.SubAcct varchar(3)
As
Select AcctCode, SubAcct
From GLDetails
Where SubAcct <> ' '
and ((@.AcctCode = '*' ) or (AcctCode = @.Acctcode))
and ((@.SubAcct = '*' ) or (SubAcct = @.SubAcct))

-Dick Christoph
"Edgar" <edgarjtan@.yahoo.com> wrote in message
news:1141058639.691125.36720@.v46g2000cwv.googlegro ups.com...
> Hi,
> I need some advise on how to automate record selection on a stored
> proc. Here is my situation. I have a stored proc that I used on
> Crystal reports with two parameters - Acctcode and Subacct. When a
> user enters ' *' on these parameter, it means to report on all
> accounts otherwise, report only on specific account.
> Here is my select statement with line numbers:
> Create proc rb_SubledgerRpt
> @.Acctcode varchar(4), @.SubAcct varchar(3)
> As
> 3 Select AcctCode, SubAcct
> 4 From GLDetails
> 5 Where SubAcct <> ' '
> 6 and AcctCode = @.Acctcode -- for specific acctcode
> 7 and SubAcct = @.SubAcct -- for specific subacct
> 8 Go
> -- If a user wants to see all Acctcode, and all Subacct, how do I
> disable lines 6 and 7?
> Thank you in advance for your help.
> Edgar|||One way would be this:

Declare @.sQry nvarchar(500)
Declare @.sWhere nvarchar(500)
Set @.sWhere = ' '
set @.AcctCode = 'ABC'
Set @.sQry =
'Select AcctCode, SubAcct ' +
'From GLDetails ' +
'Where SubAcct <> '' '''

If @.AcctCode <> '*'
Set @.sWhere = @.sWhere + 'and AcctCode = ''' + @.Acctcode + ''''

If @.SubAcct <> '*'
Set @.sWhere = @.sWhere + 'and SubAcct = ''' + @.SubAcct + ''''

Set @.sQry = @.sQry + @.sWhere
EXEC sp_executesql @.sQry|||Edgar (edgarjtan@.yahoo.com) writes:
> I need some advise on how to automate record selection on a stored
> proc. Here is my situation. I have a stored proc that I used on
> Crystal reports with two parameters - Acctcode and Subacct. When a
> user enters ' *' on these parameter, it means to report on all
> accounts otherwise, report only on specific account.
> Here is my select statement with line numbers:
> Create proc rb_SubledgerRpt
> @.Acctcode varchar(4), @.SubAcct varchar(3)
> As
> 3 Select AcctCode, SubAcct
> 4 From GLDetails
> 5 Where SubAcct <> ' '
> 6 and AcctCode = @.Acctcode -- for specific acctcode
> 7 and SubAcct = @.SubAcct -- for specific subacct
> 8 Go
> -- If a user wants to see all Acctcode, and all Subacct, how do I
> disable lines 6 and 7?

Dick and Jennifer suggested two methods. Dick's method is cleaner, but
Jennifer's solution can give better performance. If there are no
indexes on AcctCode or SubAcct (or the table is small), then there is
on performance issue. But if there are indexes, it is not likely that
Dick's solution will make use of them.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hmmmm.

I guess I don't understand your comments Mr. Sommarskog. Dick's
solution to me is better. It is simpler, and doesn't use dynamic SQL.

Further, I've had the best success with GL's using a composite index on
Account+Subaccount.
If you don't know the account, you will end up doing a table scan
pretty much no matter what. If you know the account and the subaccount,
retrievals can be VERY fast.

Finally, because Dick's solution doesn't use dynamic SQL, the optimizer
can key in on the best way to do the search relatively easily.

I look forward to hearing why you don't think the optimizer would
indexes. They have in my experience.

Thank you,
Doug|||Doug (drmiller100@.hotmail.com) writes:
> I guess I don't understand your comments Mr. Sommarskog. Dick's
> solution to me is better. It is simpler, and doesn't use dynamic SQL.
> Further, I've had the best success with GL's using a composite index on
> Account+Subaccount.
> If you don't know the account, you will end up doing a table scan
> pretty much no matter what. If you know the account and the subaccount,
> retrievals can be VERY fast.

I will have admit that I did not consider the particular business problem,
but more considered the general case. Let's say that you have something
like:

SELECT ...
FROM tbl
WHERE (indexedcol1 = @.vall OR @.val1 = '*')
AND (indexedcol2 = @.val2 OR @.val2 = '*')
AND (nonindexedcol = @.val3 OR @.val3 = '*')

This will most certainly table scan, even if @.val1 has a distinct value and
@.val3 is '*', because when the optimizer builds the plan it has no
knowledge what value the parameter will have. It can sniff parameter
values, but it cannot build plans that produce wrong results. So it must
be a table scan. (The exception in SQL 2005 is when you add the query hint
OPTION (RECOMPILE).)

> Finally, because Dick's solution doesn't use dynamic SQL, the optimizer
> can key in on the best way to do the search relatively easily.

If you build a dynamic SQL string and include only the the search
parameters that were actually given, you will give the optimizer the
exact right amount of information to work with. If you only supply
@.val1, the optimizer will use that index (if it is selective enough).
If you only supply @.val2, it will use thar index, and if you supply
only @.val3, the query will table scan.

For a longer discsussion on the topic, see my article
http://www.sommarskog.se/dyn-search.html.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello,

Thanks for your comments.

An interesting subtlty is involved here. I wonder if the difference is
significant.
Dick's code had and ((@.AcctCode = '*' ) or (AcctCode = @.Acctcode))

your code had
(indexedcol1 = @.vall OR @.val1 = '*')

this sounds weird, but I can easily see where your code would require a
table scan, yet Dick's code could be done without one.
Does this make any sense to anyone else?
I don't know how to explain my logic.

automating profiler

I have created a stored proc that will automate the capture of traces. The trace is captured to a file instead of a table because tracing to a table on the same server has caused performance issues.
The problem is that I really want the trace in a table so that we can produce reports with Reporting Services. I know that I can manually save the trace to a trace table. But is there a way to automate this process? I can't find any command line parameter
s for Profiler.
Sure, take a look at fn_trace_gettable in BOL. By the way you almost never
want to trace directly to a table if you care about performance.
Andrew J. Kelly SQL MVP
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:75FFDF10-B0AA-4AD4-8573-0A241F630717@.microsoft.com...
> I have created a stored proc that will automate the capture of traces. The
trace is captured to a file instead of a table because tracing to a table on
the same server has caused performance issues.
> The problem is that I really want the trace in a table so that we can
produce reports with Reporting Services. I know that I can manually save the
trace to a trace table. But is there a way to automate this process? I can't
find any command line parameters for Profiler.
>
>
|||Is there a SQL 7.0 soluthion?
"Andrew J. Kelly" wrote:

> Sure, take a look at fn_trace_gettable in BOL. By the way you almost never
> want to trace directly to a table if you care about performance.
> --
> Andrew J. Kelly SQL MVP
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:75FFDF10-B0AA-4AD4-8573-0A241F630717@.microsoft.com...
> trace is captured to a file instead of a table because tracing to a table on
> the same server has caused performance issues.
> produce reports with Reporting Services. I know that I can manually save the
> trace to a trace table. But is there a way to automate this process? I can't
> find any command line parameters for Profiler.
>
>
|||Not that I am aware of.
Andrew J. Kelly SQL MVP
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...[vbcol=seagreen]
> Is there a SQL 7.0 soluthion?
> "Andrew J. Kelly" wrote:
never[vbcol=seagreen]
The[vbcol=seagreen]
table on[vbcol=seagreen]
the[vbcol=seagreen]
can't[vbcol=seagreen]
|||Do you know anything about the xp_trace_opentracefile stored proc in 7.0? Is it possible that this will do the same thing as fn_trace_gettable in 2000?
"Andrew J. Kelly" wrote:

> Not that I am aware of.
> --
> Andrew J. Kelly SQL MVP
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...
> never
> The
> table on
> the
> can't
>
>
|||Do you know anything about the extended stored proc called xp_trace_opentracefile?
Will this do the same thing as fn_trace_gettable?
"Andrew J. Kelly" wrote:

> Not that I am aware of.
> --
> Andrew J. Kelly SQL MVP
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...
> never
> The
> table on
> the
> can't
>
>
|||I am not familiar with the xp and don't have 7.0 anymore.
Andrew J. Kelly SQL MVP
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:5302ACCD-993C-4CD6-914A-0B2FEC8B8203@.microsoft.com...
> Do you know anything about the extended stored proc called
xp_trace_opentracefile?[vbcol=seagreen]
> Will this do the same thing as fn_trace_gettable?
> "Andrew J. Kelly" wrote:
almost[vbcol=seagreen]
traces.[vbcol=seagreen]
can[vbcol=seagreen]
save[vbcol=seagreen]
I[vbcol=seagreen]
|||The SQL Server 7.0 resource kit has a COM+ object that will allow you to
read a SQL Server 7.0 formatted trace. I don't have sample code handy but it
wasn't hard to figure out how to import the trace using some simple VB
code...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...[vbcol=seagreen]
> Is there a SQL 7.0 soluthion?
> "Andrew J. Kelly" wrote:
never[vbcol=seagreen]
The[vbcol=seagreen]
table on[vbcol=seagreen]
the[vbcol=seagreen]
can't[vbcol=seagreen]
|||Thank you, I'll give it a try.
"Brian Moran" wrote:

> The SQL Server 7.0 resource kit has a COM+ object that will allow you to
> read a SQL Server 7.0 formatted trace. I don't have sample code handy but it
> wasn't hard to figure out how to import the trace using some simple VB
> code...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...
> never
> The
> table on
> the
> can't
>
>
|||I can't find the resource kit on the original 7.0 disks. The only sql server resource kit that I find under the MSDN subscriber downloads is for sql server 2000. Is there another place that I need to look?
"Brian Moran" wrote:

> The SQL Server 7.0 resource kit has a COM+ object that will allow you to
> read a SQL Server 7.0 formatted trace. I don't have sample code handy but it
> wasn't hard to figure out how to import the trace using some simple VB
> code...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:DC0B3779-1875-40D4-848B-31473782A038@.microsoft.com...
> never
> The
> table on
> the
> can't
>
>

Automating installation of ODBC data source

Is there a script anyone has that will automate the addition of an
access database to the OBDC datasources in control panel.

ThanksHere's something that I did in Kixtart for our environment. It was last
November, so I don't recall all the details. Some key values I would expect to
vary, as I have six (6) scripts for all the data sources we have. I figured out
what to do by checking the registry, and creating a script that would duplicate
what I had done manually.

Also, since it was three (3) PCs ago, I don't recall what OS it was. Since I've
been here, I've had three (3) PCs. This one is XP, the previous was w2k and the
first? MAY have been NT. I mention this as I don't know if it'll require any
changes in the script.

Here it is (between the lines of '=========='):
==========
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; ;;;;;;;;;;;;;;;;;;;;
; Kixtart script - create Data Source Entries for HTE applications ;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; ;;;;;;;;;;;;;;;;;;;;
; ;
; 11-10-03 tcs initial creation date. ;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; ;;;;;;;;;;;;;;;;;;;;
;
; *** NOTE ***
;
; Path to 'cwbodbc.dll' must be modified as necessary. For example,
; as originally written, the path is:
;
; d:\IBM\Client Access\Shared\cwbodbc.dll
;
; which was correct for the machine upon which this script was developed.
;
;================================================= ====================
;
;Setup ODBC for HTE DB2 database [Begin]
;Create the necessary keys, values and expressions for current user.
$CR=chr(13)
$HteDSN='Finance'
$KeyBase='HKEY_CURRENT_USER\Software\ODBC\ODBC.INI '
$Key1='$KeyBase\ODBC Data Sources'
$Key2='$KeyBase\$HteDSN'
if existkey($Key2) ; key doesn't exist, so create it.
$result=addkey($Key2)
$result=writevalue($Key1,'$HteDSN','Client Access ODBC Driver
(32-bit)','REG_SZ')

$result=writevalue($Key2,'AllowDataCompression','0 ','REG_SZ')
$result=writevalue($Key2,'AllowUnsupportedChar','0 ','REG_SZ')
$result=writevalue($Key2,'AlwaysScrollable','0','R EG_SZ')
$result=writevalue($Key2,'BlockSizeKB','32','REG_S Z')
$result=writevalue($Key2,'CCSID','','REG_SZ')
$result=writevalue($Key2,'CommitMode','0','REG_SZ' )
$result=writevalue($Key2,'ConnectionType','0','REG _SZ')
$result=writevalue($Key2,'DateFormat','5','REG_SZ' )
$result=writevalue($Key2,'DateSeparator','1','REG_ SZ')
$result=writevalue($Key2,'Decimal','0','REG_SZ')
$result=writevalue($Key2,'DefaultLibraries','atlib , prlib, pilib, gmlib,
fmlib, mrlib, crlib, gflib, falib, wflib, lxlib, malib','REG_SZ')
$result=writevalue($Key2,'DefaultPkgLibrary','QGPL ','REG_SZ')
$result=writevalue($Key2,'Description','Client Access Express ODBC data
source','REG_SZ')
$result=writevalue($Key2,'Driver','d:\IBM\Client
Access\Shared\cwbodbc.dll','REG_SZ')
$result=writevalue($Key2,'ExtendedDynamic','0','RE G_SZ')
$result=writevalue($Key2,'ForceTranslation','0','R EG_SZ')
$result=writevalue($Key2,'LanguageID','ENU','REG_S Z')
$result=writevalue($Key2,'LazyClose','0','REG_SZ')
$result=writevalue($Key2,'LibraryView','0','REG_SZ ')
$result=writevalue($Key2,'ManagedDataSource','0',' REG_SZ')
$result=writevalue($Key2,'MaxFieldLength','32','RE G_SZ')
$result=writevalue($Key2,'Naming','0','REG_SZ')
$result=writevalue($Key2,'ODBCRemarks','0','REG_SZ ')
$result=writevalue($Key2,'PreFetch','0','REG_SZ')
$result=writevalue($Key2,'RecordBlocking','2','REG _SZ')
$result=writevalue($Key2,'SearchPattern','1','REG_ SZ')
$result=writevalue($Key2,'Signon','0','REG_SZ')
$result=writevalue($Key2,'SortSequence','0','REG_S Z')
$result=writevalue($Key2,'SortTable','','REG_SZ')
$result=writevalue($Key2,'SortWeight','0','REG_SZ' )
$result=writevalue($Key2,'SSL','2','REG_SZ')
$result=writevalue($Key2,'System','10.4.1.1','REG_ SZ')
$result=writevalue($Key2,'TimeFormat','0','REG_SZ' )
$result=writevalue($Key2,'TimeSeparator','0','REG_ SZ')
$result=writevalue($Key2,'TranslationDLL','','REG_ SZ')
$result=writevalue($Key2,'TranslationOption','','R EG_SZ')
$result=writevalue($Key2,'','','REG_SZ')
$result=writevalue($Key2,'','','REG_SZ')
$result=writevalue($Key2,'','','REG_SZ')
$result=writevalue($Key2,'','','REG_SZ')
endif
;Setup ODBC for HTE DB2 Server [End]
==========
I don't think that I fully finished, so I'd tread lightly, but hopefully this'll
get you started.

Tom

On Wed, 23 Jul 2003 10:49:53 +0000 (UTC), SkunkDave
<dave_casserly@.totalise.co.uk> wrote:

>Is there a script anyone has that will automate the addition of an
>access database to the OBDC datasources in control panel.
>Thanks

Automating Generate Script in Command Line

Hello,
Is there a way to automate Generate Script in 2005, so by clicking a link it
generates script which is using a command line and not using Management
Studio Tasks->Generate Script ?
--
MikeMike
There are a new objects library SQL SMO (.NET) to deal with such reports.
Previously we used ( and if you still have SQL Server 2000) SQL DMO object
library
"Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
news:3F3E26B8-C60A-4E37-A7DC-87BA9A515A1D@.microsoft.com...
> Hello,
> Is there a way to automate Generate Script in 2005, so by clicking a link
> it
> generates script which is using a command line and not using Management
> Studio Tasks->Generate Script ?
> --
> Mike|||What do you want to script ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||SQl Server 2005 database.
--
Mike
"Jens" wrote:

> What do you want to script ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Automating File Deployment of SSIS Packages

Hi:

I am trying to utilize DTUtil command to automate deployment of SSIS Package from the Build Server to Dev Server. I am getting an invalid option error, when I try to run the following command:

dtutil /FILE C:\Program Files\ABC\ABC ABC CAD\ETL Packages\Load_Staging_FromWCF.dtsx /MOVE FILE;\\serverA\C$\Program Files\ABC\ABC ABC CAD\ETL Packages\Load_Staging_FromWCF.dtsx /QUIET

Currently I have a .CMD file, in which I have the above command, and I am trying to run the CMD file and it returns an error. Can someone help me the DTUtil switches. My goal is to move a DTSX file from Server A(Build Server) to Server B(Dev Server). Also is there a switch to specify the server name, if UNC path is not supported. Also I am using File System Deployment, and running the file as an Administrator. I have admin privs on both Build and Dev Server. Thanks and I appreciate some help on this.

Why not just copy the package files over there using regular COPY commands?|||

That is true, because I am not actually utilizing SQL Server Deployment switches, although DTUtil does allow moving package files, just not sure what switches to apply. Thanks.

Automating db Backups

Is there a way to automate (schedule) backups of the databases in SQL Express? Similar to Maintenance Plans in SQL2000....Hi Mark McFarlane,

If you come accross anything reagrding automating a schedule backup,Pls let me know,
Have been on the look out for this some time now.
Will do likewise.
email : papali4@.hotmail.com
Tnx

|||

hi,

not directly as SQL Server Agent is not provided, but you can workarond that using the OS provided native scheduler (AT or SCHTASKS)..

you can write down a cmd file like

<backup.cmd>

REM scheduled backup

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup executions started at - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'backup database [db_name]'; PRINT '';" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"BACKUP DATABASE [db_name] TO DISK = N'D:\BackupFolder\db_name.Bak' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup terminated at - ' + CONVERT(varchar, GETDATE());" >>d:\YourCheckFolder\ScheduledBCK.txt
</backup.cmd>

then you can schedule it as desired... the script will backup the db as required and will output the result of the task to a text file, d:\YourCheckFolder\ScheduledBCK.txt, you can later review to verify the performed operation...

in my own scenarios, I do add another "features".. I wrote a CLR assmbly exporting a stored procedure to "mimic" database mail feature (not present in SQLExpress) so that the "d:\YourCheckFolder\ScheduledBCK.txt" will be automatically sent to a defined list of recipients (sysadmins, dba or myself as well).. the component (amDBObj) is free and can be downloaded from http://www.asql.biz/en/Download2005.aspx .. feedback is apprecieted

the resulting script can be modified adding the

<add this>

REM adding SMTP mail to sysadmins, dba, etc of the backup operation result..

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing backup result of - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK-mailing.txt

SqlCmd -E -S(Local) -Q"SET NOCOUNT ON; DECLARE @.ret int;EXEC @.ret = [db_hosting_the_CLR_assembly].[dbo].[amSMTPmail] @.Server = N'your_mail_server', @.Sender = N'the_SQLsender@.sender.com', @.AddressesTO = N'me@.me.com', @.AddressesCC = N'further_recipients@.domain.com', @.AddressesCCN = NULL, @.AttachFiles = N'd:\YourCheckFolder\ScheduledBCK.txt', @.Subject = N'Backup performed', @.MessageBody = N'Backup performed'; SELECT @.ret AS [Execution result];" >>d:\YourCheckFolder\ScheduledBCK-mailing.txt

</add this>

or whatever required change to the original cmd file...

regards

|||

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs. Also, what about SSIS in express edition. Do have to install them seperately. Thanks

|||

UMAR DAR wrote:

hi Andrea Montanari ,
That was very informative. Is there any other way to run and schedule jobs.

hy, perhaps you can have a look at a great artice (and tool) by Jasper Smith, SQL Server MVP, at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29, based on WinNT native scheduler as well... but all these are not SQL Server jobs, as SQLExpress does not provide SQL Server Agent...

UMAR DAR wrote:
Also, what about SSIS in express edition. Do have to install them seperately. Thanks

SSIS are not available as well, in SQLEpress edition.. http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

|||

I have a similar solution posted here that's not incredibly elegant but it works just fine. It goes through and backs up all databases in a given instance and there is an included batch file to schedule it. The video deployment instructions can be seen here:

http://www.jumpstarttv.com/Media.aspx?vid=30

or written instructions here:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

-- Brian

Tuesday, March 27, 2012

Automating db Backups

Is there a way to automate (schedule) backups of the databases in SQL Express? Similar to Maintenance Plans in SQL2000....Hi Mark McFarlane,

If you come accross anything reagrding automating a schedule backup,Pls let me know,
Have been on the look out for this some time now.
Will do likewise.
email : papali4@.hotmail.com
Tnx

|||

hi,

not directly as SQL Server Agent is not provided, but you can workarond that using the OS provided native scheduler (AT or SCHTASKS)..

you can write down a cmd file like

<backup.cmd>

REM scheduled backup

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup executions started at - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'backup database [db_name]'; PRINT '';" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup terminated at - ' + CONVERT(varchar, GETDATE());" >>d:\YourCheckFolder\ScheduledBCK.txt
</backup.cmd>

the resulting script can be modified adding the

<add this>

REM adding SMTP mail to sysadmins, dba, etc of the backup operation result..

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing backup result of - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK-mailing.txt

</add this>

or whatever required change to the original cmd file...

regards

|||

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs. Also, what about SSIS in express edition. Do have to install them seperately. Thanks

|||

UMAR DAR wrote:

hi Andrea Montanari ,
That was very informative. Is there any other way to run and schedule jobs.

UMAR DAR wrote:
Also, what about SSIS in express edition. Do have to install them seperately. Thanks

SSIS are not available as well, in SQLEpress edition.. http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

|||

http://www.jumpstarttv.com/Media.aspx?vid=30

or written instructions here:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

-- Brian

Thursday, March 29, 2012

Automation of database restoration

Automation of database restoration

Automation of database restoration

Automating XML from Web to SQL Server

automating the dumping of databaser in sql server 6.5

automating the dumping of databaser in sql server 6.5

automating the dumping of databaser in sql server 6.5

automating taking data from sql server 2000 onto excel 2003 spreasheet

automating SSMS reports

Automating SQL Setup.

automating restore

automating restore

automating restore

Automating record selection parameters

automating profiler

Automating installation of ODBC data source

Automating Generate Script in Command Line

Automating File Deployment of SSIS Packages

Automating db Backups

Tuesday, March 27, 2012

Automating db Backups

Sql Auto grow

Blog Archive

About Me