Creating Test Database

I need to create a test database on the live server for performance testing.
From time to time, as structural modifications are made to the live
database, I'd like to be able to delete the test database and replace it
with a new copy of the live database. Is there a simple way to do this with
a script or other? Thanks!I'd go for backup and restore. When you do the restore, specify a different database name and use
the MOVE option of the RESTORE command to specify new database file names.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Neil" <nospam@.nospam.netwrote in message news:EAb_i.20588$Pv2.7236@.newssvr23.news.prodigy.n et...

Quote:

Originally Posted by

>I need to create a test database on the live server for performance testing. From time to time, as
>structural modifications are made to the live database, I'd like to be able to delete the test
>database and replace it with a new copy of the live database. Is there a simple way to do this with
>a script or other? Thanks!
>

|||And if there's an existing db with that name, it will overwrite it entirely?

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.comwr ote in
message news:F4D3C4C1-35C2-4CFE-81C0-A7AF434911AA@.microsoft.com...

Quote:

Originally Posted by

I'd go for backup and restore. When you do the restore, specify a
different database name and use the MOVE option of the RESTORE command to
specify new database file names.
>
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
>
>
"Neil" <nospam@.nospam.netwrote in message
news:EAb_i.20588$Pv2.7236@.newssvr23.news.prodigy.n et...

Quote:

Originally Posted by

>>I need to create a test database on the live server for performance
>>testing. From time to time, as structural modifications are made to the
>>live database, I'd like to be able to delete the test database and replace
>>it with a new copy of the live database. Is there a simple way to do this
>>with a script or other? Thanks!
>>

>

|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

And if there's an existing db with that name, it will overwrite it
entirely?

Yes. I always use the REPLACE option together the MOVE as suggested by
Tibor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to both of you!

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99E7EE6584750Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>And if there's an existing db with that name, it will overwrite it
>entirely?

>
Yes. I always use the REPLACE option together the MOVE as suggested by
Tibor.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Creating Reports from a SQL Server Database

What software/application offers the fastest performance when reporting from
a SQL Server database. Currently I am using MS-Access with an odbc
connection to the database. Although the tables are indexed, they are
somewhat large and multiple links are needed in queries. The result is that
in some instances reports take quite a while to run. I am trying to find out
if there is a "native" or faster way to produce these reports.
If you're using SQL Server 2000 then Reporting Services would definitely be
a better option.
Michael Otey
"Jim Pockmire" <jpockmire@.cinci.rr.com> wrote in message
news:ulbJc.54361$W6.54088@.fe2.texas.rr.com...
> What software/application offers the fastest performance when reporting
from
> a SQL Server database. Currently I am using MS-Access with an odbc
> connection to the database. Although the tables are indexed, they are
> somewhat large and multiple links are needed in queries. The result is
that
> in some instances reports take quite a while to run. I am trying to find
out
> if there is a "native" or faster way to produce these reports.
>
|||Michael,
Yes this is a SQL Server 2000 database. But being unfamiliar with it, is
"Reporting Services" a component of SQL Server?
Jim
"Michael Otey" <mikeo@.teca.com> wrote in message
news:%23enJXbjaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> If you're using SQL Server 2000 then Reporting Services would definitely
be
> a better option.
> Michael Otey
>
> "Jim Pockmire" <jpockmire@.cinci.rr.com> wrote in message
> news:ulbJc.54361$W6.54088@.fe2.texas.rr.com...
> from
> that
> out
>
|||I am using SQL Server 2000 via Microsoft Office XP Developer Edition. I
can't seem to find out if Reporting Services is included or not. How can I
get more information?
"Michael Otey" <mikeo@.teca.com> wrote in message
news:%23enJXbjaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> If you're using SQL Server 2000 then Reporting Services would definitely
be
> a better option.
> Michael Otey
>
> "Jim Pockmire" <jpockmire@.cinci.rr.com> wrote in message
> news:ulbJc.54361$W6.54088@.fe2.texas.rr.com...
> from
> that
> out
>
|||If you have SQL Server 2000 you are also licensed to use Reporting Services.
Reporting Services is an excellent reporting authoring and deployments tool.
The place to start to find out more information is:
http://www.microsoft.com/sql/reporting/default.asp
and
http://www.microsoft.com/sql/reporti...uy/default.asp
Michael Otey
"Jim Pockmire" <jpockmire@.cinci.rr.com> wrote in message
news:xxxJc.44357$mY2.4728@.fe1.texas.rr.com...
> I am using SQL Server 2000 via Microsoft Office XP Developer Edition. I
> can't seem to find out if Reporting Services is included or not. How can
I[vbcol=seagreen]
> get more information?
>
> "Michael Otey" <mikeo@.teca.com> wrote in message
> news:%23enJXbjaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> be
reporting[vbcol=seagreen]
find
>

Creating Reports from a SQL Server Database

What software/application offers the fastest performance when reporting from
a SQL Server database. Currently I am using MS-Access with an odbc
connection to the database. Although the tables are indexed, they are
somewhat large and multiple links are needed in queries. The result is that
in some instances reports take quite a while to run. I am trying to find out
if there is a "native" or faster way to produce these reports.If you're using SQL Server 2000 then Reporting Services would definitely be
a better option.
Michael Otey
"Jim Pockmire" <jpockmire@.cinci.rr.com> wrote in message
news:ulbJc.54361$W6.54088@.fe2.texas.rr.com...
> What software/application offers the fastest performance when reporting
from
> a SQL Server database. Currently I am using MS-Access with an odbc
> connection to the database. Although the tables are indexed, they are
> somewhat large and multiple links are needed in queries. The result is
that
> in some instances reports take quite a while to run. I am trying to find
out
> if there is a "native" or faster way to produce these reports.
>|||Michael,
Yes this is a SQL Server 2000 database. But being unfamiliar with it, is
"Reporting Services" a component of SQL Server?
Jim
"Michael Otey" <mikeo@.teca.com> wrote in message
news:%23enJXbjaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> If you're using SQL Server 2000 then Reporting Services would definitely
be
> a better option.
> Michael Otey
>
> "Jim Pockmire" <jpockmire@.cinci.rr.com> wrote in message
> news:ulbJc.54361$W6.54088@.fe2.texas.rr.com...
> from
> that
> out
>|||I am using SQL Server 2000 via Microsoft Office XP Developer Edition. I
can't seem to find out if Reporting Services is included or not. How can I
get more information?
"Michael Otey" <mikeo@.teca.com> wrote in message
news:%23enJXbjaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> If you're using SQL Server 2000 then Reporting Services would definitely
be
> a better option.
> Michael Otey
>
> "Jim Pockmire" <jpockmire@.cinci.rr.com> wrote in message
> news:ulbJc.54361$W6.54088@.fe2.texas.rr.com...
> from
> that
> out
>|||If you have SQL Server 2000 you are also licensed to use Reporting Services.
Reporting Services is an excellent reporting authoring and deployments tool.
The place to start to find out more information is:
http://www.microsoft.com/sql/reporting/default.asp
and
http://www.microsoft.com/sql/report...buy/default.asp
Michael Otey
"Jim Pockmire" <jpockmire@.cinci.rr.com> wrote in message
news:xxxJc.44357$mY2.4728@.fe1.texas.rr.com...
> I am using SQL Server 2000 via Microsoft Office XP Developer Edition. I
> can't seem to find out if Reporting Services is included or not. How can
I
> get more information?
>
> "Michael Otey" <mikeo@.teca.com> wrote in message
> news:%23enJXbjaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> be
reporting[vbcol=seagreen]
find[vbcol=seagreen]
>

Creating indexes

I need to improve the performance of a query that is scheduled to run
every 5 minutes but is now taking longer than 5 minutes to execute.
I want to add an index or 2 to the table but I'm not sure of the
impact that will have during production hours. Another question, does
an index get used based on the "select" list or the "where" condition.
Lastly I used the database index tuning wizard and would like to know
what people think of the recommedations that the wizard provide.1) Be wary of ITW.
2) Indexes can be used for both select (if they cover all columns in the
output) and where clauses.
3) index tuning is much more than just "I think i need an index or 2". It
is a very complex subject with lots of variables that come into play. Best
is to get a pro to give you a quick review to help you develop an
appropriate indexing strategy. Failing that, indexes do take up space and
have overhead associated with maintaining them for inserts, updates and
deletes. Sometimes they don't help out at all like you think they should
because the data is very unspecific.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:000e3e3a-936d-41df-95f0-5336b20289f6@.b32g2000hsa.googlegroups.com...
>I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||In addition to Kevin's reply: when you create a new index, the entire
table will be scanned, and you should expect that during this time the
table will be locked.
Obviously, it would be best to test both the index creation and the
index' effectiveness on a test machine.
And finally, to repeat the previous reply: each addition index adds to
the cost of each insert and delete, and to all relevant updates. This is
also something you might want to test before deploying.
Gert-Jan
NC3 wrote:
> I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||On Nov 20, 5:22 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> In addition to Kevin's reply: when you create a new index, the entire
> table will be scanned, and you should expect that during this time the
> table will be locked.
> Obviously, it would be best to test both the index creation and the
> index' effectiveness on a test machine.
> And finally, to repeat the previous reply: each addition index adds to
> the cost of each insert and delete, and to all relevant updates. This is
> also something you might want to test before deploying.
> --
> Gert-Jan
>
> NC3 wrote:
>
>
> - Show quoted text -
Thanks for the responses, I will look into getting the db copied to a
test server and try addding the indexes to see if they are useful at
all.

Creating indexes

I need to improve the performance of a query that is scheduled to run
every 5 minutes but is now taking longer than 5 minutes to execute.
I want to add an index or 2 to the table but I'm not sure of the
impact that will have during production hours. Another question, does
an index get used based on the "select" list or the "where" condition.
Lastly I used the database index tuning wizard and would like to know
what people think of the recommedations that the wizard provide.
1) Be wary of ITW.
2) Indexes can be used for both select (if they cover all columns in the
output) and where clauses.
3) index tuning is much more than just "I think i need an index or 2". It
is a very complex subject with lots of variables that come into play. Best
is to get a pro to give you a quick review to help you develop an
appropriate indexing strategy. Failing that, indexes do take up space and
have overhead associated with maintaining them for inserts, updates and
deletes. Sometimes they don't help out at all like you think they should
because the data is very unspecific.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:000e3e3a-936d-41df-95f0-5336b20289f6@.b32g2000hsa.googlegroups.com...
>I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.
|||On Nov 20, 5:22 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> In addition to Kevin's reply: when you create a new index, the entire
> table will be scanned, and you should expect that during this time the
> table will be locked.
> Obviously, it would be best to test both the index creation and the
> index' effectiveness on a test machine.
> And finally, to repeat the previous reply: each addition index adds to
> the cost of each insert and delete, and to all relevant updates. This is
> also something you might want to test before deploying.
> --
> Gert-Jan
>
> NC3 wrote:
>
> - Show quoted text -
Thanks for the responses, I will look into getting the db copied to a
test server and try addding the indexes to see if they are useful at
all.

Creating indexes

I need to improve the performance of a query that is scheduled to run
every 5 minutes but is now taking longer than 5 minutes to execute.
I want to add an index or 2 to the table but I'm not sure of the
impact that will have during production hours. Another question, does
an index get used based on the "select" list or the "where" condition.
Lastly I used the database index tuning wizard and would like to know
what people think of the recommedations that the wizard provide.1) Be wary of ITW.
2) Indexes can be used for both select (if they cover all columns in the
output) and where clauses.
3) index tuning is much more than just "I think i need an index or 2". It
is a very complex subject with lots of variables that come into play. Best
is to get a pro to give you a quick review to help you develop an
appropriate indexing strategy. Failing that, indexes do take up space and
have overhead associated with maintaining them for inserts, updates and
deletes. Sometimes they don't help out at all like you think they should
because the data is very unspecific.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:000e3e3a-936d-41df-95f0-5336b20289f6@.b32g2000hsa.googlegroups.com...
>I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||In addition to Kevin's reply: when you create a new index, the entire
table will be scanned, and you should expect that during this time the
table will be locked.
Obviously, it would be best to test both the index creation and the
index' effectiveness on a test machine.
And finally, to repeat the previous reply: each addition index adds to
the cost of each insert and delete, and to all relevant updates. This is
also something you might want to test before deploying.
--
Gert-Jan
NC3 wrote:
> I need to improve the performance of a query that is scheduled to run
> every 5 minutes but is now taking longer than 5 minutes to execute.
> I want to add an index or 2 to the table but I'm not sure of the
> impact that will have during production hours. Another question, does
> an index get used based on the "select" list or the "where" condition.
> Lastly I used the database index tuning wizard and would like to know
> what people think of the recommedations that the wizard provide.|||On Nov 20, 5:22 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> In addition to Kevin's reply: when you create a new index, the entire
> table will be scanned, and you should expect that during this time the
> table will be locked.
> Obviously, it would be best to test both the index creation and the
> index' effectiveness on a test machine.
> And finally, to repeat the previous reply: each addition index adds to
> the cost of each insert and delete, and to all relevant updates. This is
> also something you might want to test before deploying.
> --
> Gert-Jan
>
> NC3 wrote:
> > I need to improve the performance of a query that is scheduled to run
> > every 5 minutes but is now taking longer than 5 minutes to execute.
> > I want to add an index or 2 to the table but I'm not sure of the
> > impact that will have during production hours. Another question, does
> > an index get used based on the "select" list or the "where" condition.
> > Lastly I used the database index tuning wizard and would like to know
> > what people think of the recommedations that the wizard provide.- Hide quoted text -
> - Show quoted text -
Thanks for the responses, I will look into getting the db copied to a
test server and try addding the indexes to see if they are useful at
all.