Moving an SQL 2000 database

I am going to be performing an upgrade on a 2000 server. I
will be adding two drives (mirrored). The server is
running 'ACT! Web' which uses SQL. I would like to move
the SQL database to the newly created volume, I am very
green when it comes to SQL, so I don't really know where
to begin.
If anyone hase some suggestions please post.Look up sp_detach_db and sp_attach_db in BOL.
"Steve" <deviller@.starbeam.ca> wrote in message
news:7b9001c402e7$cc7cd310$a001280a@.phx.gbl...
> I am going to be performing an upgrade on a 2000 server. I
> will be adding two drives (mirrored). The server is
> running 'ACT! Web' which uses SQL. I would like to move
> the SQL database to the newly created volume, I am very
> green when it comes to SQL, so I don't really know where
> to begin.
> If anyone hase some suggestions please post.|||And for some more reading, KB article 224071.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O2yXqhuAEHA.3184@.TK2MSFTNGP09.phx.gbl...
> Look up sp_detach_db and sp_attach_db in BOL.
> "Steve" <deviller@.starbeam.ca> wrote in message
> news:7b9001c402e7$cc7cd310$a001280a@.phx.gbl...
> > I am going to be performing an upgrade on a 2000 server. I
> > will be adding two drives (mirrored). The server is
> > running 'ACT! Web' which uses SQL. I would like to move
> > the SQL database to the newly created volume, I am very
> > green when it comes to SQL, so I don't really know where
> > to begin.
> >
> > If anyone hase some suggestions please post.
>|||Steve wrote:
> I am going to be performing an upgrade on a 2000 server. I
> will be adding two drives (mirrored). The server is
> running 'ACT! Web' which uses SQL. I would like to move
> the SQL database to the newly created volume, I am very
> green when it comes to SQL, so I don't really know where
> to begin.
> If anyone hase some suggestions please post.
Look into Detach/Attach. See:
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSqlLogins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Moving an SQL 2000 database

I am going to be performing an upgrade on a 2000 server. I
will be adding two drives (mirrored). The server is
running 'ACT! Web' which uses SQL. I would like to move
the SQL database to the newly created volume, I am very
green when it comes to SQL, so I don't really know where
to begin.
If anyone hase some suggestions please post.Look up sp_detach_db and sp_attach_db in BOL.
"Steve" <deviller@.starbeam.ca> wrote in message
news:7b9001c402e7$cc7cd310$a001280a@.phx.gbl...
> I am going to be performing an upgrade on a 2000 server. I
> will be adding two drives (mirrored). The server is
> running 'ACT! Web' which uses SQL. I would like to move
> the SQL database to the newly created volume, I am very
> green when it comes to SQL, so I don't really know where
> to begin.
> If anyone hase some suggestions please post.|||And for some more reading, KB article 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O2yXqhuAEHA.3184@.TK2MSFTNGP09.phx.gbl...
> Look up sp_detach_db and sp_attach_db in BOL.
> "Steve" <deviller@.starbeam.ca> wrote in message
> news:7b9001c402e7$cc7cd310$a001280a@.phx.gbl...
>|||Steve wrote:
> I am going to be performing an upgrade on a 2000 server. I
> will be adding two drives (mirrored). The server is
> running 'ACT! Web' which uses SQL. I would like to move
> the SQL database to the newly created volume, I am very
> green when it comes to SQL, so I don't really know where
> to begin.
> If anyone hase some suggestions please post.
Look into Detach/Attach. See:
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSqlLogins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scr...sp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Moving a DTS to another server

Hi I'm very new to this but what I'm looking to do should'nt be too difficult although I've tried to no avail.

I'm looking to deploy a web site that uses SQL2000 to another server. Im trying to find out how to move the database to that server. Ideally I would like to be able to save it to CD for safety but its not like old access where you save the mdb file. I need to save the whole 'DTS' package, is that what its called? stored procedures, triggers the lot.

I tried the wizards but just got in a mess.

ThanksDTS stands for Data Transformation Services. It's a part of Sql Server that is useful for copying tables and other database objects, importing and exporting data, and doing other ETL (extract, transform, load) functions. While you may want to use a DTS package to move your database it doesn't by itself represent the set of tables, views, stored procedures, and other objects that make up a database.

Options for copying or moving a database include:

You can detach a database, copy the mdf and log files to another server, and then re-attach them on the other server. You need to detach from the current db before you copy because otherwise it will say the files are in use.

You can backup the database, copy the backup to the other server, and then restore it there.

If can connect to both database servers from Enterprise Manager you can use DTS to copy all the database objects (tables, views, stored procedures, etc.) to the other server. There is a wizard within Enterprise Manager to help you do this. Right click on the database, select All Tasks->Export Data. After selecting the source and destination servers, select "Copy objects and data between SQL Server databases". It will prompt you for various details about what to copy and whether you want to save it as a DTS package.|||Thanks for that, can I ask how do I go about detaching the database prior to saving the mdf file and also could you tell me where this mdf file resides?

Thanks again|||You can detach a database this way (just make sure that it's not currently in use):

sp_detach_db 'MyDB'

By default, databases are located at c:\Program Files\Microsoft SQL Server\MSSQL\Data and the logs are at c:\Program Files\Microsoft SQL Server\MSSQL\LOG. But you can change the default for a server or for individual databases, and you can scatter filegroups all over the place.

If you have Enterprise Manager, you can right-click on the database name, select Properties, and look under the Data Files and Transaction Log tabs to see where the files are located. If not, you can get the information using osql.

Enough information?

Don|||To detach a database:

1. Shrink it first to make the file sizes smaller. (This is sort of like compacting and repairing an Access file.) Select the database in Enterprise Manager, right-click, select All Tasks->Shrink Database... Click OK on the dialog that comes up.

2. Backup the database just in case: All Tasks->Backup Database...

3. Detach database: All Tasks->Detach Database... (If any process is still using the db it won't let you detach until they are cleared. If the database is being used for replication it also won't let you detach it).

4. File location. They are probably in C:\Program Files\Microsoft SQL Server\MSSQL\Data. Look for two files names: YourDbName.MDF and YourDbName.LDF. They should have very recent timestamps for when you detached them. If the files are not there just search the hard drive for one of those names. The db files don't have to be in that folder but it's the default if not otherwise specfied. If you are using MSDE then the path will be slightly different.

Move Web Services Front-End

Does anyone know the steps to move the SQL RS web services front-end? The
back-end database will stay in its current location.
Thanks,
DougTake a look at this article:
http://www.sqljunkies.com/HowTo/525B575A-7F61-483A-AC8F-FEC700C34674.scuk
--
erik perez
www.solien.com
"Doug Lott" <dlott@.rmahomeservices.com> wrote in message
news:OMDdED0lEHA.712@.TK2MSFTNGP09.phx.gbl...
> Does anyone know the steps to move the SQL RS web services front-end? The
> back-end database will stay in its current location.
> Thanks,
> Doug
>|||This article does a great of explaining how to move the web service to
another website on the same server, but I need to move the whole front-end
installation including the windows service to another server entirely.
Thanks,
Doug
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:eTO52F0lEHA.1672@.TK2MSFTNGP14.phx.gbl...
> Take a look at this article:
> http://www.sqljunkies.com/HowTo/525B575A-7F61-483A-AC8F-FEC700C34674.scuk
> --
> erik perez
> www.solien.com
> "Doug Lott" <dlott@.rmahomeservices.com> wrote in message
> news:OMDdED0lEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Does anyone know the steps to move the SQL RS web services front-end?
The
> > back-end database will stay in its current location.
> >
> > Thanks,
> >
> > Doug
> >
> >
>|||I would imagine it would probably be best to use the RS Installer to install
Reporting Services on the new server just like you installed the original
server. Make sure you backup your ReportServer and ReportServerTempDB
databases, and also make copies of your configuration files and encryption
key in case you need them. Also, you should note that installing Reporting
Services on a server separate from where the SQL Server database resides
will require you have an additional SQL Server license.
--
erik perez
www.solien.com
"Doug Lott" <dlott@.rmahomeservices.com> wrote in message
news:eY4GMQ0lEHA.3544@.TK2MSFTNGP15.phx.gbl...
> This article does a great of explaining how to move the web service to
> another website on the same server, but I need to move the whole front-end
> installation including the windows service to another server entirely.
> Thanks,
> Doug
> "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> news:eTO52F0lEHA.1672@.TK2MSFTNGP14.phx.gbl...
> > Take a look at this article:
> >
http://www.sqljunkies.com/HowTo/525B575A-7F61-483A-AC8F-FEC700C34674.scuk
> >
> > --
> > erik perez
> > www.solien.com
> >
> > "Doug Lott" <dlott@.rmahomeservices.com> wrote in message
> > news:OMDdED0lEHA.712@.TK2MSFTNGP09.phx.gbl...
> > > Does anyone know the steps to move the SQL RS web services front-end?
> The
> > > back-end database will stay in its current location.
> > >
> > > Thanks,
> > >
> > > Doug
> > >
> > >
> >
> >
>|||Erik is correct. This is the easiest way to move the report server. Trying
to do it manually is possible but not trivial. The biggest gotcha would be
if you point to the existing database during setup. If you are planning on
doing this then setup will think you are joining a web farm, as long as the
original machine is still running then everything should be fine, if it is
not then you will fail to activate. The other option is to install the new
RS pointing to a new DB. Once the install is complete use RSConfig.exe to
point to the old database. You will also need to run rskeymgmt to extract
the key from the old RS and import it into the new RS.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:eOdHdZ0lEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I would imagine it would probably be best to use the RS Installer to
install
> Reporting Services on the new server just like you installed the original
> server. Make sure you backup your ReportServer and ReportServerTempDB
> databases, and also make copies of your configuration files and encryption
> key in case you need them. Also, you should note that installing
Reporting
> Services on a server separate from where the SQL Server database resides
> will require you have an additional SQL Server license.
> --
> erik perez
> www.solien.com
> "Doug Lott" <dlott@.rmahomeservices.com> wrote in message
> news:eY4GMQ0lEHA.3544@.TK2MSFTNGP15.phx.gbl...
> > This article does a great of explaining how to move the web service to
> > another website on the same server, but I need to move the whole
front-end
> > installation including the windows service to another server entirely.
> >
> > Thanks,
> >
> > Doug
> >
> > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> > news:eTO52F0lEHA.1672@.TK2MSFTNGP14.phx.gbl...
> > > Take a look at this article:
> > >
> http://www.sqljunkies.com/HowTo/525B575A-7F61-483A-AC8F-FEC700C34674.scuk
> > >
> > > --
> > > erik perez
> > > www.solien.com
> > >
> > > "Doug Lott" <dlott@.rmahomeservices.com> wrote in message
> > > news:OMDdED0lEHA.712@.TK2MSFTNGP09.phx.gbl...
> > > > Does anyone know the steps to move the SQL RS web services
front-end?
> > The
> > > > back-end database will stay in its current location.
> > > >
> > > > Thanks,
> > > >
> > > > Doug
> > > >
> > > >
> > >
> > >
> >
> >
>

Move Reporting Services Web Installation to another Web Server

Move Reporting Services Web Installation to another Web ServerI assume this is RS 2000. You may want to check this KB article (in
particular the second section of the article):
http://support.microsoft.com/default.aspx?scid=842425
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Ennamorato" <PaulEnnamorato@.discussions.microsoft.com> wrote in
message news:2C6E33F4-A377-4803-9A8B-B46CBE7E5662@.microsoft.com...
>

move report server to new machine

Can someone tell me how I can move report server web sites into a new machine without reinstall it?

I like to keep the current report server database intact, it’s residing on a third server.

I copied the report server file folders including bin (dlls) into the new machine and set up in the new machine the Reportserver and Reports web sites pointing to these two folders while keeping the report server database still residing on the third machine, but the new site did not work, I got "Event source Report Server does not exist. (rsEventLogSourceNotFound)" error

Any advice will be greatly appreciated.

Thanks

Jerry

Well, the short answer is you can't. :) You'll need to install RS on the new machne. Licensing requires that if your RS and SQL are on different machines, you will need a seperate license for each.|||

Thanks James.

Well my next quest is: when I install RS on new machine with the license, I think I should install the new RS database on the same SQL server/database as the old RS server does, then will the newly installed RS DB overwrite the existing RS DB? or it will just makes use it without change anything in it?

Thanks a lot!

Jerry

|||It will never overwrite your RS database. Since you are doing a RS only install, you will only be able to use the "files-only" option (you will see this in the installation wizard). Post-install, you need to use the configuration tool to either create the RS databases, or set RS to connect to your old databases. The decision is up to you, but you will need to do it manually.|||Thanks a lot James, I will try.

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
MeIf you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
Me
If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.
|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
MeIf you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

MS Reporting Services 2000 Error ?

hi

when i try to use Reporting Services to render a report from aspx page

(web app) (vb.net) by usingReportingServices.dll (by add referennces)

i am not using (web referennces)

i get this erorr :

==================

System.Web.Services.Protocols.SoapException: The permissions granted to user 'NT AUTHORITY\NETWORK SERVICE' are insufficient for performing this operation. > Microsoft.ReportingServices.Diagnostics.Utilities.RSException .

note:

=====

- i have windows 2003 server ent.

- sql 2000 sp3 .

- MS Reporting Services 2000 sp2 .

- i am login as administrator .

pls tell me what i can do ?

thank you all

Hi,

as you did not post any code, I can give you a common suggestion. If you use impersonation, you will have to use the credentials of the actual user and add it to the instantiated proxy of the webservice. This will make the call to the webservice under the credentials and the permissions of the impersonated user.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Correct me if I'm wrong, but if you don't do this then it uses the identity of the app pool under which the website runs which by default is network service for the default app pool|||yep.

moving indexes onto another partition

Hi
From several weeks I suffer poor performance of my SQL Serv (as backend to
the web application).
I heard that sometimes splitting database to different hard disk could raise
performance. But in my case I have got only one HDD devided to 2 partition -
my question is: if it is good idea to migrate tables or indexes to anther
partition - does it make sens?
Or maybe thare is another solution to my problem - connected mostly with HDD
queue legth. So I could say that all the delay in answers are connected with
poor HDD performance.
Best Regards
Darek T.It does not help to move the indexes to the other partition: it is still the
same physical disk drive.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dariusz Tomon" wrote:

> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could rai
se
> performance. But in my case I have got only one HDD devided to 2 partition
-
> my question is: if it is good idea to migrate tables or indexes to anther
> partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with H
DD
> queue legth. So I could say that all the delay in answers are connected wi
th
> poor HDD performance.
>
> Best Regards
> Darek T.
>
>|||Use SQL profiler to run a trace and identify the query that is slow. The
vast majority of performance problems can be fixed by rewriting queries or
adding indexes.
Beyond that, you can only add physical drives to distribute the read/writes.
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:O$Q3lgRdGHA.1656@.TK2MSFTNGP02.phx.gbl...
> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could
> raise performance. But in my case I have got only one HDD devided to 2
> partition - my question is: if it is good idea to migrate tables or
> indexes to anther partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with
> HDD queue legth. So I could say that all the delay in answers are
> connected with poor HDD performance.
>
> Best Regards
> Darek T.
>

moving indexes onto another partition

Hi
From several weeks I suffer poor performance of my SQL Serv (as backend to
the web application).
I heard that sometimes splitting database to different hard disk could raise
performance. But in my case I have got only one HDD devided to 2 partition -
my question is: if it is good idea to migrate tables or indexes to anther
partition - does it make sens?
Or maybe thare is another solution to my problem - connected mostly with HDD
queue legth. So I could say that all the delay in answers are connected with
poor HDD performance.
Best Regards
Darek T.It does not help to move the indexes to the other partition: it is still the
same physical disk drive.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dariusz Tomon" wrote:
> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could raise
> performance. But in my case I have got only one HDD devided to 2 partition -
> my question is: if it is good idea to migrate tables or indexes to anther
> partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with HDD
> queue legth. So I could say that all the delay in answers are connected with
> poor HDD performance.
>
> Best Regards
> Darek T.
>
>|||Use SQL profiler to run a trace and identify the query that is slow. The
vast majority of performance problems can be fixed by rewriting queries or
adding indexes.
Beyond that, you can only add physical drives to distribute the read/writes.
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:O$Q3lgRdGHA.1656@.TK2MSFTNGP02.phx.gbl...
> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could
> raise performance. But in my case I have got only one HDD devided to 2
> partition - my question is: if it is good idea to migrate tables or
> indexes to anther partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with
> HDD queue legth. So I could say that all the delay in answers are
> connected with poor HDD performance.
>
> Best Regards
> Darek T.
>

Move data from SqlDataSource to a TextBox

I have a DataReader on a web page:<asp:SqlDataSourceID="ChangeInfoRecord"runat="server"ConnectionString........

I want to display the value of one of the returned field in a text box. What is the correct syntax? I tried

RolledFromDisplay.Text = ChangeInfoRecord("RolledFrom")

but that isn't it.

You can do something like this:

DataView dv = SqlDataSource1.Select(DataSourceSelectArguments.Empty)as DataView;TextBox1.Text = dv[0]["Name"].ToString();