copy index statistics from production to development.

I have a very large database that I need to be able to copy the statistics only from the production database server to my develoment environment. I need to be able to reproduce the execution plans on development without moving all the data. MS does this when they need to review the exeecution plan for large complex databases.

Need help.

See if this helps.

Transferring SQL Server Statistics From One Database to Another

http://www.sql-server-performance.com/jc_transferring_statistics.asp

AMB

|||

Thanks for your help.

|||

The technique described in the link is not a supported mechanism. It will not work in SQL Server 2005 also. It is risky to manipulate system metadata especially page pointers from one database to another. You will end up corrupting the database. The ability to create statistics and histograms for indexes is available in SQL Server 2005. You can use the SMO scripting capability to do this and create an empty database with all the stats from production. See the following topics for more details:

http://msdn2.microsoft.com/en-us/library/ms186472.aspx -- Use Script Statistics option

Copy Database Wizard

We have member SQL servers in a workgroup, running in a netware environment.
We do not have a domain. The wizard fails when I try to copy a database f
rom one server to another. What's the solution or workaround?
Thank you.Part of the process for the Copy Database Wizard involves the SQL Server
startup account on the destination accessing a mapped drive on the source
machine. If this account cannot access a mapped drive the Copy Database
Wizard will fail.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Copy database changes without data

Hi,

This requirement is for our development and test environment. We have a database server which we use for development hence many changes are done on the database objects on daily basis like tables def changes, view def changes, sp changes, new sp etc.

On the other hand our testing team connects to another database server where they perform testing hence any internal test release would need the changes like tables def changes, view def changes, sp changes, new sp etc.

Since testing team already have their test data set in the test environment hence they want to keep their data but new test release should only provide with datbase object changes. The difficult part is, we have over 500 tables and about 1000 views and somewhere about 400 stored procs, so its not possible to manually perform the above task.

I dont do database admin job hence clueless as what would be the best way to accomplish the above. Pl advice.

we are using sql server 2005 on both test and dev environemts.

cheers!
d2

Check out SQL Compare from RedGate.

http://www.red-gate.com/

WesleyB

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

|||

Check out Visual Studio Team Suite for Database Professionals

http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx

|||Hi Vishal,

Thanks for your response.

We have license for team system but we are not using visual studio for database professionals as yet. Could you pl point me to some good tutorial on this subject.

thanks,
d2
|||

http://msdn2.microsoft.com/en-us/teamsystem/aa718764.aspx

|||i tested SQL Compare [www.red-gate.com] with SQL Server 2005. it is very good tool and it enables you to synchronize changes on DB Schema.|||I have used the VSTS for Database Professionals tool as we have VSTS licenses. It works like a charm.

Thanks for all the advises.

Copy database changes without data

Hi,

This requirement is for our development and test environment. We have a database server which we use for development hence many changes are done on the database objects on daily basis like tables def changes, view def changes, sp changes, new sp etc.

On the other hand our testing team connects to another database server where they perform testing hence any internal test release would need the changes like tables def changes, view def changes, sp changes, new sp etc.

Since testing team already have their test data set in the test environment hence they want to keep their data but new test release should only provide with datbase object changes. The difficult part is, we have over 500 tables and about 1000 views and somewhere about 400 stored procs, so its not possible to manually perform the above task.

I dont do database admin job hence clueless as what would be the best way to accomplish the above. Pl advice.

we are using sql server 2005 on both test and dev environemts.

cheers!
d2

Check out SQL Compare from RedGate.

http://www.red-gate.com/

WesleyB

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

|||

Check out Visual Studio Team Suite for Database Professionals

http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx

|||Hi Vishal,

Thanks for your response.

We have license for team system but we are not using visual studio for database professionals as yet. Could you pl point me to some good tutorial on this subject.

thanks,
d2
|||

http://msdn2.microsoft.com/en-us/teamsystem/aa718764.aspx

|||i tested SQL Compare [www.red-gate.com] with SQL Server 2005. it is very good tool and it enables you to synchronize changes on DB Schema.|||I have used the VSTS for Database Professionals tool as we have VSTS licenses. It works like a charm.

Thanks for all the advises.

Copy database changes without data

Hi,
This requirement is for our development and test environment. We have
a database server which we use for development hence many changes are
done on the database objects on daily basis like tables def changes,
view def changes, sp changes, new sp etc.
On the other hand our testing team connects to another database server
where they perform testing hence any internal test release would need
the changes like tables def changes, view def changes, sp changes, new
sp etc.
Since testing team already have their test data set in the test
environment hence they want to keep their data but new test release
should only provide with datbase object changes.
I dont do database admin job hence clueless as what would be the best
way to accomplish the above. Pl advice.
we are using sql server 2005 on both test and dev environemts.
cheers!
d2
D2
Visit at http://www.red-gate.com/products/SQL_Compare/index.htm
"D2" <dhapola@.yahoo.com> wrote in message
news:1185784582.666495.164430@.x35g2000prf.googlegr oups.com...
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>
|||* D2 wrote, On 30-7-2007 10:36:
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>
If you're using Visual Studio Team System, have a look at the new
Database professional add-in for the suite.
http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx
I can also heartily recommend Red-gate SQL Compare, I've used that
product many times before with great results:
http://www.red-gate.com/products/SQL_Compare/index.htm
Jesse
|||I like xsql. http://www.xsqlsoftware.com/Main.aspx
They have an sdk which is very handy for custom applications.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D2" <dhapola@.yahoo.com> wrote in message
news:1185784582.666495.164430@.x35g2000prf.googlegr oups.com...
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>

Copy database changes without data

Hi,
This requirement is for our development and test environment. We have
a database server which we use for development hence many changes are
done on the database objects on daily basis like tables def changes,
view def changes, sp changes, new sp etc.
On the other hand our testing team connects to another database server
where they perform testing hence any internal test release would need
the changes like tables def changes, view def changes, sp changes, new
sp etc.
Since testing team already have their test data set in the test
environment hence they want to keep their data but new test release
should only provide with datbase object changes.
I dont do database admin job hence clueless as what would be the best
way to accomplish the above. Pl advice.
we are using sql server 2005 on both test and dev environemts.
cheers!
d2D2
Visit at http://www.red-gate.com/products/SQL_Compare/index.htm
"D2" <dhapola@.yahoo.com> wrote in message
news:1185784582.666495.164430@.x35g2000prf.googlegroups.com...
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>|||* D2 wrote, On 30-7-2007 10:36:
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>
If you're using Visual Studio Team System, have a look at the new
Database professional add-in for the suite.
http://msdn2.microsoft.com/en-us/te...m/aa718807.aspx
I can also heartily recommend Red-gate SQL Compare, I've used that
product many times before with great results:
http://www.red-gate.com/products/SQL_Compare/index.htm
Jesse|||I like xsql. http://www.xsqlsoftware.com/Main.aspx
They have an sdk which is very handy for custom applications.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D2" <dhapola@.yahoo.com> wrote in message
news:1185784582.666495.164430@.x35g2000prf.googlegroups.com...
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>|||I recommend ApexSQL Diff. http://www.apexsql.com/sql_tools_diff.asp
TheSQLGuru
President
Indicium Resources, Inc.
"D2" <dhapola@.yahoo.com> wrote in message
news:1185784582.666495.164430@.x35g2000prf.googlegroups.com...
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>

Copy database changes without data

Hi,
This requirement is for our development and test environment. We have
a database server which we use for development hence many changes are
done on the database objects on daily basis like tables def changes,
view def changes, sp changes, new sp etc.
On the other hand our testing team connects to another database server
where they perform testing hence any internal test release would need
the changes like tables def changes, view def changes, sp changes, new
sp etc.
Since testing team already have their test data set in the test
environment hence they want to keep their data but new test release
should only provide with datbase object changes.
I dont do database admin job hence clueless as what would be the best
way to accomplish the above. Pl advice.
we are using sql server 2005 on both test and dev environemts.
cheers!
d2D2
Visit at http://www.red-gate.com/products/SQL_Compare/index.htm
"D2" <dhapola@.yahoo.com> wrote in message
news:1185784582.666495.164430@.x35g2000prf.googlegroups.com...
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>|||* D2 wrote, On 30-7-2007 10:36:
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>
If you're using Visual Studio Team System, have a look at the new
Database professional add-in for the suite.
http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx
I can also heartily recommend Red-gate SQL Compare, I've used that
product many times before with great results:
http://www.red-gate.com/products/SQL_Compare/index.htm
Jesse|||I like xsql. http://www.xsqlsoftware.com/Main.aspx
They have an sdk which is very handy for custom applications.
--
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D2" <dhapola@.yahoo.com> wrote in message
news:1185784582.666495.164430@.x35g2000prf.googlegroups.com...
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>|||I recommend ApexSQL Diff. http://www.apexsql.com/sql_tools_diff.asp
--
TheSQLGuru
President
Indicium Resources, Inc.
"D2" <dhapola@.yahoo.com> wrote in message
news:1185784582.666495.164430@.x35g2000prf.googlegroups.com...
> Hi,
> This requirement is for our development and test environment. We have
> a database server which we use for development hence many changes are
> done on the database objects on daily basis like tables def changes,
> view def changes, sp changes, new sp etc.
> On the other hand our testing team connects to another database server
> where they perform testing hence any internal test release would need
> the changes like tables def changes, view def changes, sp changes, new
> sp etc.
> Since testing team already have their test data set in the test
> environment hence they want to keep their data but new test release
> should only provide with datbase object changes.
> I dont do database admin job hence clueless as what would be the best
> way to accomplish the above. Pl advice.
> we are using sql server 2005 on both test and dev environemts.
> cheers!
> d2
>

Converting MSAccess .MDB to SQL Server database

I need to switch from MS Access to SQL Server for my database. To setup a development environment I downloaded the free Microsoft SQL ServerExpress (February CTP version). I installed the required .NET Frameworkv2, and then SQLExpress. The install was done using all the defaults,and was done successfully. I also downloaded and installed the SQLExpress Manager Tool.
The SQL Server was installed on the same machine as my VS.NETdevelopment environment. The SQL Server process is now running, and Ican connect to the server using the SQL Express Manager Tool. Thisallows me to view and query the sample databases, but not much else.
To convert my Access .MDB database to SQL Server, I am trying to usethe MS Access Upsizing Wizard. The version of Access I am using isAccess 2002 on a Windows XP-Professional system. The problem is thatAccess cannot get a connection to the SQL Server. I tried using thedefault server name "(local)" and "Use Trusted Connection", but Ireceive the following error:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver[]Shared Memory]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver[]Shared Memory]SQL Server does not exist or access denied.
Curiously, I get this exact same error message even if the SQL serviceis stopped. So I'm pretty sure the problem is that it is not findingthe SQL server, and not a security issue.
In order to connect to the SQL server using the SQL Server ManagerTool, you have to provide the actual instance name for the server"COMPNAME\SQLExpress". So I tried using this server name in the AccessUpsizing Wizard, but this returns the same error message as aboveexcept the first SQL Server Error is 53. I also tried using a Login IDand password (using the Windows administrator ID and password, and alsothe "sa" ID and password) to no avail.
I am at wits end, and can't figure out why Access can't find the SQL Server. Any ideas would be appreciated.
Thanks.

I believe I found the answer to my problem through trial and error. Itappears that that Access wants to communicate with the SQL Server viaTCP/IP, however the default for SQL Server Express when installed onthe local machine is shared memory. If you enable shared memory in theSQL Computer Manager, it all seems to work. And it also appears thatthe server name must be specified as "COMPNAME\SQLExpress", not"(local)".|||

Thank you Paul.

After several hours of searching, your solution worked right out of the box.

You would only hope that some of the folks at MSDN would mention your solution on their websites.