Moving Transaction Log file to different partition

How to do that when I have already created database which have database and
log files on same partition. What I need is to move log file to another
partition.
DubravkoUse sp_detach_db and sp_attach_db. See KB 224071 for more info.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"duby" <duby72002@.yahoo.com> wrote in message news:u6W1bi5nDHA.684@.TK2MSFTNGP09.phx.gbl...
> How to do that when I have already created database which have database and
> log files on same partition. What I need is to move log file to another
> partition.
> Dubravko
>|||detach and transfer
--
Shaju Thomas
e-Cosmos Technologies Ltd.,
Mail: shaju@.e-cosmostech.com
Phone(Off) : 51217038/39 Extn: 132
Mobile: +91 98455 21794
"duby" <duby72002@.yahoo.com> wrote in message
news:u6W1bi5nDHA.684@.TK2MSFTNGP09.phx.gbl...
> How to do that when I have already created database which have database
and
> log files on same partition. What I need is to move log file to another
> partition.
> Dubravko
>sql

Moving tables, procs ... etc from full version of SQL Server to MS

I have all of the tables, procs ... etc created using a full version of SQL
Server. Now I want to move the tables, proc ... etc to MSDE so I can
distribute what I've created. Is there an easy way?
detach db .. copy Db and log file to MSDE installed computer
attach db on MSDE
"MisterB" <MisterB@.discussions.microsoft.com> wrote in message
news:55FAC4EA-DC77-426F-84D0-8395521103C8@.microsoft.com...
> I have all of the tables, procs ... etc created using a full version of
SQL
> Server. Now I want to move the tables, proc ... etc to MSDE so I can
> distribute what I've created. Is there an easy way?

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI on PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/B9F7F302-964A-4825-9246-6143A8681900.scuk
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||Thanks all.
I have one more question. How do I move the log file from the default location
to another one.
I would like to add a new file to the log file and remove the first file to the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
> own.
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option in TSQL
to add a column at the desired location. The newly added NTEXT column should be
right before the existing NTEXT column, so that after dropping the existing NTEXT
column, its position is taken up by this new column. This is the only way to guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series of steps
it does to mimic that.|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
--
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI on PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.
Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>
|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/B...3A8681900.scuk
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>
|||Thanks all.
I have one more question. How do I move the log file from the default location
to another one.
I would like to add a new file to the log file and remove the first file to the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.
|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
> own.
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option in TSQL
to add a column at the desired location. The newly added NTEXT column should be
right before the existing NTEXT column, so that after dropping the existing NTEXT
column, its position is taken up by this new column. This is the only way to guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series of steps
it does to mimic that.
|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com
sql

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI o
n PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns
and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate fil
egroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/...43A8681900.scuk
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||Thanks all.
I have one more question. How do I move the log file from the default locati
on
to another one.
I would like to add a new file to the log file and remove the first file to
the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its[
vbcol=seagreen]
> own.[/vbcol]
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be
used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option i
n TSQL
to add a column at the desired location. The newly added NTEXT column should
be
right before the existing NTEXT column, so that after dropping the existing
NTEXT
column, its position is taken up by this new column. This is the only way to
guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series
of steps
it does to mimic that.|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

Moving Reports to another computer Connection String

Hello
I created several Reports using SSRS and SSAS. now i have to move them to another PC, which name is different. is there a way to do this without having to open each report in BIDS and changing the connection string for each Data Source ?
thanks

There are several ways to do this, although it is probably too late for you now to do some of them:

1) use a shared datasource so that you can change only one place

2) use an expression as your connection string, as described here http://msdn2.microsoft.com/en-us/library/ms156450.aspx
... look for the section on "expression-based DataSources" or DataSource Expressions or something like that.

as I said it is too late for those now, you already created the reports, right?

3) write a script that loads each RDL/RDLC as an XML document and fixes the connection string -- or if there are only a handful of reports, just open the XML and do it manually... even manually it is probably faster than loading every report in the designer. But writing such a script to iterate over the contents of a folder and fix the one item you want to fix in each one shouldn't be very hard -- might come in handy later.

4) deploy the reports as-is to the new server and then manually fix their connection strings in the Report Manager interface, again depending on how many this might be pretty quick.

5) deploy the reports as-is to the new server and then use the SQL Studio Management interface to drill down to the Data Sources node for each report and fix it there -- might be faster than the BIDS way since you're not opening the reports

.... ?

>L<

Moving report from Access to Reporting Services

I created 2 stand alone queries and another that combines/joins the 2 for a
cumulative repoirt in MS Access. I need to recreate the combined report in
Reporting Services but do no know how:
Query 1 MON _is Down Karen 10_05
SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Down]
FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to 'MON-System Down'%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Query 2 MON_is Up Karen 10_05
SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Up]
FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to 'MON-System Up'%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Combined Query
SELECT [MON _is Down Karen 10_05].ref_num, [MON _is Down Karen 10_05].[Time
Stamp Down], [MON_is Up Karen 10_05].[Time Stamp Up],
AHD_net_res.nr_prim_search_key
FROM (([MON_is Up Karen 10_05] INNER JOIN [MON _is Down Karen 10_05] ON
[MON_is Up Karen 10_05].ref_num = [MON _is Down Karen 10_05].ref_num) INNER
JOIN call_req ON [MON _is Down Karen 10_05].ref_num = call_req.ref_num) LEFT
JOIN AHD_net_res ON call_req.affected_rc = AHD_net_res.id;
Thank you,
KarenI wanted to add the query in Reporting Services I have created:
SELECT DISTINCT
AHD.call_req.ref_num, MIN(DISTINCT
AHD.act_log.time_stamp) AS MINOftime_stamp, MIN(DISTINCT
AHD.act_log.time_stamp) AS [Time Stamp Down],
DATEADD(ss, AHD.act_log.time_stamp - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Time Down], DATEADD(ss,
act_log_1.time_stamp - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)) AS [Time Up], MAX(DISTINCT act_log_1.time_stamp)
AS [Time Stamp Up], MAX(DISTINCT act_log_1.time_stamp)
AS MaxOftime_stamp, AHD.net_res.nr_prim_search_key
FROM AHD.net_res RIGHT OUTER JOIN
AHD.call_req ON AHD.net_res.id =AHD.call_req.affected_rc LEFT OUTER JOIN
AHD.act_log act_log_1 ON AHD.call_req.persid =act_log_1.call_req_id LEFT OUTER JOIN
AHD.act_log ON AHD.call_req.persid =AHD.act_log.call_req_id
WHERE (AHD.act_log.action_desc LIKE '%to ''MON-System Down%') AND
(act_log_1.action_desc LIKE '%to ''MON-System Up%')
GROUP BY AHD.call_req.ref_num, DATEADD(ss, AHD.act_log.time_stamp - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), DATEADD(ss,
act_log_1.time_stamp - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)), AHD.net_res.nr_prim_search_key
ORDER BY AHD.net_res.nr_prim_search_key, AHD.call_req.ref_num
--The query works up to a point. I changed "First" to "Min" and "Max". I
am trying to weed out multiple instances of time_stamp when action_desc
inlcludes Mon is Up or Mon is Down. In a perfect world the user would enter
each only once but you know how that goes. So, I want the earliest time_stamp
when action_desc includes "to Mon-System Down" and Max time_stamp when
action_desc includes "to Mon-System Up".
Thanks in advance for any help with this.
Karen
"Moving rpts from Access to Rptg Services" wrote:
> I created 2 stand alone queries and another that combines/joins the 2 for a
> cumulative repoirt in MS Access. I need to recreate the combined report in
> Reporting Services but do no know how:
> Query 1 MON _is Down Karen 10_05
> SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
> First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Down]
> FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
> WHERE (((act_log.action_desc) Like "% to 'MON-System Down'%"))
> GROUP BY call_req.ref_num
> ORDER BY call_req.ref_num;
>
> Query 2 MON_is Up Karen 10_05
> SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
> First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Up]
> FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
> WHERE (((act_log.action_desc) Like "% to 'MON-System Up'%"))
> GROUP BY call_req.ref_num
> ORDER BY call_req.ref_num;
>
> Combined Query
> SELECT [MON _is Down Karen 10_05].ref_num, [MON _is Down Karen 10_05].[Time
> Stamp Down], [MON_is Up Karen 10_05].[Time Stamp Up],
> AHD_net_res.nr_prim_search_key
> FROM (([MON_is Up Karen 10_05] INNER JOIN [MON _is Down Karen 10_05] ON
> [MON_is Up Karen 10_05].ref_num = [MON _is Down Karen 10_05].ref_num) INNER
> JOIN call_req ON [MON _is Down Karen 10_05].ref_num = call_req.ref_num) LEFT
> JOIN AHD_net_res ON call_req.affected_rc = AHD_net_res.id;
> Thank you,
> Karen

Moving Report Builder reports to new server

I have a development server setup with Report Builder that our users have been testing out. They have a number of reports that they have created on this dev server.

We now have our live environment all setup and I'd like to move their Report Builder reports to the new server for them so they don't have to recreate them.

Is this possible? If so, how?

Thanks.

The Report Builder reports are stored in the report catalog just like the Report Designer reports. There are several options available for moving report definitions ranging from manually exporting and uploading them one by one to handling this programatically. I would recommend you try the Jasper Smith's Report Scripter to automate the process. Remember to script and deploy not only the report definitions but the the data source and the model.

Moving of Stored Procedures

Need a little advice,
I am creating an application, created two databases, one for building
the app. And the other is for when it is running. Is there a simple way
of copying the stored procedures to the one that i will be running the
app on? All the tables and columns are named the same in both.
Tom
*** Sent via Developersdex http://www.codecomments.com ***
We migrate the sp with custom made table and sp.
We have table with 'from server', 'To Server', 'sp_name' and' permission'
information, which I populate for any sp migration using bcp. This table and
script can differ depending on your needs.
We also use a script that is built around sp_helptext, which can create the
drop sp statement, and script and permission. This is only useful if you do
extensive version control. Please let me know if you found and easier way to
deal with this.
Sam
|||You can simply script out the stored procedures using Enterprise Manager by
right clicking on the db, going to all tasks then generate scripts. Take
that script(s) and run it on the other database via query analyzer. You can
also invest a few dollars into a tool from www.red-gate.com that will
compare the two db's and tell you want is different. It will also generate
the scripts to make them both the same. It's really worth the few hundred
dollars if you do this often.
Andrew J. Kelly SQL MVP
"Thomas Beyerlein" <tbxml@.yahoo.com> wrote in message
news:%23C7dnX%23NGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Need a little advice,
> I am creating an application, created two databases, one for building
> the app. And the other is for when it is running. Is there a simple way
> of copying the stored procedures to the one that i will be running the
> app on? All the tables and columns are named the same in both.
> Tom
>
> *** Sent via Developersdex http://www.codecomments.com ***

Moving of Stored Procedures

Need a little advice,
I am creating an application, created two databases, one for building
the app. And the other is for when it is running. Is there a simple way
of copying the stored procedures to the one that i will be running the
app on? All the tables and columns are named the same in both.
Tom
*** Sent via Developersdex http://www.developersdex.com ***We migrate the sp with custom made table and sp.
We have table with 'from server', 'To Server', 'sp_name' and' permission'
information, which I populate for any sp migration using bcp. This table and
script can differ depending on your needs.
We also use a script that is built around sp_helptext, which can create the
drop sp statement, and script and permission. This is only useful if you do
extensive version control. Please let me know if you found and easier way to
deal with this.
Sam|||You can simply script out the stored procedures using Enterprise Manager by
right clicking on the db, going to all tasks then generate scripts. Take
that script(s) and run it on the other database via query analyzer. You can
also invest a few dollars into a tool from www.red-gate.com that will
compare the two db's and tell you want is different. It will also generate
the scripts to make them both the same. It's really worth the few hundred
dollars if you do this often.
--
Andrew J. Kelly SQL MVP
"Thomas Beyerlein" <tbxml@.yahoo.com> wrote in message
news:%23C7dnX%23NGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Need a little advice,
> I am creating an application, created two databases, one for building
> the app. And the other is for when it is running. Is there a simple way
> of copying the stored procedures to the one that i will be running the
> app on? All the tables and columns are named the same in both.
> Tom
>
> *** Sent via Developersdex http://www.developersdex.com ***

Moving of Stored Procedures

Need a little advice,
I am creating an application, created two databases, one for building
the app. And the other is for when it is running. Is there a simple way
of copying the stored procedures to the one that i will be running the
app on? All the tables and columns are named the same in both.
Tom
*** Sent via Developersdex http://www.codecomments.com ***We migrate the sp with custom made table and sp.
We have table with 'from server', 'To Server', 'sp_name' and' permission'
information, which I populate for any sp migration using bcp. This table and
script can differ depending on your needs.
We also use a script that is built around sp_helptext, which can create the
drop sp statement, and script and permission. This is only useful if you do
extensive version control. Please let me know if you found and easier way t
o
deal with this.
Sam|||You can simply script out the stored procedures using Enterprise Manager by
right clicking on the db, going to all tasks then generate scripts. Take
that script(s) and run it on the other database via query analyzer. You can
also invest a few dollars into a tool from www.red-gate.com that will
compare the two db's and tell you want is different. It will also generate
the scripts to make them both the same. It's really worth the few hundred
dollars if you do this often.
Andrew J. Kelly SQL MVP
"Thomas Beyerlein" <tbxml@.yahoo.com> wrote in message
news:%23C7dnX%23NGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Need a little advice,
> I am creating an application, created two databases, one for building
> the app. And the other is for when it is running. Is there a simple way
> of copying the stored procedures to the one that i will be running the
> app on? All the tables and columns are named the same in both.
> Tom
>
> *** Sent via Developersdex http://www.codecomments.com ***

Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group> )
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself t
o
another.
Any suggestions would be appreciated.
--
Todd C(Re)Create a clustered index on the table of concern and specify the
filegroup then.
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group> )
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-cluster
ed
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>
>|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
Aaron Bertrand
SQL Server MVP|||Inserting/querrying/updating...not sure where the load is coming from at thi
s
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the blac
k
art of database performance tuning for a while...
Any good books or resources out there?
--
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because
the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s)
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...[vbcol=seagreen]
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issue
s.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C

Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group>)
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself to
another.
Any suggestions would be appreciated.
Todd C
(Re)Create a clustered index on the table of concern and specify the
filegroup then.
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group>)
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C
|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>
|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-clustered
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>
>
|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
Aaron Bertrand
SQL Server MVP
|||Inserting/querrying/updating...not sure where the load is coming from at this
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the black
art of database performance tuning for a while...
Any good books or resources out there?
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...[vbcol=seagreen]
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issues.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C

Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group>)
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself to
another.
Any suggestions would be appreciated.
--
Todd C(Re)Create a clustered index on the table of concern and specify the
filegroup then.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group>)
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
--
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>> Hello All;
>> I am trying to spread database objects by moving certain tables and
>> indexes
>> to file groups that I have created just for this purpose. Right now,
>> everything is in the PRIMARY file group, and I have created a secondary
>> Data
>> filegroup and a third file group for indexes.
>> I see in BOL the syntax for moving an index (actaully dropping and
>> re-creating) but don't see how to move just a single table. I can't seem
>> to
>> get the syntax correct.
>> ALTER TABLE <My Table>
>> DROP CONSTRAINT <My Constraint>
>> WITH (ONLINE = ON, MOVE TO <My File Group>)
>> That works, but only if I have something to drop. I don't want to drop
>> the
>> PK Constraints, I want to move them to one file group and the table
>> itself to
>> another.
>> Any suggestions would be appreciated.
>> --
>> Todd C
>|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
--
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:
> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-clustered
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> > (Re)Create a clustered index on the table of concern and specify the
> > filegroup then.
> >
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> >
> > "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> > news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> >> Hello All;
> >> I am trying to spread database objects by moving certain tables and
> >> indexes
> >> to file groups that I have created just for this purpose. Right now,
> >> everything is in the PRIMARY file group, and I have created a secondary
> >> Data
> >> filegroup and a third file group for indexes.
> >>
> >> I see in BOL the syntax for moving an index (actaully dropping and
> >> re-creating) but don't see how to move just a single table. I can't seem
> >> to
> >> get the syntax correct.
> >>
> >> ALTER TABLE <My Table>
> >> DROP CONSTRAINT <My Constraint>
> >> WITH (ONLINE = ON, MOVE TO <My File Group>)
> >>
> >> That works, but only if I have something to drop. I don't want to drop
> >> the
> >> PK Constraints, I want to move them to one file group and the table
> >> itself to
> >> another.
> >>
> >> Any suggestions would be appreciated.
> >> --
> >> Todd C
> >
> >
>
>|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
--
Aaron Bertrand
SQL Server MVP|||Inserting/querrying/updating...not sure where the load is coming from at this
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the black
art of database performance tuning for a while...
Any good books or resources out there?
--
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Leaving Table Partitioning out of the discussuion, what is the best way to
> > increase performance on tables that are heavily used and heavily indexed?
> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issues.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Todd, note that this moves the data, the clustered index and all
>> non-clustered indexes to the new filegroup. By definition, you cannot put
>> the clustered index on one filegroup, the data on another, and non-clustered
>> indexes on yet others...
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>> > (Re)Create a clustered index on the table of concern and specify the
>> > filegroup then.
>> >
>> > --
>> > TheSQLGuru
>> > President
>> > Indicium Resources, Inc.
>> >
>> > "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
>> > news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>> >> Hello All;
>> >> I am trying to spread database objects by moving certain tables and
>> >> indexes
>> >> to file groups that I have created just for this purpose. Right now,
>> >> everything is in the PRIMARY file group, and I have created a secondary
>> >> Data
>> >> filegroup and a third file group for indexes.
>> >>
>> >> I see in BOL the syntax for moving an index (actaully dropping and
>> >> re-creating) but don't see how to move just a single table. I can't seem
>> >> to
>> >> get the syntax correct.
>> >>
>> >> ALTER TABLE <My Table>
>> >> DROP CONSTRAINT <My Constraint>
>> >> WITH (ONLINE = ON, MOVE TO <My File Group>)
>> >>
>> >> That works, but only if I have something to drop. I don't want to drop
>> >> the
>> >> PK Constraints, I want to move them to one file group and the table
>> >> itself to
>> >> another.
>> >>
>> >> Any suggestions would be appreciated.
>> >> --
>> >> Todd C
>> >
>> >
>>|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C

moving MSDE database to SQL...

rI have an end-use that created a database on MSDE and is housing it on
his local computer. We want to move/copy it to our SQL server so it
can be backed up etc. What are the appropriate steps in doing so?
Thanks in advance,
KenIf they have both the same (and probably latest) service packs, the
compatibility is 100% and you don't have to take any special step (excerpt
maybe for authentification and login information). So a Backup/Restore or a
Detach/Reattach operation will be the easiest way.
S. L.
"Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
news:1CE3E73D-87E1-4058-BDAC-5D2FDB3984FE@.microsoft.com...
> rI have an end-use that created a database on MSDE and is housing it on
> his local computer. We want to move/copy it to our SQL server so it
> can be backed up etc. What are the appropriate steps in doing so?
> Thanks in advance,
> Ken
>|||Thanks for the reply, but I have no idea what the command lines are for MSDE
in order to backup etc. Where would I find those that I need in order to
backup and then restore to the SQL server?
Thanks,
Ken
"Sylvain Lafontaine" wrote:

> If they have both the same (and probably latest) service packs, the
> compatibility is 100% and you don't have to take any special step (excerpt
> maybe for authentification and login information). So a Backup/Restore or
a
> Detach/Reattach operation will be the easiest way.
> S. L.
> "Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
> news:1CE3E73D-87E1-4058-BDAC-5D2FDB3984FE@.microsoft.com...
>
>|||Well, look up BACKUP DATABASE in Books Online, and to execute it against
MSDE, you can use
osql -S(local)\instancename -E -Q"BACKUP DATABASE ..."
or
osql -S(local)\instancename -E -Q"EXEC sp_detachcb ..."
http://www.aspfaq.com/
(Reverse address to reply.)
"Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
news:17196A2A-FC3F-4B6C-AC32-BF41E842AB48@.microsoft.com...
> Thanks for the reply, but I have no idea what the command lines are for
MSDE[vbcol=seagreen]
> in order to backup etc. Where would I find those that I need in order to
> backup and then restore to the SQL server?
> Thanks,
> Ken
> "Sylvain Lafontaine" wrote:
>
(excerpt[vbcol=seagreen]
or a[vbcol=seagreen]
on[vbcol=seagreen]|||Mr. Aaron,
The problem with this is going to be the users password which Ken needs to
recreate after restoring the db.
v/r
ktf
"Aaron [SQL Server MVP]" wrote:

> Well, look up BACKUP DATABASE in Books Online, and to execute it against
> MSDE, you can use
> osql -S(local)\instancename -E -Q"BACKUP DATABASE ..."
> or
> osql -S(local)\instancename -E -Q"EXEC sp_detachcb ..."
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
> news:17196A2A-FC3F-4B6C-AC32-BF41E842AB48@.microsoft.com...
> MSDE
> (excerpt
> or a
> on
>
>|||> The problem with this is going to be the users password which Ken needs to
> recreate after restoring the db.
And he can't do:
osql -S(local)\instancename -E -Q"EXEC sp_password ..."
?
http://www.aspfaq.com/
(Reverse address to reply.)

moving MSDE database to SQL...

rI have an end-use that created a database on MSDE and is housing it on
his local computer. We want to move/copy it to our SQL server so it
can be backed up etc. What are the appropriate steps in doing so?
Thanks in advance,
Ken
If they have both the same (and probably latest) service packs, the
compatibility is 100% and you don't have to take any special step (excerpt
maybe for authentification and login information). So a Backup/Restore or a
Detach/Reattach operation will be the easiest way.
S. L.
"Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
news:1CE3E73D-87E1-4058-BDAC-5D2FDB3984FE@.microsoft.com...
> rI have an end-use that created a database on MSDE and is housing it on
> his local computer. We want to move/copy it to our SQL server so it
> can be backed up etc. What are the appropriate steps in doing so?
> Thanks in advance,
> Ken
>
|||Thanks for the reply, but I have no idea what the command lines are for MSDE
in order to backup etc. Where would I find those that I need in order to
backup and then restore to the SQL server?
Thanks,
Ken
"Sylvain Lafontaine" wrote:

> If they have both the same (and probably latest) service packs, the
> compatibility is 100% and you don't have to take any special step (excerpt
> maybe for authentification and login information). So a Backup/Restore or a
> Detach/Reattach operation will be the easiest way.
> S. L.
> "Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
> news:1CE3E73D-87E1-4058-BDAC-5D2FDB3984FE@.microsoft.com...
>
>
|||Well, look up BACKUP DATABASE in Books Online, and to execute it against
MSDE, you can use
osql -S(local)\instancename -E -Q"BACKUP DATABASE ..."
or
osql -S(local)\instancename -E -Q"EXEC sp_detachcb ..."
http://www.aspfaq.com/
(Reverse address to reply.)
"Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
news:17196A2A-FC3F-4B6C-AC32-BF41E842AB48@.microsoft.com...
> Thanks for the reply, but I have no idea what the command lines are for
MSDE[vbcol=seagreen]
> in order to backup etc. Where would I find those that I need in order to
> backup and then restore to the SQL server?
> Thanks,
> Ken
> "Sylvain Lafontaine" wrote:
(excerpt[vbcol=seagreen]
or a[vbcol=seagreen]
on[vbcol=seagreen]
|||Mr. Aaron,
The problem with this is going to be the users password which Ken needs to
recreate after restoring the db.
v/r
ktf
"Aaron [SQL Server MVP]" wrote:

> Well, look up BACKUP DATABASE in Books Online, and to execute it against
> MSDE, you can use
> osql -S(local)\instancename -E -Q"BACKUP DATABASE ..."
> or
> osql -S(local)\instancename -E -Q"EXEC sp_detachcb ..."
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
> news:17196A2A-FC3F-4B6C-AC32-BF41E842AB48@.microsoft.com...
> MSDE
> (excerpt
> or a
> on
>
>
|||> The problem with this is going to be the users password which Ken needs to
> recreate after restoring the db.
And he can't do:
osql -S(local)\instancename -E -Q"EXEC sp_password ..."
?
http://www.aspfaq.com/
(Reverse address to reply.)

moving MSDE database to SQL...

rI have an end-use that created a database on MSDE and is housing it on
his local computer. We want to move/copy it to our SQL server so it
can be backed up etc. What are the appropriate steps in doing so?
Thanks in advance,
KenIf they have both the same (and probably latest) service packs, the
compatibility is 100% and you don't have to take any special step (excerpt
maybe for authentification and login information). So a Backup/Restore or a
Detach/Reattach operation will be the easiest way.
S. L.
"Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
news:1CE3E73D-87E1-4058-BDAC-5D2FDB3984FE@.microsoft.com...
> rI have an end-use that created a database on MSDE and is housing it on
> his local computer. We want to move/copy it to our SQL server so it
> can be backed up etc. What are the appropriate steps in doing so?
> Thanks in advance,
> Ken
>|||Thanks for the reply, but I have no idea what the command lines are for MSDE
in order to backup etc. Where would I find those that I need in order to
backup and then restore to the SQL server?
Thanks,
Ken
"Sylvain Lafontaine" wrote:
> If they have both the same (and probably latest) service packs, the
> compatibility is 100% and you don't have to take any special step (excerpt
> maybe for authentification and login information). So a Backup/Restore or a
> Detach/Reattach operation will be the easiest way.
> S. L.
> "Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
> news:1CE3E73D-87E1-4058-BDAC-5D2FDB3984FE@.microsoft.com...
> > rI have an end-use that created a database on MSDE and is housing it on
> > his local computer. We want to move/copy it to our SQL server so it
> > can be backed up etc. What are the appropriate steps in doing so?
> >
> > Thanks in advance,
> >
> > Ken
> >
>
>|||Well, look up BACKUP DATABASE in Books Online, and to execute it against
MSDE, you can use
osql -S(local)\instancename -E -Q"BACKUP DATABASE ..."
or
osql -S(local)\instancename -E -Q"EXEC sp_detachcb ..."
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
news:17196A2A-FC3F-4B6C-AC32-BF41E842AB48@.microsoft.com...
> Thanks for the reply, but I have no idea what the command lines are for
MSDE
> in order to backup etc. Where would I find those that I need in order to
> backup and then restore to the SQL server?
> Thanks,
> Ken
> "Sylvain Lafontaine" wrote:
> > If they have both the same (and probably latest) service packs, the
> > compatibility is 100% and you don't have to take any special step
(excerpt
> > maybe for authentification and login information). So a Backup/Restore
or a
> > Detach/Reattach operation will be the easiest way.
> >
> > S. L.
> >
> > "Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
> > news:1CE3E73D-87E1-4058-BDAC-5D2FDB3984FE@.microsoft.com...
> > > rI have an end-use that created a database on MSDE and is housing it
on
> > > his local computer. We want to move/copy it to our SQL server so it
> > > can be backed up etc. What are the appropriate steps in doing so?
> > >
> > > Thanks in advance,
> > >
> > > Ken
> > >
> >
> >
> >|||Mr. Aaron,
The problem with this is going to be the users password which Ken needs to
recreate after restoring the db.
v/r
ktf
"Aaron [SQL Server MVP]" wrote:
> Well, look up BACKUP DATABASE in Books Online, and to execute it against
> MSDE, you can use
> osql -S(local)\instancename -E -Q"BACKUP DATABASE ..."
> or
> osql -S(local)\instancename -E -Q"EXEC sp_detachcb ..."
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
> news:17196A2A-FC3F-4B6C-AC32-BF41E842AB48@.microsoft.com...
> > Thanks for the reply, but I have no idea what the command lines are for
> MSDE
> > in order to backup etc. Where would I find those that I need in order to
> > backup and then restore to the SQL server?
> >
> > Thanks,
> >
> > Ken
> >
> > "Sylvain Lafontaine" wrote:
> >
> > > If they have both the same (and probably latest) service packs, the
> > > compatibility is 100% and you don't have to take any special step
> (excerpt
> > > maybe for authentification and login information). So a Backup/Restore
> or a
> > > Detach/Reattach operation will be the easiest way.
> > >
> > > S. L.
> > >
> > > "Ken Gardner" <KenGardner@.discussions.microsoft.com> wrote in message
> > > news:1CE3E73D-87E1-4058-BDAC-5D2FDB3984FE@.microsoft.com...
> > > > rI have an end-use that created a database on MSDE and is housing it
> on
> > > > his local computer. We want to move/copy it to our SQL server so it
> > > > can be backed up etc. What are the appropriate steps in doing so?
> > > >
> > > > Thanks in advance,
> > > >
> > > > Ken
> > > >
> > >
> > >
> > >
>
>|||> The problem with this is going to be the users password which Ken needs to
> recreate after restoring the db.
And he can't do:
osql -S(local)\instancename -E -Q"EXEC sp_password ..."
?
--
http://www.aspfaq.com/
(Reverse address to reply.)

moving ms SQL databases

I've created a web application with Visual Studio 2003. Uploading my application to the server is easy enough. I use the Copy Project utility from the Project selection on the menu bar.

I'm using a remote server-- and hosting account that supports ASP.NET and ms SQL. But now I want to copy my ms SQL database and can't find any way to copy my DB to the server.

What do the best way to to this?

Paul
Tucson, AZ

Ask your hoster. Some options are hoster specific.

The Enterprise Manager has a tool to copy databases between two servers. This is one way - but do you have the rights for this on the server?

Then there is attach/detach. Same issue.

As you are not interested in theoretical discussions what could be possible (if your hoster would not disallow it), ask your hoster.

Moving Logins from 6.5 to 2000

Hello all,
I need to move over all the users in 6.5 into 2000, but when i created the
script to create the logins, the passwords came in as null into the new 2000
db. How do I birng over the passwords from 6.5 into the new 2000? Any help
is appreciated. Thanks in advance.
--
New SQL Server DBAHi,
The method to transfer the logins with the password from SQL Server 6.5 to S
QL Server 2000 is
to upgrade the databases using "upgrade wizard". There is no documented way
to move logins with password using scripts.
But there is undocumented method method to do this. see the below link. I ha
ve never tried this.
http://groups-beta.google.com/group...99a55c7fbed5496
FOR SQL 7 and SQL 2000 servers you can read the below article to transfer lo
gins.
http://support.microsoft.com/?kbid=246133
Thanks
Hari
SQL Server MVP
____________________________________
Newbie Wrote:
Hello all,
I need to move over all the users in 6.5 into 2000, but when i created the
script to create the logins, the passwords came in as null into the new 2000
db. How do I birng over the passwords from 6.5 into the new 2000? Any help
is appreciated. Thanks in advance.
--
New SQL Server DBA
Sent via SreeSharp NewsReader http://www.SreeSharp.com|||Thanks for the advise. How do I get this upgrade wizard? Thanks.
"Hari Prasad" wrote:

> Hi,
> The method to transfer the logins with the password from SQL Server 6.5 to
SQL Server 2000 is
> to upgrade the databases using "upgrade wizard". There is no documented wa
y to move logins with password using scripts.
> But there is undocumented method method to do this. see the below link. I
have never tried this.
> http://groups-beta.google.com/group...99a55c7fbed5496
>
> FOR SQL 7 and SQL 2000 servers you can read the below article to transfer
logins.
> http://support.microsoft.com/?kbid=246133
>
> Thanks
> Hari
> SQL Server MVP
> ____________________________________
> Newbie Wrote:
> Hello all,
> I need to move over all the users in 6.5 into 2000, but when i created the
> script to create the logins, the passwords came in as null into the new 20
00
> db. How do I birng over the passwords from 6.5 into the new 2000? Any he
lp
> is appreciated. Thanks in advance.
> --
> New SQL Server DBA
>
>
>
> Sent via SreeSharp NewsReader http://www.SreeSharp.com
>|||Hi,
This comes automatically when you install SQL Server 2000. After
installation see the SQL Server program groups.
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:C222A6D0-0AF9-4F6E-88D8-AA280AE1E2CF@.microsoft.com...[vbcol=seagreen]
> Thanks for the advise. How do I get this upgrade wizard? Thanks.
> "Hari Prasad" wrote:
>

Moving Logins from 6.5 to 2000

Hello all,
I need to move over all the users in 6.5 into 2000, but when i created the
script to create the logins, the passwords came in as null into the new 2000
db. How do I birng over the passwords from 6.5 into the new 2000? Any help
is appreciated. Thanks in advance.
New SQL Server DBA
Hi,
The method to transfer the logins with the password from SQL Server 6.5 to SQL Server 2000 is
to upgrade the databases using "upgrade wizard". There is no documented way to move logins with password using scripts.
But there is undocumented method method to do this. see the below link. I have never tried this.
http://groups-beta.google.com/group/...9a55c7fbed5496
FOR SQL 7 and SQL 2000 servers you can read the below article to transfer logins.
http://support.microsoft.com/?kbid=246133
Thanks
Hari
SQL Server MVP
____________________________________
Newbie Wrote:
Hello all,
I need to move over all the users in 6.5 into 2000, but when i created the
script to create the logins, the passwords came in as null into the new 2000
db. How do I birng over the passwords from 6.5 into the new 2000? Any help
is appreciated. Thanks in advance.
New SQL Server DBA
Sent via SreeSharp NewsReader http://www.SreeSharp.com
|||Thanks for the advise. How do I get this upgrade wizard? Thanks.
"Hari Prasad" wrote:

> Hi,
> The method to transfer the logins with the password from SQL Server 6.5 to SQL Server 2000 is
> to upgrade the databases using "upgrade wizard". There is no documented way to move logins with password using scripts.
> But there is undocumented method method to do this. see the below link. I have never tried this.
> http://groups-beta.google.com/group/...9a55c7fbed5496
>
> FOR SQL 7 and SQL 2000 servers you can read the below article to transfer logins.
> http://support.microsoft.com/?kbid=246133
>
> Thanks
> Hari
> SQL Server MVP
> ____________________________________
> Newbie Wrote:
> Hello all,
> I need to move over all the users in 6.5 into 2000, but when i created the
> script to create the logins, the passwords came in as null into the new 2000
> db. How do I birng over the passwords from 6.5 into the new 2000? Any help
> is appreciated. Thanks in advance.
> --
> New SQL Server DBA
>
>
>
> Sent via SreeSharp NewsReader http://www.SreeSharp.com
>
|||Hi,
This comes automatically when you install SQL Server 2000. After
installation see the SQL Server program groups.
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:C222A6D0-0AF9-4F6E-88D8-AA280AE1E2CF@.microsoft.com...[vbcol=seagreen]
> Thanks for the advise. How do I get this upgrade wizard? Thanks.
> "Hari Prasad" wrote: