Sql Auto grow: edition

Showing posts with label edition. 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

Sunday, March 25, 2012

Automatically enable broker after restore or attach

Is there a way to automatically enable Service Broker on SQL Server 2005 Express Edition after a database has been attached or restored?
My initial idea was to check if broker is disabled whenever the client application starts and enable it from there but the problem with this is that the current user might not have the required permissions. So now I'm looking for another way to enable it right after restore/attach if at all possible. Any ideas would be appreciated.

Simplest approach would be to enabled it via a procedure that does have the appropiate permissions (using EXECUTE AS and code signing).

Other possiblity would be to create a server event notification and use an activate procedure in msdb to enable the database:

Code Snippet

use msdb;

create queue [dbevents]

create service [dbevents] on queue [dbevents] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create event notification [dbbackuprestore]

on server for AUDIT_BACKUP_RESTORE_EVENT

to service 'dbevents', 'current database';

Sunday, March 11, 2012

Automatic Field Entry

Hi,

I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:

ID
UserId
Date
Description

(UserId is a foreign key to asp_net_Users as I am supporting user accounts)

Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).

Can anyone point me in the right direction on the steps needed to create this scenario?

Thanks for any help

Daniel

I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.

|||

wtroom:
I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.

To be fully accurate, getDate() returns a datetime datatype value that is set to the current date and time.

It's dangerous to mix in the word "timestamp" into such a discussion because timestamp is also a sql server datatype. A timestamp datatype does NOT contain a date or a time and so cannot be used for this purpose.

|||

Hi,

Thanks for your help. The UserId is coming from the Membership provider in asp.net.

I'm relatively new to ASP.NET and am struggling to implement the getDate() on the SQL side. I have created the SQL Datasource to return the table and have then tried to add a WHERE statement.

I assume that the column needs to Date and the Operator = however I am unsure on the control and parameter properties. I have played around and tried Control and QueryString for the Control property and then added getDate() to the Parameter Property Default Value however this just breaks the Details view which is attached to the SQL Datasource.

Any more help would be greatly appreciated.

Thanks

Daniel

|||thanks for your help but I managed to fix this problem.

for the username I created a label with the Id UserIdValue and deleted the text and turned visible to false.

I then added code for the label onload

 Protected Sub UserIdValue_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles UserIdValue.Load UserIdValue.Text = Membership.GetUser().UserName.ToString() End Sub

This made the label display my username when logged on.

The next part for the detailsview code for iteminserting I added

Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
e.Values("Writtenby") = Membership.GetUser().UserName
e.Values("Date") = DateTime.Now
End Sub

I then deleted Writtenby and Date fields from the details view.

Now when I insert and enter only title and description the Writtenby and Date fields are automatically updated.

Daniel

Automatic deletion to allow import

Greetings All
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won'
t
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:

>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won
't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs wil
l
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:

> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:

>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:

> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:

>Well we have successfully done a copy already and found that if we wanted t
o
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQ
L
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:

> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>

Automatic deletion to allow import

Greetings All
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won't
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs will
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Greetings All
> >
> >I'm new to SQL. I'm using Standard edition (SP3)
> >I have recently had to sort out the import of a Unisys dbs into our SQL
> >server. This dbs needs to be imported a minimum of 3 times per week. It won't
> >overwrite an existing dbs so the only way I can think to do this is by
> >deleting the existing dbs before the scheduled import. I know Unisys can
> >provide a componant that allows synchronisation but as with everything that
> >costs mucho money.
> >
> >Is there a was to delete the existing dbs automatically so that the dbs will
> >be imported through Data Transformation Services, local packages?
> >
> >Or am I, due to my lack of knowledge going about this the wrong way.
> >
> >Andy
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Thanks Roy
> >
> >dbs = database (Sorry for confusing you)
> >
> >The problem is we have at least 150 tables.
> >A coleague found the "Drop" command .. do you know if this could be used?
> >
> >Andy
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Well we have successfully done a copy already and found that if we wanted to
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQL
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:
> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Well we have successfully done a copy already and found that if we wanted to
> >keep the database uptodate we needed to copy 3 times per week (for our
> >purposes) If the database exists it fails to copy. So we had looked at
> >deleting manually but then someone suggested that if we could incorporate
> >this in the import then that would solve it all in one go.
> >
> >Basically I'm just trying to achive a copy of the Unisys database to the SQL
> >server using DTS on a schedual.
> >
> >As for the table definitions I have a colleague who may have the skills
> >required. If we achive this can the import into existing tables be done via
> >DTS?
> >
> >Andy
>

AutoMatic Configuration Differential Backup

hello every one
i am using Microsoft SQL Server 2000
Enterprise Edition and running four data bases on
server.All on Local Areal Network the size of every data
bases is 1 GB and changing occour every Second and minute
so i want to implement Differential Backup after every One
Hours using SQL Server agent and schedule task can any
body guide me properly and any tutorial like web site that
help me step by step ......Documentation etc...
Second i also want replicated to another machine on LAN
thank
IrfanIn SQL Enterprise Manager, select SQL Agent->jobs, New job
Give the job a name and go to steps -> new Step... Choose t-sql step...
The command should be
backup database yourdbname to disk =
'\\servername\sharename\backupfilename.bkp' with init,differential
OK this and add similar steps for each of the other databases.
Then go to schedule and choose a schedule for this to run...
Save it and see if it works...
More details can be found in SQL Server Books On Line...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Irfan" <mirfanaslam@.hotmail.com> wrote in message
news:0ca401c48b8c$9c5f9a90$a301280a@.phx.gbl...
> hello every one
> i am using Microsoft SQL Server 2000
> Enterprise Edition and running four data bases on
> server.All on Local Areal Network the size of every data
> bases is 1 GB and changing occour every Second and minute
> so i want to implement Differential Backup after every One
> Hours using SQL Server agent and schedule task can any
> body guide me properly and any tutorial like web site that
> help me step by step ......Documentation etc...
> Second i also want replicated to another machine on LAN
> thank
> Irfan
>

AutoMatic Configuration Differential Backup

hello every one
i am using Microsoft SQL Server 2000
Enterprise Edition and running four data bases on
server.All on Local Areal Network the size of every data
bases is 1 GB and changing occour every Second and minute
so i want to implement Differential Backup after every One
Hours using SQL Server agent and schedule task can any
body guide me properly and any tutorial like web site that
help me step by step ......Documentation etc...
Second i also want replicated to another machine on LAN
thank
Irfan
In SQL Enterprise Manager, select SQL Agent->jobs, New job
Give the job a name and go to steps -> new Step... Choose t-sql step...
The command should be
backup database yourdbname to disk =
'\\servername\sharename\backupfilename.bkp' with init,differential
OK this and add similar steps for each of the other databases.
Then go to schedule and choose a schedule for this to run...
Save it and see if it works...
More details can be found in SQL Server Books On Line...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Irfan" <mirfanaslam@.hotmail.com> wrote in message
news:0ca401c48b8c$9c5f9a90$a301280a@.phx.gbl...
> hello every one
> i am using Microsoft SQL Server 2000
> Enterprise Edition and running four data bases on
> server.All on Local Areal Network the size of every data
> bases is 1 GB and changing occour every Second and minute
> so i want to implement Differential Backup after every One
> Hours using SQL Server agent and schedule task can any
> body guide me properly and any tutorial like web site that
> help me step by step ......Documentation etc...
> Second i also want replicated to another machine on LAN
> thank
> Irfan
>

AutoMatic Configuration Differential Backup

hello every one
i am using Microsoft SQL Server 2000
Enterprise Edition and running four data bases on
server.All on Local Areal Network the size of every data
bases is 1 GB and changing occour every Second and minute
so i want to implement Differential Backup after every One
Hours using SQL Server agent and schedule task can any
body guide me properly and any tutorial like web site that
help me step by step ......Documentation etc...
Second i also want replicated to another machine on LAN
thank
IrfanIn SQL Enterprise Manager, select SQL Agent->jobs, New job
Give the job a name and go to steps -> new Step... Choose t-sql step...
The command should be
backup database yourdbname to disk ='\\servername\sharename\backupfilename.bkp' with init,differential
OK this and add similar steps for each of the other databases.
Then go to schedule and choose a schedule for this to run...
Save it and see if it works...
More details can be found in SQL Server Books On Line...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Irfan" <mirfanaslam@.hotmail.com> wrote in message
news:0ca401c48b8c$9c5f9a90$a301280a@.phx.gbl...
> hello every one
> i am using Microsoft SQL Server 2000
> Enterprise Edition and running four data bases on
> server.All on Local Areal Network the size of every data
> bases is 1 GB and changing occour every Second and minute
> so i want to implement Differential Backup after every One
> Hours using SQL Server agent and schedule task can any
> body guide me properly and any tutorial like web site that
> help me step by step ......Documentation etc...
> Second i also want replicated to another machine on LAN
> thank
> Irfan
>

Sunday, February 19, 2012

AutoClose option and Personal and MSDE Edition

Hi there,
As per SQL Server's install information the AutoClose option is set to TRUE
for both the Personal and MSDE edition installations. My problem is that any
new databases created (ie snapshots) have the AutoClose option set to TRUE.
How can I change the way this option was set on installation since it's
obviously not paying attention to the model database which has the AutoClose
option set to FALSE.
Any info would be appreciated
thanksrazmanaz wrote:
> Hi there,
> As per SQL Server's install information the AutoClose option is set
> to TRUE for both the Personal and MSDE edition installations. My
> problem is that any new databases created (ie snapshots) have the
> AutoClose option set to TRUE. How can I change the way this option
> was set on installation since it's obviously not paying attention to
> the model database which has the AutoClose option set to FALSE.
> Any info would be appreciated
> thanks
AutoClose and AutoShrink default to True for both the Desktop and MSDE
editions of SQL Server. I think you need to use sp_dboption after
database creation to turn the options off. THe model database cannot
override the default settings. For MSDE, you may want to leave it on to
prevent SQL Server from using too much memory when the database is
inactive, unless this is causing a real performance issue. One way to
prevent this is to keep at least one connection open to the database at
all times while the application is running.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
Thanks for responding.
However I should have mentioned that the AutoClose has to be set to FALSE
for both Personal and MSDE editions. There is no problem for resources so I
do not need to close the databases to save memory/CPU etc.
Also the sp_dboption script "sp_dboption <database>, autoclose, FALSE" won't
work because the snapshots are continually updated and the script cannot run
continually each time a new snapshot is created. This needs to be changed at
the ground floor.
So another question. If I were to install the Standard edition or higher the
install sets the default to FALSE. I just need to know where the low level
setting is so I can change it so that the changes will take effect any
database that is automatically created.
"David Gugick" wrote:
> razmanaz wrote:
> > Hi there,
> >
> > As per SQL Server's install information the AutoClose option is set
> > to TRUE for both the Personal and MSDE edition installations. My
> > problem is that any new databases created (ie snapshots) have the
> > AutoClose option set to TRUE. How can I change the way this option
> > was set on installation since it's obviously not paying attention to
> > the model database which has the AutoClose option set to FALSE.
> >
> > Any info would be appreciated
> > thanks
> AutoClose and AutoShrink default to True for both the Desktop and MSDE
> editions of SQL Server. I think you need to use sp_dboption after
> database creation to turn the options off. THe model database cannot
> override the default settings. For MSDE, you may want to leave it on to
> prevent SQL Server from using too much memory when the database is
> inactive, unless this is causing a real performance issue. One way to
> prevent this is to keep at least one connection open to the database at
> all times while the application is running.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||razmanaz wrote:
> Hi David,
> Thanks for responding.
> However I should have mentioned that the AutoClose has to be set to
> FALSE for both Personal and MSDE editions. There is no problem for
> resources so I do not need to close the databases to save memory/CPU
> etc.
> Also the sp_dboption script "sp_dboption <database>, autoclose,
> FALSE" won't work because the snapshots are continually updated and
> the script cannot run continually each time a new snapshot is
> created. This needs to be changed at the ground floor.
> So another question. If I were to install the Standard edition or
> higher the install sets the default to FALSE. I just need to know
> where the low level setting is so I can change it so that the changes
> will take effect any database that is automatically created.
>
You can't change the default on a Windows 2000/XP installation since all
you get is the Desktop Edition for those OSes AFAIK.
I'm not clear why sp_dboption won't work. What happens when you execute
the command to change the autoclose option.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
The script to set the AutoClose = FALSE works with no problems when I run
it. It's just that databases in my environment get created all the time. I'm
not sitting there waiting for them and running the script on each and every
database that gets created. That's why I was asking if there was a way to
change the default so that I wouldn't have to do that.
"David Gugick" wrote:
> razmanaz wrote:
> > Hi David,
> >
> > Thanks for responding.
> > However I should have mentioned that the AutoClose has to be set to
> > FALSE for both Personal and MSDE editions. There is no problem for
> > resources so I do not need to close the databases to save memory/CPU
> > etc.
> > Also the sp_dboption script "sp_dboption <database>, autoclose,
> > FALSE" won't work because the snapshots are continually updated and
> > the script cannot run continually each time a new snapshot is
> > created. This needs to be changed at the ground floor.
> > So another question. If I were to install the Standard edition or
> > higher the install sets the default to FALSE. I just need to know
> > where the low level setting is so I can change it so that the changes
> > will take effect any database that is automatically created.
> >
> You can't change the default on a Windows 2000/XP installation since all
> you get is the Desktop Edition for those OSes AFAIK.
> I'm not clear why sp_dboption won't work. What happens when you execute
> the command to change the autoclose option.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||If model doesn't cut it, I suggest you create an Agent job that walk the databases where autoclose
is on and for each such database turn it off. Then schedule this to run every day. I'm a bit
surprised that databases are created all the time, though. Doesn't sound like the typical
environment to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"razmanaz" <razmanaz@.discussions.microsoft.com> wrote in message
news:EF1A3B40-5B1D-40F0-8541-5A807631BC65@.microsoft.com...
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I run
> it. It's just that databases in my environment get created all the time. I'm
> not sitting there waiting for them and running the script on each and every
> database that gets created. That's why I was asking if there was a way to
> change the default so that I wouldn't have to do that.
> "David Gugick" wrote:
>> razmanaz wrote:
>> > Hi David,
>> >
>> > Thanks for responding.
>> > However I should have mentioned that the AutoClose has to be set to
>> > FALSE for both Personal and MSDE editions. There is no problem for
>> > resources so I do not need to close the databases to save memory/CPU
>> > etc.
>> > Also the sp_dboption script "sp_dboption <database>, autoclose,
>> > FALSE" won't work because the snapshots are continually updated and
>> > the script cannot run continually each time a new snapshot is
>> > created. This needs to be changed at the ground floor.
>> > So another question. If I were to install the Standard edition or
>> > higher the install sets the default to FALSE. I just need to know
>> > where the low level setting is so I can change it so that the changes
>> > will take effect any database that is automatically created.
>> >
>> You can't change the default on a Windows 2000/XP installation since all
>> you get is the Desktop Edition for those OSes AFAIK.
>> I'm not clear why sp_dboption won't work. What happens when you execute
>> the command to change the autoclose option.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>|||razmanaz wrote:
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I
> run it. It's just that databases in my environment get created all
> the time. I'm not sitting there waiting for them and running the
> script on each and every database that gets created. That's why I was
> asking if there was a way to change the default so that I wouldn't
> have to do that.
Why can't you add the autoclose to the scripts that are used to create
the databases?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

AutoClose option and Personal and MSDE Edition

Hi there,
As per SQL Server's install information the AutoClose option is set to TRUE
for both the Personal and MSDE edition installations. My problem is that any
new databases created (ie snapshots) have the AutoClose option set to TRUE.
How can I change the way this option was set on installation since it's
obviously not paying attention to the model database which has the AutoClose
option set to FALSE.
Any info would be appreciated
thanks
razmanaz wrote:
> Hi there,
> As per SQL Server's install information the AutoClose option is set
> to TRUE for both the Personal and MSDE edition installations. My
> problem is that any new databases created (ie snapshots) have the
> AutoClose option set to TRUE. How can I change the way this option
> was set on installation since it's obviously not paying attention to
> the model database which has the AutoClose option set to FALSE.
> Any info would be appreciated
> thanks
AutoClose and AutoShrink default to True for both the Desktop and MSDE
editions of SQL Server. I think you need to use sp_dboption after
database creation to turn the options off. THe model database cannot
override the default settings. For MSDE, you may want to leave it on to
prevent SQL Server from using too much memory when the database is
inactive, unless this is causing a real performance issue. One way to
prevent this is to keep at least one connection open to the database at
all times while the application is running.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi David,
Thanks for responding.
However I should have mentioned that the AutoClose has to be set to FALSE
for both Personal and MSDE editions. There is no problem for resources so I
do not need to close the databases to save memory/CPU etc.
Also the sp_dboption script "sp_dboption <database>, autoclose, FALSE" won't
work because the snapshots are continually updated and the script cannot run
continually each time a new snapshot is created. This needs to be changed at
the ground floor.
So another question. If I were to install the Standard edition or higher the
install sets the default to FALSE. I just need to know where the low level
setting is so I can change it so that the changes will take effect any
database that is automatically created.
"David Gugick" wrote:

> razmanaz wrote:
> AutoClose and AutoShrink default to True for both the Desktop and MSDE
> editions of SQL Server. I think you need to use sp_dboption after
> database creation to turn the options off. THe model database cannot
> override the default settings. For MSDE, you may want to leave it on to
> prevent SQL Server from using too much memory when the database is
> inactive, unless this is causing a real performance issue. One way to
> prevent this is to keep at least one connection open to the database at
> all times while the application is running.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||razmanaz wrote:
> Hi David,
> Thanks for responding.
> However I should have mentioned that the AutoClose has to be set to
> FALSE for both Personal and MSDE editions. There is no problem for
> resources so I do not need to close the databases to save memory/CPU
> etc.
> Also the sp_dboption script "sp_dboption <database>, autoclose,
> FALSE" won't work because the snapshots are continually updated and
> the script cannot run continually each time a new snapshot is
> created. This needs to be changed at the ground floor.
> So another question. If I were to install the Standard edition or
> higher the install sets the default to FALSE. I just need to know
> where the low level setting is so I can change it so that the changes
> will take effect any database that is automatically created.
>
You can't change the default on a Windows 2000/XP installation since all
you get is the Desktop Edition for those OSes AFAIK.
I'm not clear why sp_dboption won't work. What happens when you execute
the command to change the autoclose option.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi David,
The script to set the AutoClose = FALSE works with no problems when I run
it. It's just that databases in my environment get created all the time. I'm
not sitting there waiting for them and running the script on each and every
database that gets created. That's why I was asking if there was a way to
change the default so that I wouldn't have to do that.
"David Gugick" wrote:

> razmanaz wrote:
> You can't change the default on a Windows 2000/XP installation since all
> you get is the Desktop Edition for those OSes AFAIK.
> I'm not clear why sp_dboption won't work. What happens when you execute
> the command to change the autoclose option.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||If model doesn't cut it, I suggest you create an Agent job that walk the databases where autoclose
is on and for each such database turn it off. Then schedule this to run every day. I'm a bit
surprised that databases are created all the time, though. Doesn't sound like the typical
environment to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"razmanaz" <razmanaz@.discussions.microsoft.com> wrote in message
news:EF1A3B40-5B1D-40F0-8541-5A807631BC65@.microsoft.com...[vbcol=seagreen]
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I run
> it. It's just that databases in my environment get created all the time. I'm
> not sitting there waiting for them and running the script on each and every
> database that gets created. That's why I was asking if there was a way to
> change the default so that I wouldn't have to do that.
> "David Gugick" wrote:
|||razmanaz wrote:
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I
> run it. It's just that databases in my environment get created all
> the time. I'm not sitting there waiting for them and running the
> script on each and every database that gets created. That's why I was
> asking if there was a way to change the default so that I wouldn't
> have to do that.
Why can't you add the autoclose to the scripts that are used to create
the databases?
David Gugick
Quest Software
www.imceda.com
www.quest.com

AutoClose option and Personal and MSDE Edition

Hi there,
As per SQL Server's install information the AutoClose option is set to TRUE
for both the Personal and MSDE edition installations. My problem is that any
new databases created (ie snapshots) have the AutoClose option set to TRUE.
How can I change the way this option was set on installation since it's
obviously not paying attention to the model database which has the AutoClose
option set to FALSE.
Any info would be appreciated
thanksrazmanaz wrote:
> Hi there,
> As per SQL Server's install information the AutoClose option is set
> to TRUE for both the Personal and MSDE edition installations. My
> problem is that any new databases created (ie snapshots) have the
> AutoClose option set to TRUE. How can I change the way this option
> was set on installation since it's obviously not paying attention to
> the model database which has the AutoClose option set to FALSE.
> Any info would be appreciated
> thanks
AutoClose and AutoShrink default to True for both the Desktop and MSDE
editions of SQL Server. I think you need to use sp_dboption after
database creation to turn the options off. THe model database cannot
override the default settings. For MSDE, you may want to leave it on to
prevent SQL Server from using too much memory when the database is
inactive, unless this is causing a real performance issue. One way to
prevent this is to keep at least one connection open to the database at
all times while the application is running.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
Thanks for responding.
However I should have mentioned that the AutoClose has to be set to FALSE
for both Personal and MSDE editions. There is no problem for resources so I
do not need to close the databases to save memory/CPU etc.
Also the sp_dboption script "sp_dboption <database>, autoclose, FALSE" won't
work because the snapshots are continually updated and the script cannot run
continually each time a new snapshot is created. This needs to be changed at
the ground floor.
So another question. If I were to install the Standard edition or higher the
install sets the default to FALSE. I just need to know where the low level
setting is so I can change it so that the changes will take effect any
database that is automatically created.
"David Gugick" wrote:

> razmanaz wrote:
> AutoClose and AutoShrink default to True for both the Desktop and MSDE
> editions of SQL Server. I think you need to use sp_dboption after
> database creation to turn the options off. THe model database cannot
> override the default settings. For MSDE, you may want to leave it on to
> prevent SQL Server from using too much memory when the database is
> inactive, unless this is causing a real performance issue. One way to
> prevent this is to keep at least one connection open to the database at
> all times while the application is running.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||razmanaz wrote:
> Hi David,
> Thanks for responding.
> However I should have mentioned that the AutoClose has to be set to
> FALSE for both Personal and MSDE editions. There is no problem for
> resources so I do not need to close the databases to save memory/CPU
> etc.
> Also the sp_dboption script "sp_dboption <database>, autoclose,
> FALSE" won't work because the snapshots are continually updated and
> the script cannot run continually each time a new snapshot is
> created. This needs to be changed at the ground floor.
> So another question. If I were to install the Standard edition or
> higher the install sets the default to FALSE. I just need to know
> where the low level setting is so I can change it so that the changes
> will take effect any database that is automatically created.
>
You can't change the default on a Windows 2000/XP installation since all
you get is the Desktop Edition for those OSes AFAIK.
I'm not clear why sp_dboption won't work. What happens when you execute
the command to change the autoclose option.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
The script to set the AutoClose = FALSE works with no problems when I run
it. It's just that databases in my environment get created all the time. I'm
not sitting there waiting for them and running the script on each and every
database that gets created. That's why I was asking if there was a way to
change the default so that I wouldn't have to do that.
"David Gugick" wrote:

> razmanaz wrote:
> You can't change the default on a Windows 2000/XP installation since all
> you get is the Desktop Edition for those OSes AFAIK.
> I'm not clear why sp_dboption won't work. What happens when you execute
> the command to change the autoclose option.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||If model doesn't cut it, I suggest you create an Agent job that walk the dat
abases where autoclose
is on and for each such database turn it off. Then schedule this to run ever
y day. I'm a bit
surprised that databases are created all the time, though. Doesn't sound lik
e the typical
environment to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"razmanaz" <razmanaz@.discussions.microsoft.com> wrote in message
news:EF1A3B40-5B1D-40F0-8541-5A807631BC65@.microsoft.com...[vbcol=seagreen]
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I run
> it. It's just that databases in my environment get created all the time. I
'm
> not sitting there waiting for them and running the script on each and ever
y
> database that gets created. That's why I was asking if there was a way to
> change the default so that I wouldn't have to do that.
> "David Gugick" wrote:
>|||razmanaz wrote:
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I
> run it. It's just that databases in my environment get created all
> the time. I'm not sitting there waiting for them and running the
> script on each and every database that gets created. That's why I was
> asking if there was a way to change the default so that I wouldn't
> have to do that.
Why can't you add the autoclose to the scripts that are used to create
the databases?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Thursday, March 29, 2012

Automation of database restoration

Automation of database restoration

Automation of database restoration

Sunday, March 25, 2012

Automatically enable broker after restore or attach

Sunday, March 11, 2012

Automatic Field Entry

Automatic deletion to allow import

Automatic deletion to allow import

AutoMatic Configuration Differential Backup

AutoMatic Configuration Differential Backup

AutoMatic Configuration Differential Backup

Sunday, February 19, 2012

AutoClose option and Personal and MSDE Edition

AutoClose option and Personal and MSDE Edition

AutoClose option and Personal and MSDE Edition

Sql Auto grow

Blog Archive

About Me