Moved SQL to the new box

Hi everybody,

I re-built SQl server 2000 on the new box. Attached and detached all DBs (master, msdb, users DBs) and moved them to the new paths (E:\mssql). Everything is fine.

The questions are:
1. The 'filename' in the master.sysdatabases for 'master' DB is still pointed to the old location (F:\) even physicaly files are on E:\ drive;

2. The 'phyname' in the master.sysdevices for all devices (DBs), including master, msdb, model are still shows the old location (F:\);
At list I will get a wrong reports of the files location.

it could be other places in the system tables that keep old information.
How and where I can correct the information?

3. All jobs, bakups, etc. that creates output files still pointed to the old locations (let say to the F:\sql2000\). F:\ drive is not exists anymore.
I will recreate all jobs and main plans to specify correct path.
But how can I change the DEFAULT locations for all backups, logs, etc. ?

Thank you,
LYOne way you could do it (sorry but its a bit long winded), is to create your dbs on the new server and restore the dbs you wish to move, using the "with move" clause on the resore database command.

By virtue of the fact you have created the dbs on the new server your paths should be OK, and not inherited by the dbs you sp_attach_db'ed

Hope this helps|||In the rebuild situation, I generally will not restore or reattach system databases, only the user databases. Because my past experience of having numerous troubles with the master after restoring to a new machine. Scheduled tasks and DTS can be saved and transfer to the new machine.

Good luck!|||Originally posted by dbabren
One way you could do it (sorry but its a bit long winded), is to create your dbs on the new server and restore the dbs you wish to move, using the "with move" clause on the resore database command.

By virtue of the fact you have created the dbs on the new server your paths should be OK, and not inherited by the dbs you sp_attach_db'ed

Hope this helps

=====

All user DB's are fine.
The only incorrect information for master.sysdatabases.filename (for master database) and master.sysdevices (for all devices).

Thank you.
LYsql

Moved master db and now cannot start SQL

After changing the startup parameters and moving the database we are unable
to start SQL. The error message we are getting in the application log is
that the initconfig: (error 5) access denied to g:\sqldata.
HI,
How u moved the database and what parameter u have changed.
from
killer
Ray Skweres wrote:
> After changing the startup parameters and moving the database we are unable
> to start SQL. The error message we are getting in the application log is
> that the initconfig: (error 5) access denied to g:\sqldata.
|||Hi
This article descibes how to move your master database
http://support.microsoft.com/kb/224071/EN-US/
You may want to try starting SQL Server from the command prompt
http://msdn.microsoft.com/library/de...servr_4apa.asp
with the correct parameters (possibly with -f also!) and also making sure
that the permissions for the directories being used are correct.
You can not use a mapped network drive for your database files.
John
"Ray Skweres" wrote:

> After changing the startup parameters and moving the database we are unable
> to start SQL. The error message we are getting in the application log is
> that the initconfig: (error 5) access denied to g:\sqldata.
|||Error 5 is access denied. Make sure that the SQL Server service account has permissions to the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ray Skweres" <RaySkweres@.discussions.microsoft.com> wrote in message
news:2053CEEC-32D3-4840-9898-D59433C29617@.microsoft.com...
> After changing the startup parameters and moving the database we are unable
> to start SQL. The error message we are getting in the application log is
> that the initconfig: (error 5) access denied to g:\sqldata.
|||Moved it based on Microsoft articel changed startup parameters and only moved
the master.mdf so I only changed that parameter.
"doller" wrote:

> HI,
> How u moved the database and what parameter u have changed.
> from
> killer
>
> Ray Skweres wrote:
>
|||Used this article to move the database also tried to start it from a command
prompt and would not start. Also tried to change the startup parameters back
though command prompt and did not work. I am not using a mapped network
drive. Permissions are fine on the file. Even went into registry and tried
to change and then start sql still did not start and then when I went back
into the registry and it was back to the old setting.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This article descibes how to move your master database
> http://support.microsoft.com/kb/224071/EN-US/
> You may want to try starting SQL Server from the command prompt
> http://msdn.microsoft.com/library/de...servr_4apa.asp
> with the correct parameters (possibly with -f also!) and also making sure
> that the permissions for the directories being used are correct.
> You can not use a mapped network drive for your database files.
> John
> "Ray Skweres" wrote:
|||Hi
Are you using the -d and -l parameters when starting on the command prompt?
Is there any privacy or anti-virus software running?
You may want to try reverting back to the previous location and see if it
starts.
If have backed up all your databases then you may want to try un-installing
and re-installing. You should be able to attach the databases from the
mdf/ldf files, but you will need to re-instate logins and jobs.
John
"Ray Skweres" wrote:
[vbcol=seagreen]
> Used this article to move the database also tried to start it from a command
> prompt and would not start. Also tried to change the startup parameters back
> though command prompt and did not work. I am not using a mapped network
> drive. Permissions are fine on the file. Even went into registry and tried
> to change and then start sql still did not start and then when I went back
> into the registry and it was back to the old setting.
> "John Bell" wrote:
|||Whether your the service account you are starting has access to the
g:\sqldata.
Thanks
Ajay Rengunthwar
MCDBA, MCAD
"Ray Skweres" <RaySkweres@.discussions.microsoft.com> wrote in message
news:19FD3DAA-35C5-482E-B0CD-FA8068B7FA71@.microsoft.com...
> Moved it based on Microsoft articel changed startup parameters and only
moved[vbcol=seagreen]
> the master.mdf so I only changed that parameter.
> "doller" wrote:
unable[vbcol=seagreen]
log is[vbcol=seagreen]
|||Hi
Another thought, have you checked for disc corruption?
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BFC7ACBD-19F4-4714-919B-C7774A17FFC7@.microsoft.com...[vbcol=seagreen]
> Hi
> Are you using the -d and -l parameters when starting on the command
> prompt?
> Is there any privacy or anti-virus software running?
> You may want to try reverting back to the previous location and see if it
> starts.
> If have backed up all your databases then you may want to try
> un-installing
> and re-installing. You should be able to attach the databases from the
> mdf/ldf files, but you will need to re-instate logins and jobs.
>
> John
> "Ray Skweres" wrote:

Moved master db and now cannot start SQL

After changing the startup parameters and moving the database we are unable
to start SQL. The error message we are getting in the application log is
that the initconfig: (error 5) access denied to g:\sqldata.HI,
How u moved the database and what parameter u have changed.
from
killer
Ray Skweres wrote:
> After changing the startup parameters and moving the database we are unabl
e
> to start SQL. The error message we are getting in the application log is
> that the initconfig: (error 5) access denied to g:\sqldata.|||Hi
This article descibes how to move your master database
http://support.microsoft.com/kb/224071/EN-US/
You may want to try starting SQL Server from the command prompt
http://msdn.microsoft.com/library/d...>
rvr_4apa.asp
with the correct parameters (possibly with -f also!) and also making sure
that the permissions for the directories being used are correct.
You can not use a mapped network drive for your database files.
John
"Ray Skweres" wrote:

> After changing the startup parameters and moving the database we are unabl
e
> to start SQL. The error message we are getting in the application log is
> that the initconfig: (error 5) access denied to g:\sqldata.|||Error 5 is access denied. Make sure that the SQL Server service account has
permissions to the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ray Skweres" <RaySkweres@.discussions.microsoft.com> wrote in message
news:2053CEEC-32D3-4840-9898-D59433C29617@.microsoft.com...
> After changing the startup parameters and moving the database we are unabl
e
> to start SQL. The error message we are getting in the application log is
> that the initconfig: (error 5) access denied to g:\sqldata.|||Moved it based on Microsoft articel changed startup parameters and only move
d
the master.mdf so I only changed that parameter.
"doller" wrote:

> HI,
> How u moved the database and what parameter u have changed.
> from
> killer
>
> Ray Skweres wrote:
>|||Used this article to move the database also tried to start it from a command
prompt and would not start. Also tried to change the startup parameters bac
k
though command prompt and did not work. I am not using a mapped network
drive. Permissions are fine on the file. Even went into registry and tried
to change and then start sql still did not start and then when I went back
into the registry and it was back to the old setting.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This article descibes how to move your master database
> http://support.microsoft.com/kb/224071/EN-US/
> You may want to try starting SQL Server from the command prompt
> http://msdn.microsoft.com/library/d...
servr_4apa.asp
> with the correct parameters (possibly with -f also!) and also making sure
> that the permissions for the directories being used are correct.
> You can not use a mapped network drive for your database files.
> John
> "Ray Skweres" wrote:
>|||Hi
Are you using the -d and -l parameters when starting on the command prompt?
Is there any privacy or anti-virus software running?
You may want to try reverting back to the previous location and see if it
starts.
If have backed up all your databases then you may want to try un-installing
and re-installing. You should be able to attach the databases from the
mdf/ldf files, but you will need to re-instate logins and jobs.
John
"Ray Skweres" wrote:
[vbcol=seagreen]
> Used this article to move the database also tried to start it from a comma
nd
> prompt and would not start. Also tried to change the startup parameters b
ack
> though command prompt and did not work. I am not using a mapped network
> drive. Permissions are fine on the file. Even went into registry and trie
d
> to change and then start sql still did not start and then when I went back
> into the registry and it was back to the old setting.
> "John Bell" wrote:
>|||Whether your the service account you are starting has access to the
g:\sqldata.
Thanks
Ajay Rengunthwar
MCDBA, MCAD
"Ray Skweres" <RaySkweres@.discussions.microsoft.com> wrote in message
news:19FD3DAA-35C5-482E-B0CD-FA8068B7FA71@.microsoft.com...
> Moved it based on Microsoft articel changed startup parameters and only
moved[vbcol=seagreen]
> the master.mdf so I only changed that parameter.
> "doller" wrote:
>
unable[vbcol=seagreen]
log is[vbcol=seagreen]|||Hi
Another thought, have you checked for disc corruption?
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BFC7ACBD-19F4-4714-919B-C7774A17FFC7@.microsoft.com...[vbcol=seagreen]
> Hi
> Are you using the -d and -l parameters when starting on the command
> prompt?
> Is there any privacy or anti-virus software running?
> You may want to try reverting back to the previous location and see if it
> starts.
> If have backed up all your databases then you may want to try
> un-installing
> and re-installing. You should be able to attach the databases from the
> mdf/ldf files, but you will need to re-instate logins and jobs.
>
> John
> "Ray Skweres" wrote:
>

Moved master db and now cannot start SQL

After changing the startup parameters and moving the database we are unable
to start SQL. The error message we are getting in the application log is
that the initconfig: (error 5) access denied to g:\sqldata.HI,
How u moved the database and what parameter u have changed.
from
killer
Ray Skweres wrote:
> After changing the startup parameters and moving the database we are unable
> to start SQL. The error message we are getting in the application log is
> that the initconfig: (error 5) access denied to g:\sqldata.|||Hi
This article descibes how to move your master database
http://support.microsoft.com/kb/224071/EN-US/
You may want to try starting SQL Server from the command prompt
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_sqlservr_4apa.asp
with the correct parameters (possibly with -f also!) and also making sure
that the permissions for the directories being used are correct.
You can not use a mapped network drive for your database files.
John
"Ray Skweres" wrote:
> After changing the startup parameters and moving the database we are unable
> to start SQL. The error message we are getting in the application log is
> that the initconfig: (error 5) access denied to g:\sqldata.|||Error 5 is access denied. Make sure that the SQL Server service account has permissions to the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ray Skweres" <RaySkweres@.discussions.microsoft.com> wrote in message
news:2053CEEC-32D3-4840-9898-D59433C29617@.microsoft.com...
> After changing the startup parameters and moving the database we are unable
> to start SQL. The error message we are getting in the application log is
> that the initconfig: (error 5) access denied to g:\sqldata.|||Moved it based on Microsoft articel changed startup parameters and only moved
the master.mdf so I only changed that parameter.
"doller" wrote:
> HI,
> How u moved the database and what parameter u have changed.
> from
> killer
>
> Ray Skweres wrote:
> > After changing the startup parameters and moving the database we are unable
> > to start SQL. The error message we are getting in the application log is
> > that the initconfig: (error 5) access denied to g:\sqldata.
>|||Used this article to move the database also tried to start it from a command
prompt and would not start. Also tried to change the startup parameters back
though command prompt and did not work. I am not using a mapped network
drive. Permissions are fine on the file. Even went into registry and tried
to change and then start sql still did not start and then when I went back
into the registry and it was back to the old setting.
"John Bell" wrote:
> Hi
> This article descibes how to move your master database
> http://support.microsoft.com/kb/224071/EN-US/
> You may want to try starting SQL Server from the command prompt
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_sqlservr_4apa.asp
> with the correct parameters (possibly with -f also!) and also making sure
> that the permissions for the directories being used are correct.
> You can not use a mapped network drive for your database files.
> John
> "Ray Skweres" wrote:
> > After changing the startup parameters and moving the database we are unable
> > to start SQL. The error message we are getting in the application log is
> > that the initconfig: (error 5) access denied to g:\sqldata.|||Hi
Are you using the -d and -l parameters when starting on the command prompt?
Is there any privacy or anti-virus software running?
You may want to try reverting back to the previous location and see if it
starts.
If have backed up all your databases then you may want to try un-installing
and re-installing. You should be able to attach the databases from the
mdf/ldf files, but you will need to re-instate logins and jobs.
John
"Ray Skweres" wrote:
> Used this article to move the database also tried to start it from a command
> prompt and would not start. Also tried to change the startup parameters back
> though command prompt and did not work. I am not using a mapped network
> drive. Permissions are fine on the file. Even went into registry and tried
> to change and then start sql still did not start and then when I went back
> into the registry and it was back to the old setting.
> "John Bell" wrote:
> > Hi
> >
> > This article descibes how to move your master database
> > http://support.microsoft.com/kb/224071/EN-US/
> >
> > You may want to try starting SQL Server from the command prompt
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_sqlservr_4apa.asp
> > with the correct parameters (possibly with -f also!) and also making sure
> > that the permissions for the directories being used are correct.
> >
> > You can not use a mapped network drive for your database files.
> >
> > John
> >
> > "Ray Skweres" wrote:
> >
> > > After changing the startup parameters and moving the database we are unable
> > > to start SQL. The error message we are getting in the application log is
> > > that the initconfig: (error 5) access denied to g:\sqldata.|||Whether your the service account you are starting has access to the
g:\sqldata.
Thanks
Ajay Rengunthwar
MCDBA, MCAD
"Ray Skweres" <RaySkweres@.discussions.microsoft.com> wrote in message
news:19FD3DAA-35C5-482E-B0CD-FA8068B7FA71@.microsoft.com...
> Moved it based on Microsoft articel changed startup parameters and only
moved
> the master.mdf so I only changed that parameter.
> "doller" wrote:
> > HI,
> > How u moved the database and what parameter u have changed.
> >
> > from
> > killer
> >
> >
> > Ray Skweres wrote:
> > > After changing the startup parameters and moving the database we are
unable
> > > to start SQL. The error message we are getting in the application
log is
> > > that the initconfig: (error 5) access denied to g:\sqldata.
> >
> >|||Hi
Another thought, have you checked for disc corruption?
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BFC7ACBD-19F4-4714-919B-C7774A17FFC7@.microsoft.com...
> Hi
> Are you using the -d and -l parameters when starting on the command
> prompt?
> Is there any privacy or anti-virus software running?
> You may want to try reverting back to the previous location and see if it
> starts.
> If have backed up all your databases then you may want to try
> un-installing
> and re-installing. You should be able to attach the databases from the
> mdf/ldf files, but you will need to re-instate logins and jobs.
>
> John
> "Ray Skweres" wrote:
>> Used this article to move the database also tried to start it from a
>> command
>> prompt and would not start. Also tried to change the startup parameters
>> back
>> though command prompt and did not work. I am not using a mapped network
>> drive. Permissions are fine on the file. Even went into registry and
>> tried
>> to change and then start sql still did not start and then when I went
>> back
>> into the registry and it was back to the old setting.
>> "John Bell" wrote:
>> > Hi
>> >
>> > This article descibes how to move your master database
>> > http://support.microsoft.com/kb/224071/EN-US/
>> >
>> > You may want to try starting SQL Server from the command prompt
>> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_sqlservr_4apa.asp
>> > with the correct parameters (possibly with -f also!) and also making
>> > sure
>> > that the permissions for the directories being used are correct.
>> >
>> > You can not use a mapped network drive for your database files.
>> >
>> > John
>> >
>> > "Ray Skweres" wrote:
>> >
>> > > After changing the startup parameters and moving the database we are
>> > > unable
>> > > to start SQL. The error message we are getting in the application
>> > > log is
>> > > that the initconfig: (error 5) access denied to g:\sqldata.

Move transaction logs in SQL cluster

Hi
I need to move the transaction logs for all the sql db's (including master,
model and user db's etc) to a seperate drive. Does anyone have any experience
doing this in a sql cluster?
Thanks
Parky
Parky wrote:
> Hi
> I need to move the transaction logs for all the sql db's (including
> master, model and user db's etc) to a seperate drive. Does anyone
> have any experience doing this in a sql cluster?
There is no difference if you move t-log in a single box machine or in a
cluster environment.
You have to pay attention to move db files ONLY on shared disk and modify,
using EM, the switch -l in startup properties of SQL Server and assign them
the new location of t-log of master database

> Thanks
Bye
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
http://italy.mvps.org
|||Hello Parky,
I agree with Luca. There is no difference between moving the db files on a standalone SQL server v/s clustered SQL Server. One difference is that the shared dirve where you want to move the db files should be in
the dependency list of SQL Server resource. (For Ex. Say you want to move the Tlogs to a shared drive F:. Make sure that SQL Server resource has F: as a dependency. If not you will have have make it. Note that
this requires a downtime as you will have to take SQL Server resource offline, add the shared drive as a dependency and then take SQL Server online. Then you can use your usual steps (see
http://support.microsoft.com/kb/224071/EN-US/
INF: Moving SQL Server Databases to a New Location using Detach/Attach )
If you have never done this on a cluster, I will recommend you to practice the steps on a test SQL Cluster or a cluster using virtual technology.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||I would leave the System databases and there log file on one drive and split
the user databases to two diffrent databases, that way is you losse the log
drive you can still get sql server running and manage it to recreate the user
databases from the datafiles with diffrent location of the log files.
"Uttam Parui[MS]" wrote:

> Hello Parky,
> I agree with Luca. There is no difference between moving the db files on a standalone SQL server v/s clustered SQL Server. One difference is that the shared dirve where you want to move the db files should be in
> the dependency list of SQL Server resource. (For Ex. Say you want to move the Tlogs to a shared drive F:. Make sure that SQL Server resource has F: as a dependency. If not you will have have make it. Note that
> this requires a downtime as you will have to take SQL Server resource offline, add the shared drive as a dependency and then take SQL Server online. Then you can use your usual steps (see
> http://support.microsoft.com/kb/224071/EN-US/
> INF: Moving SQL Server Databases to a New Location using Detach/Attach )
> If you have never done this on a cluster, I will recommend you to practice the steps on a test SQL Cluster or a cluster using virtual technology.
> HTH,
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>

Move System and User database

Dear All,
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
K
That is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K
|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:

> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>
>
|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...[vbcol=seagreen]
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James

>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database[vbcol=seagreen]
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
DB's between Servers[vbcol=seagreen]
SQL Server Databases[vbcol=seagreen]
Using WITH MOVE in a[vbcol=seagreen]
Transfer Logins and[vbcol=seagreen]
Logins & SIDs after a[vbcol=seagreen]
Utility to map logins to[vbcol=seagreen]
Logon and/or Permission[vbcol=seagreen]
Resolve Permission[vbcol=seagreen]
http://www.sqlservercentral.com/scri...ptdetails.asp?
scriptid=599[vbcol=seagreen]
Recovery Articles[vbcol=seagreen]
message[vbcol=seagreen]
58F96DB228F9@.microsoft.com...[vbcol=seagreen]
so on...) and user[vbcol=seagreen]
ldf and mdf files in[vbcol=seagreen]
restore?[vbcol=seagreen]
server.
>.
>

Move System and User database

Dear All,
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
KThat is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:

> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Server
s
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Database
s
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permissi
on
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>
>|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...[vbcol=seagreen]
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
>|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James

>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
>
DB's between Servers[vbcol=seagreen]
SQL Server Databases[vbcol=seagreen]
Using WITH MOVE in a[vbcol=seagreen]
Transfer Logins and[vbcol=seagreen]
Logins & SIDs after a[vbcol=seagreen]
Utility to map logins to[vbcol=seagreen]
Logon and/or Permission[vbcol=seagreen]
Resolve Permission[vbcol=seagreen]
http://www.sqlservercentral.com/scr...iptdetails.asp?
scriptid=599[vbcol=seagreen]
Recovery Articles[vbcol=seagreen]
message[vbcol=seagreen]
58F96DB228F9@.microsoft.com...[vbcol=seagreen]
so on...) and user[vbcol=seagreen]
ldf and mdf files in[vbcol=seagreen]
restore?[vbcol=seagreen]
server.[vbcol=seagreen]
>.
>

Move System and User database

Dear All,
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
KThat is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:
> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> > Dear All,
> >
> > Is it possible to move system(master, model, msdb and so on...) and user
> > database to another sql server by just copying all ldf and mdf files in
> > data
> > directory while the sql on the source server is down?
> >
> > Any problem using this method instead of backup and restore?
> > as I don't want to detach the database from source server.
> >
> > Thanks for any advices
> > K
>
>|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
--
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
>> That is never a good idea. Check these out:
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
>> for SQL Server
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
>> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>> > Dear All,
>> >
>> > Is it possible to move system(master, model, msdb and so on...) and
>> > user
>> > database to another sql server by just copying all ldf and mdf files in
>> > data
>> > directory while the sql on the source server is down?
>> >
>> > Any problem using this method instead of backup and restore?
>> > as I don't want to detach the database from source server.
>> >
>> > Thanks for any advices
>> > K
>>|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James
>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
>> That is never a good idea. Check these out:
>> http://www.support.microsoft.com/?id=314546 Moving
DB's between Servers
>> http://www.support.microsoft.com/?id=224071 Moving
SQL Server Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465
Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping
Logins & SIDs after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp
Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User
Logon and/or Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to
Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?
scriptid=599
>> Restoring a .mdf
>> http://www.support.microsoft.com/?id=307775 Disaster
Recovery Articles
>> for SQL Server
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in
message
>> news:D1EB32EB-A2F6-465D-978E-
58F96DB228F9@.microsoft.com...
>> > Dear All,
>> >
>> > Is it possible to move system(master, model, msdb and
so on...) and user
>> > database to another sql server by just copying all
ldf and mdf files in
>> > data
>> > directory while the sql on the source server is down?
>> >
>> > Any problem using this method instead of backup and
restore?
>> > as I don't want to detach the database from source
server.
>> >
>> > Thanks for any advices
>> > K
>>
>.
>

Move SQL Server EE databases to SQL Server Std.

I would like to know if we have sql server 2000 EE on build 818, can we
simply restore the master database and other databases to a sql server 2000
standard build 818 without issue?I doubt you can do this with the master database but the others should be
fine.
Mike Kruchten
"ECS" <xxxx@.xx.com> wrote in message
news:OjaxVZdEEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to know if we have sql server 2000 EE on build 818, can we
> simply restore the master database and other databases to a sql server
2000
> standard build 818 without issue?
>|||Anyone else like to comment. If this can not be done what would be the best
and easiest way to move
like 50 databases off of EE onto a standard version of MS SQL?|||Like I said you should be fine for everything except the master, and you
could try that one and see. I'm assuming you have a different machine with
Std. installed that you can test on. The only thing you really need from
master is the login info, which is easy enough to recreate.
I know the other databases work as I use this process on a weekly basis for
a test environment. I'm guessing that if you try it with master the restore
process will complain but I'm only guessing, try it and see. I know it will
complain if you try to restore a backup of master with a different service
pack level.
Mike Kruchten
"ECS" <ecs@.netset.com> wrote in message
news:uNziNjmEEHA.2768@.tk2msftngp13.phx.gbl...
> Anyone else like to comment. If this can not be done what would be the
best
> and easiest way to move
> like 50 databases off of EE onto a standard version of MS SQL?
>|||Thanks mike,
any websites to point us to for scripting the sql server logins?|||I don't have any links for you, but I'm sure there are several places that
have this info. I think you can use DTS also, though I haven't done that
myself
Here's what I use. Run these on the old system, and results of each
statement is a script you run on the new system. They can be easily modified
to include a GO between each statement, otherwise you have to run each line
individually.
--For SQL logins
SELECT 'sp_addlogin '''+
left(name + ''', ',15), --Adjust for max login length
password,
+ ', ''' +
db_name(dbid) +
+ ''', ''' +
+ language
+ ''', ',
sid,
', skip_encryption'
FROM master.dbo.sysxlogins
where srvid is null
and xstatus&4 <> 4 --isntname
order by name
--For Windows logins
SELECT 'sp_grantlogin '''+ name + ''''
FROM master.dbo.sysxlogins
where srvid is null
and xstatus&4 = 4 --isntname
and name <> 'BUILTIN\ADMINISTRATORS'
order by name
"ECS" <xxxx@.xx.com> wrote in message
news:uvyjrhqEEHA.3392@.TK2MSFTNGP11.phx.gbl...
> Thanks mike,
> any websites to point us to for scripting the sql server logins?
>

Move DTS packages

Is there anyway to move the DTS packages to a new server. Can I backup and
restore the master database?
Any ideas?
ThanksGreg,
DTS packages are stored in MSDB not in master. The easist way to move a
small number of packages is to design the package, and click on the package
drop down menu, and click save as, and select the name of the new server.
--
Denny Cherry
DBA
GameSpy Industries
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:eM2Yy$iVDHA.1948@.TK2MSFTNGP11.phx.gbl...
> Is there anyway to move the DTS packages to a new server. Can I backup
and
> restore the master database?
> Any ideas?
> Thanks
>|||Greg,
Follow this link for good tips on moving DTS packages.
http://www.sqldts.com/default.aspx?6,105,204,0,1
Hope this helps
John

move databases

Hi,
I like to move sql 2000 databases from 2003 server A, on drive D to 2003
server B on drive N. But master database would stop working after I moved to
server B, on drive N.
What I need to do to resolve the problem or is this do able?
Thanks
Check out:
http://support.microsoft.com/default.aspx/kb/224071
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"anoni" <anoni@.discussions.microsoft.com> wrote in message
news:0B68D43F-A774-46F1-BE55-3D78D87D63FD@.microsoft.com...
Hi,
I like to move sql 2000 databases from 2003 server A, on drive D to 2003
server B on drive N. But master database would stop working after I moved to
server B, on drive N.
What I need to do to resolve the problem or is this do able?
Thanks

move databases

Hi,
I like to move sql 2000 databases from 2003 server A, on drive D to 2003
server B on drive N. But master database would stop working after I moved to
server B, on drive N.
What I need to do to resolve the problem or is this do able?
ThanksCheck out:
http://support.microsoft.com/default.aspx/kb/224071
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"anoni" <anoni@.discussions.microsoft.com> wrote in message
news:0B68D43F-A774-46F1-BE55-3D78D87D63FD@.microsoft.com...
Hi,
I like to move sql 2000 databases from 2003 server A, on drive D to 2003
server B on drive N. But master database would stop working after I moved to
server B, on drive N.
What I need to do to resolve the problem or is this do able?
Thanks

move databases

Hi,
I like to move sql 2000 databases from 2003 server A, on drive D to 2003
server B on drive N. But master database would stop working after I moved to
server B, on drive N.
What I need to do to resolve the problem or is this do able?
ThanksCheck out:
http://support.microsoft.com/default.aspx/kb/224071
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"anoni" <anoni@.discussions.microsoft.com> wrote in message
news:0B68D43F-A774-46F1-BE55-3D78D87D63FD@.microsoft.com...
Hi,
I like to move sql 2000 databases from 2003 server A, on drive D to 2003
server B on drive N. But master database would stop working after I moved to
server B, on drive N.
What I need to do to resolve the problem or is this do able?
Thanks