Currently developing a small app (Access) I'm using the same app on 3 SQL
Databases:
1 - Production: MS SQL 2000 server
2 - Test: MS SQL 2000 server
3 - Test: MSDE
As I'm modifyng extensively db structure I have to manually move modified
dbs between the 3 servers daily. multiple times, and got bored to manually
select each time paths and options (overwrite, force restore, devices etc)
So I wrote a small script in QA and thought if possible to runnit from
commandline (OSQL would do the job?). But I still miss a statement to
specifiy to wich DB the script must connect before doing th rest:
Here's my code and I've got a trutsed connection, so y would run:
osql -q "Restore.sql" -E:
The script contains:
USE MASTER
GO
RESTORE DATABASE Test
FROM DISK = 'C:\test.BAK'
WITH REPLACE,
MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
What am I missing to connect to the prper server?
Thanks
You first say:
> But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
And then:
> What am I missing to connect to the proper server?
Which one is it? Database or server? To specify a server, you use the -S switch for OSQL. The db is a bit more
problematic as it is included in the RESTORE command, but my guess is that it is server you are after.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atlas" <atlaspeak@.my-deja.com> wrote in message news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||osql -S [servername] ?
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||Hi,
OSQL -S Server_name -E -i c:\Restore.sql -oc:\restore.log
Note: -q option is to give the query in the OSQL itself. Use iption "i" to
input a file.
Thanks
Hari
MCDBA
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Automatically run query plan.
Is there a way to tell the SQL Server 2005 to automatically generate
query plan for certain stored procedure (as they run on the production
server)? (I know it's a bad idea, but still).
Thanks.Can you elaborate? A plan *is* created when you execute the procedure. You can't create a plan
without executing the procedure since the plan is dependent on a number of things like environment
settings (like SET) and also parameter values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:OQ$48kjyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is there a way to tell the SQL Server 2005 to automatically generate query plan for certain stored
> procedure (as they run on the production server)? (I know it's a bad idea, but still).
> Thanks.|||Frank,
Do you mean that you want to compile the stored procedure as soon as it
is created/deployed?
In that case, look up SET NOEXEC in BOL. You could set NOEXEC on and
call the SP.
HTH,
Gert-Jan
Frank Rizzo wrote:
> Is there a way to tell the SQL Server 2005 to automatically generate
> query plan for certain stored procedure (as they run on the production
> server)? (I know it's a bad idea, but still).
> Thanks.|||Tibor Karaszi wrote:
> Can you elaborate? A plan *is* created when you execute the procedure.
> You can't create a plan without executing the procedure since the plan
> is dependent on a number of things like environment settings (like SET)
> and also parameter values.
Understood. However, after the proc is ran, I'd love to have SQL Server
save somewhere that execution plan that it ran the proc with.|||On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
> Understood. However, after the proc is ran, I'd love to have SQL Server
> save somewhere that execution plan that it ran the proc with.
SQL 2005 profiler can capture this information. You can also attach plan to
a proc call to have SQL server use your preferred plan if you have tested
over and over and can't get SQL server to pick the plan you want. (I would
submit to http://connect.microsoft.com as they like to hear about those
kind of issues.
-Chuck Lathrope
www.sqlwebpedia.com|||SQL Server 2005 automatically captures this information and the plan is
available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
all, as when you create or exec the proc WITH RECOMPILE). You don't need
profiler.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:c0ajevo59mxn.na0e1n0vrpyc$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
>> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
>> Understood. However, after the proc is ran, I'd love to have SQL Server
>> save somewhere that execution plan that it ran the proc with.
> SQL 2005 profiler can capture this information. You can also attach plan
> to
> a proc call to have SQL server use your preferred plan if you have tested
> over and over and can't get SQL server to pick the plan you want. (I would
> submit to http://connect.microsoft.com as they like to hear about those
> kind of issues.
> -Chuck Lathrope
> www.sqlwebpedia.com|||Yes, as Tibor and Kalen said, the plan is created automatically when you
executed your procedure, it is stored in a cache of SQL Server and you can
query it from sys.dm_exec_cached_plans.
In addition, sometimes you may want to have your query execute with a
specific execution plan. In this case, you can save the execution plan in
XML format and then force your query to use the query plan by using "USE
PLAN".
For more information, please refer to:
How to: Save an Execution Plan in XML Format
http://msdn2.microsoft.com/en-us/library/ms190646.aspx
Forcing Query Plans
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
Hope this helps. Please feel free to let us know if you have any other
questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Kalen Delaney wrote:
> SQL Server 2005 automatically captures this information and the plan is
> available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
> all, as when you create or exec the proc WITH RECOMPILE). You don't need
> profiler.
Thank you. How do I now derive the actual plan from this view?
I'd like to get it into XML format so that I can open the execution plan
in SQL Server Management Studio (for inspection).|||You can a column called "plan_handle". You feed the value from this into the function
sys.dm_exec_query_plan(). You can also use CROSS APPLY:
SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:%23KV37%23xyHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Kalen Delaney wrote:
>> SQL Server 2005 automatically captures this information and the plan is available in
>> sys.dm_exec_cached_plans. (Unless the plan is never cached at all, as when you create or exec the
>> proc WITH RECOMPILE). You don't need profiler.
> Thank you. How do I now derive the actual plan from this view?
> I'd like to get it into XML format so that I can open the execution plan in SQL Server Management
> Studio (for inspection).
>|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let us know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql
query plan for certain stored procedure (as they run on the production
server)? (I know it's a bad idea, but still).
Thanks.Can you elaborate? A plan *is* created when you execute the procedure. You can't create a plan
without executing the procedure since the plan is dependent on a number of things like environment
settings (like SET) and also parameter values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:OQ$48kjyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is there a way to tell the SQL Server 2005 to automatically generate query plan for certain stored
> procedure (as they run on the production server)? (I know it's a bad idea, but still).
> Thanks.|||Frank,
Do you mean that you want to compile the stored procedure as soon as it
is created/deployed?
In that case, look up SET NOEXEC in BOL. You could set NOEXEC on and
call the SP.
HTH,
Gert-Jan
Frank Rizzo wrote:
> Is there a way to tell the SQL Server 2005 to automatically generate
> query plan for certain stored procedure (as they run on the production
> server)? (I know it's a bad idea, but still).
> Thanks.|||Tibor Karaszi wrote:
> Can you elaborate? A plan *is* created when you execute the procedure.
> You can't create a plan without executing the procedure since the plan
> is dependent on a number of things like environment settings (like SET)
> and also parameter values.
Understood. However, after the proc is ran, I'd love to have SQL Server
save somewhere that execution plan that it ran the proc with.|||On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
> Understood. However, after the proc is ran, I'd love to have SQL Server
> save somewhere that execution plan that it ran the proc with.
SQL 2005 profiler can capture this information. You can also attach plan to
a proc call to have SQL server use your preferred plan if you have tested
over and over and can't get SQL server to pick the plan you want. (I would
submit to http://connect.microsoft.com as they like to hear about those
kind of issues.
-Chuck Lathrope
www.sqlwebpedia.com|||SQL Server 2005 automatically captures this information and the plan is
available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
all, as when you create or exec the proc WITH RECOMPILE). You don't need
profiler.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:c0ajevo59mxn.na0e1n0vrpyc$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
>> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
>> Understood. However, after the proc is ran, I'd love to have SQL Server
>> save somewhere that execution plan that it ran the proc with.
> SQL 2005 profiler can capture this information. You can also attach plan
> to
> a proc call to have SQL server use your preferred plan if you have tested
> over and over and can't get SQL server to pick the plan you want. (I would
> submit to http://connect.microsoft.com as they like to hear about those
> kind of issues.
> -Chuck Lathrope
> www.sqlwebpedia.com|||Yes, as Tibor and Kalen said, the plan is created automatically when you
executed your procedure, it is stored in a cache of SQL Server and you can
query it from sys.dm_exec_cached_plans.
In addition, sometimes you may want to have your query execute with a
specific execution plan. In this case, you can save the execution plan in
XML format and then force your query to use the query plan by using "USE
PLAN".
For more information, please refer to:
How to: Save an Execution Plan in XML Format
http://msdn2.microsoft.com/en-us/library/ms190646.aspx
Forcing Query Plans
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
Hope this helps. Please feel free to let us know if you have any other
questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Kalen Delaney wrote:
> SQL Server 2005 automatically captures this information and the plan is
> available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
> all, as when you create or exec the proc WITH RECOMPILE). You don't need
> profiler.
Thank you. How do I now derive the actual plan from this view?
I'd like to get it into XML format so that I can open the execution plan
in SQL Server Management Studio (for inspection).|||You can a column called "plan_handle". You feed the value from this into the function
sys.dm_exec_query_plan(). You can also use CROSS APPLY:
SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:%23KV37%23xyHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Kalen Delaney wrote:
>> SQL Server 2005 automatically captures this information and the plan is available in
>> sys.dm_exec_cached_plans. (Unless the plan is never cached at all, as when you create or exec the
>> proc WITH RECOMPILE). You don't need profiler.
> Thank you. How do I now derive the actual plan from this view?
> I'd like to get it into XML format so that I can open the execution plan in SQL Server Management
> Studio (for inspection).
>|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let us know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql
Wednesday, March 7, 2012
Automate Database Project
We use seperate database instances for development, qa, ua, and production with multiple databases in each environment. So, I'm looking for a way to automate the database changes to these different environments.
After creating a database project with change scripts and database references, I can't seem to find a way to automate these changes out to a server/environment.
We're currently using SQL 2000 + 2005, Visual Studio 2003 + 2005, and Visual Source Safe 6 (researching Team Foundation System).
Any ideas/thoughts?
If I am reading your message correctly you already have a change script that you want to apply to your target systems and do this in an automated fashion? One possible solution is to deploy the script using SQLCMD. If I did not read this correctly, and you're looking for the capability to generate a change script, then you would need to shop for a 3rf party tool as we do not have this capability.
Automate Database Project
We use seperate database instances for development, qa, ua, and production with multiple databases in each environment. So, I'm looking for a way to automate the database changes to these different environments.
After creating a database project with change scripts and database references, I can't seem to find a way to automate these changes out to a server/environment.
We're currently using SQL 2000 + 2005, Visual Studio 2003 + 2005, and Visual Source Safe 6 (researching Team Foundation System).
Any ideas/thoughts?
If I am reading your message correctly you already have a change script that you want to apply to your target systems and do this in an automated fashion? One possible solution is to deploy the script using SQLCMD. If I did not read this correctly, and you're looking for the capability to generate a change script, then you would need to shop for a 3rf party tool as we do not have this capability.
automate creation of staging database
any ideas on how to automate the production of a staging db with day old
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the mo
st recent database
backup from the database. Save it in a variable, and use that to construct y
our RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the mo
st recent database
backup from the database. Save it in a variable, and use that to construct y
our RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
automate creation of staging database
any ideas on how to automate the production of a staging db with day old
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thx
Are you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.
|||How about doing some SELECT from the backup history tables to pick up the most recent database
backup from the database. Save it in a variable, and use that to construct your RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thx
Are you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.
|||How about doing some SELECT from the backup history tables to pick up the most recent database
backup from the database. Save it in a variable, and use that to construct your RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
automate creation of staging database
any ideas on how to automate the production of a staging db with day old
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the most recent database
backup from the database. Save it in a variable, and use that to construct your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>> RESTORE DATABASE TargetDB
>> FROM DISK = 'C:\Backups\SourceDB.bak'
>> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
>> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
>> See the Books Online for BACKUP/RESTORE details.
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the most recent database
backup from the database. Save it in a variable, and use that to construct your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>> RESTORE DATABASE TargetDB
>> FROM DISK = 'C:\Backups\SourceDB.bak'
>> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
>> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
>> See the Books Online for BACKUP/RESTORE details.
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
Friday, February 24, 2012
Autogrow problem
We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow for
up to five hours. The last time it occured, the database only needed to grow
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and the
server was not at capacity. Any ideas?
Thank You.Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow for
up to five hours. The last time it occured, the database only needed to grow
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and the
server was not at capacity. Any ideas?
Thank You.Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.
Sunday, February 19, 2012
Auto Update Stats
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.Is this the one you are referring to?
http://support.microsoft.com/defaul...b;EN-US;q195565
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"GoBoy" <anonymous@.discussions.microsoft.com> wrote in message
news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.|||Great article but that's not it. This article dealt more
with how Sql Server "round robins" through the databases
and the timing of that, etc.
>--Original Message--
>Is this the one you are referring to?
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q195565
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"GoBoy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
>I have the above set on all my databases on one of our
>2000 production servers. However, this server has a lot
>of databases (about 150) and about 300+ Gig on it. I
>remember reading an article posted here about an issue
>where if a server has a lot of databases, it may need
>manual runs of stats.
>Can someone repoint me to that article? It actually
>outlined the algorithm that Sql Server used, etc.
>I couldn't find it in the kb or bol.
>
>.
>
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.Is this the one you are referring to?
http://support.microsoft.com/defaul...b;EN-US;q195565
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"GoBoy" <anonymous@.discussions.microsoft.com> wrote in message
news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.|||Great article but that's not it. This article dealt more
with how Sql Server "round robins" through the databases
and the timing of that, etc.
>--Original Message--
>Is this the one you are referring to?
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q195565
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"GoBoy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
>I have the above set on all my databases on one of our
>2000 production servers. However, this server has a lot
>of databases (about 150) and about 300+ Gig on it. I
>remember reading an article posted here about an issue
>where if a server has a lot of databases, it may need
>manual runs of stats.
>Can someone repoint me to that article? It actually
>outlined the algorithm that Sql Server used, etc.
>I couldn't find it in the kb or bol.
>
>.
>
Auto Update Stats
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.
Is this the one you are referring to?
http://support.microsoft.com/default...;EN-US;q195565
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"GoBoy" <anonymous@.discussions.microsoft.com> wrote in message
news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.
|||Great article but that's not it. This article dealt more
with how Sql Server "round robins" through the databases
and the timing of that, etc.
>--Original Message--
>Is this the one you are referring to?
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q195565
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"GoBoy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
>I have the above set on all my databases on one of our
>2000 production servers. However, this server has a lot
>of databases (about 150) and about 300+ Gig on it. I
>remember reading an article posted here about an issue
>where if a server has a lot of databases, it may need
>manual runs of stats.
>Can someone repoint me to that article? It actually
>outlined the algorithm that Sql Server used, etc.
>I couldn't find it in the kb or bol.
>
>.
>
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.
Is this the one you are referring to?
http://support.microsoft.com/default...;EN-US;q195565
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"GoBoy" <anonymous@.discussions.microsoft.com> wrote in message
news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.
|||Great article but that's not it. This article dealt more
with how Sql Server "round robins" through the databases
and the timing of that, etc.
>--Original Message--
>Is this the one you are referring to?
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q195565
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"GoBoy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
>I have the above set on all my databases on one of our
>2000 production servers. However, this server has a lot
>of databases (about 150) and about 300+ Gig on it. I
>remember reading an article posted here about an issue
>where if a server has a lot of databases, it may need
>manual runs of stats.
>Can someone repoint me to that article? It actually
>outlined the algorithm that Sql Server used, etc.
>I couldn't find it in the kb or bol.
>
>.
>
Auto Update Stats
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.Is this the one you are referring to?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q195565
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"GoBoy" <anonymous@.discussions.microsoft.com> wrote in message
news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.|||Great article but that's not it. This article dealt more
with how Sql Server "round robins" through the databases
and the timing of that, etc.
>--Original Message--
>Is this the one you are referring to?
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q195565
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"GoBoy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
>I have the above set on all my databases on one of our
>2000 production servers. However, this server has a lot
>of databases (about 150) and about 300+ Gig on it. I
>remember reading an article posted here about an issue
>where if a server has a lot of databases, it may need
>manual runs of stats.
>Can someone repoint me to that article? It actually
>outlined the algorithm that Sql Server used, etc.
>I couldn't find it in the kb or bol.
>
>.
>
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.Is this the one you are referring to?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q195565
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"GoBoy" <anonymous@.discussions.microsoft.com> wrote in message
news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.|||Great article but that's not it. This article dealt more
with how Sql Server "round robins" through the databases
and the timing of that, etc.
>--Original Message--
>Is this the one you are referring to?
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q195565
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"GoBoy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
>I have the above set on all my databases on one of our
>2000 production servers. However, this server has a lot
>of databases (about 150) and about 300+ Gig on it. I
>remember reading an article posted here about an issue
>where if a server has a lot of databases, it may need
>manual runs of stats.
>Can someone repoint me to that article? It actually
>outlined the algorithm that Sql Server used, etc.
>I couldn't find it in the kb or bol.
>
>.
>
Thursday, February 16, 2012
Auto update statistics and Auto create statistics options
Recovery Model of the Production Database is FULL.
We have activated the Auto update statistics and Auto
create statistics options. We would like to know
switching off these options, will the size of the
Transaction Log be smaller ?
ThanksNo, these doesn't affect the size of the transaction log. If you have
problem with the size of the transaction log, there are other places to
look. Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:9b5501c3eaa8$aae1dcc0$a401280a@.phx.gbl...
> Recovery Model of the Production Database is FULL.
> We have activated the Auto update statistics and Auto
> create statistics options. We would like to know
> switching off these options, will the size of the
> Transaction Log be smaller ?
> Thanks
We have activated the Auto update statistics and Auto
create statistics options. We would like to know
switching off these options, will the size of the
Transaction Log be smaller ?
ThanksNo, these doesn't affect the size of the transaction log. If you have
problem with the size of the transaction log, there are other places to
look. Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:9b5501c3eaa8$aae1dcc0$a401280a@.phx.gbl...
> Recovery Model of the Production Database is FULL.
> We have activated the Auto update statistics and Auto
> create statistics options. We would like to know
> switching off these options, will the size of the
> Transaction Log be smaller ?
> Thanks
Auto update statistics and Auto create statistics options
Recovery Model of the Production Database is FULL.
We have activated the Auto update statistics and Auto
create statistics options. We would like to know
switching off these options, will the size of the
Transaction Log be smaller ?
ThanksNo, these doesn't affect the size of the transaction log. If you have
problem with the size of the transaction log, there are other places to
look. Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:9b5501c3eaa8$aae1dcc0$a401280a@.phx.gbl...
We have activated the Auto update statistics and Auto
create statistics options. We would like to know
switching off these options, will the size of the
Transaction Log be smaller ?
ThanksNo, these doesn't affect the size of the transaction log. If you have
problem with the size of the transaction log, there are other places to
look. Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:9b5501c3eaa8$aae1dcc0$a401280a@.phx.gbl...
quote:
> Recovery Model of the Production Database is FULL.
> We have activated the Auto update statistics and Auto
> create statistics options. We would like to know
> switching off these options, will the size of the
> Transaction Log be smaller ?
> Thanks
Monday, February 13, 2012
Auto Restore of Production to Developement Db on same Server
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||
Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||
ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||
thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||
Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||
ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||
thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||thanks
appreciate it.
rik
Subscribe to:
Posts (Atom)