Restoring Master DB or transfering logins etc

Hi there, I'm hoping you can help a SQL novice......
i need to move the SQL installs for a couple of our SQL7 servers (and sadly
no, they cant be upgraded).
i've restored the DB's no problems but i'm having problems with the master
DB and/ or its contents. i want to get all the logins, agents jobs etc from
the old servers to the new ones. restoring the master DB would do this, but
unfortunately, the drive structure on the new servers doesn match that of the
old and so the locations of the other SQL built in DB's (model, MSDB, tempdb)
is now different. if i restore the Master DB (in single user mode obviously)
the services fail to then re-start because they cant locate these DB's.
so my question.... is there any way to restart SQL service post a master
restore, without it looking for these DB's so i can then modify the file
location properties of these DB?
OR
is there any way to get stuff like the security logins, SQL agent jobs etc
from one server to another without restoring the Master DB?
many thanks for you help and advice.
Hi
There are lots discussion about such issues , try to search on.
It's my way how I do it
1) Backup all user and system databases on the old server
2) Script out all logins (MS has provide two SP to script it out)
3) Script all jobs/dts packages
4) Restore all user databases
5) Run the output of two MS store procedures on destination server
6) Run the output of jobs script.
7) Re-create all dts packages
"J Hotch" <jonathan.hotchkiss-remove-@.tesco.net> wrote in message
news:990A6094-3F08-4731-BF71-EAD9D0B343AF@.microsoft.com...
> Hi there, I'm hoping you can help a SQL novice......
> i need to move the SQL installs for a couple of our SQL7 servers (and
sadly
> no, they cant be upgraded).
> i've restored the DB's no problems but i'm having problems with the master
> DB and/ or its contents. i want to get all the logins, agents jobs etc
from
> the old servers to the new ones. restoring the master DB would do this,
but
> unfortunately, the drive structure on the new servers doesn match that of
the
> old and so the locations of the other SQL built in DB's (model, MSDB,
tempdb)
> is now different. if i restore the Master DB (in single user mode
obviously)
> the services fail to then re-start because they cant locate these DB's.
> so my question.... is there any way to restart SQL service post a master
> restore, without it looking for these DB's so i can then modify the file
> location properties of these DB?
> OR
> is there any way to get stuff like the security logins, SQL agent jobs etc
> from one server to another without restoring the Master DB?
> many thanks for you help and advice.
|||http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
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
"J Hotch" <jonathan.hotchkiss-remove-@.tesco.net> wrote in message
news:990A6094-3F08-4731-BF71-EAD9D0B343AF@.microsoft.com...
> Hi there, I'm hoping you can help a SQL novice......
> i need to move the SQL installs for a couple of our SQL7 servers (and
> sadly
> no, they cant be upgraded).
> i've restored the DB's no problems but i'm having problems with the master
> DB and/ or its contents. i want to get all the logins, agents jobs etc
> from
> the old servers to the new ones. restoring the master DB would do this,
> but
> unfortunately, the drive structure on the new servers doesn match that of
> the
> old and so the locations of the other SQL built in DB's (model, MSDB,
> tempdb)
> is now different. if i restore the Master DB (in single user mode
> obviously)
> the services fail to then re-start because they cant locate these DB's.
> so my question.... is there any way to restart SQL service post a master
> restore, without it looking for these DB's so i can then modify the file
> location properties of these DB?
> OR
> is there any way to get stuff like the security logins, SQL agent jobs etc
> from one server to another without restoring the Master DB?
> many thanks for you help and advice.
|||thankyou Uri and Andrew, you've both given me plenty to work with.
many thanks
"Andrew J. Kelly" wrote:

> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> 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
>
> "J Hotch" <jonathan.hotchkiss-remove-@.tesco.net> wrote in message
> news:990A6094-3F08-4731-BF71-EAD9D0B343AF@.microsoft.com...
>
>

Restoring Master DB or transfering logins etc

Hi there, I'm hoping you can help a SQL novice......
i need to move the SQL installs for a couple of our SQL7 servers (and sadly
no, they cant be upgraded).
i've restored the DB's no problems but i'm having problems with the master
DB and/ or its contents. i want to get all the logins, agents jobs etc from
the old servers to the new ones. restoring the master DB would do this, but
unfortunately, the drive structure on the new servers doesn match that of th
e
old and so the locations of the other SQL built in DB's (model, MSDB, tempdb
)
is now different. if i restore the Master DB (in single user mode obviously)
the services fail to then re-start because they cant locate these DB's.
so my question.... is there any way to restart SQL service post a master
restore, without it looking for these DB's so i can then modify the file
location properties of these DB?
OR
is there any way to get stuff like the security logins, SQL agent jobs etc
from one server to another without restoring the Master DB?
many thanks for you help and advice.Hi
There are lots discussion about such issues , try to search on.
It's my way how I do it
1) Backup all user and system databases on the old server
2) Script out all logins (MS has provide two SP to script it out)
3) Script all jobs/dts packages
4) Restore all user databases
5) Run the output of two MS store procedures on destination server
6) Run the output of jobs script.
7) Re-create all dts packages
"J Hotch" <jonathan.hotchkiss-remove-@.tesco.net> wrote in message
news:990A6094-3F08-4731-BF71-EAD9D0B343AF@.microsoft.com...
> Hi there, I'm hoping you can help a SQL novice......
> i need to move the SQL installs for a couple of our SQL7 servers (and
sadly
> no, they cant be upgraded).
> i've restored the DB's no problems but i'm having problems with the master
> DB and/ or its contents. i want to get all the logins, agents jobs etc
from
> the old servers to the new ones. restoring the master DB would do this,
but
> unfortunately, the drive structure on the new servers doesn match that of
the
> old and so the locations of the other SQL built in DB's (model, MSDB,
tempdb)
> is now different. if i restore the Master DB (in single user mode
obviously)
> the services fail to then re-start because they cant locate these DB's.
> so my question.... is there any way to restart SQL service post a master
> restore, without it looking for these DB's so i can then modify the file
> location properties of these DB?
> OR
> is there any way to get stuff like the security logins, SQL agent jobs etc
> from one server to another without restoring the Master DB?
> many thanks for you help and advice.|||http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
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
"J Hotch" <jonathan.hotchkiss-remove-@.tesco.net> wrote in message
news:990A6094-3F08-4731-BF71-EAD9D0B343AF@.microsoft.com...
> Hi there, I'm hoping you can help a SQL novice......
> i need to move the SQL installs for a couple of our SQL7 servers (and
> sadly
> no, they cant be upgraded).
> i've restored the DB's no problems but i'm having problems with the master
> DB and/ or its contents. i want to get all the logins, agents jobs etc
> from
> the old servers to the new ones. restoring the master DB would do this,
> but
> unfortunately, the drive structure on the new servers doesn match that of
> the
> old and so the locations of the other SQL built in DB's (model, MSDB,
> tempdb)
> is now different. if i restore the Master DB (in single user mode
> obviously)
> the services fail to then re-start because they cant locate these DB's.
> so my question.... is there any way to restart SQL service post a master
> restore, without it looking for these DB's so i can then modify the file
> location properties of these DB?
> OR
> is there any way to get stuff like the security logins, SQL agent jobs etc
> from one server to another without restoring the Master DB?
> many thanks for you help and advice.|||thankyou Uri and Andrew, you've both given me plenty to work with.
many thanks
"Andrew J. Kelly" wrote:

> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> 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
>
> "J Hotch" <jonathan.hotchkiss-remove-@.tesco.net> wrote in message
> news:990A6094-3F08-4731-BF71-EAD9D0B343AF@.microsoft.com...
>
>

Restoring Master DB or transfering logins etc

Hi there, I'm hoping you can help a SQL novice......
i need to move the SQL installs for a couple of our SQL7 servers (and sadly
no, they cant be upgraded).
i've restored the DB's no problems but i'm having problems with the master
DB and/ or its contents. i want to get all the logins, agents jobs etc from
the old servers to the new ones. restoring the master DB would do this, but
unfortunately, the drive structure on the new servers doesn match that of the
old and so the locations of the other SQL built in DB's (model, MSDB, tempdb)
is now different. if i restore the Master DB (in single user mode obviously)
the services fail to then re-start because they cant locate these DB's.
so my question.... is there any way to restart SQL service post a master
restore, without it looking for these DB's so i can then modify the file
location properties of these DB?
OR
is there any way to get stuff like the security logins, SQL agent jobs etc
from one server to another without restoring the Master DB?
many thanks for you help and advice.Hi
There are lots discussion about such issues , try to search on.
It's my way how I do it
1) Backup all user and system databases on the old server
2) Script out all logins (MS has provide two SP to script it out)
3) Script all jobs/dts packages
4) Restore all user databases
5) Run the output of two MS store procedures on destination server
6) Run the output of jobs script.
7) Re-create all dts packages
"J Hotch" <jonathan.hotchkiss-remove-@.tesco.net> wrote in message
news:990A6094-3F08-4731-BF71-EAD9D0B343AF@.microsoft.com...
> Hi there, I'm hoping you can help a SQL novice......
> i need to move the SQL installs for a couple of our SQL7 servers (and
sadly
> no, they cant be upgraded).
> i've restored the DB's no problems but i'm having problems with the master
> DB and/ or its contents. i want to get all the logins, agents jobs etc
from
> the old servers to the new ones. restoring the master DB would do this,
but
> unfortunately, the drive structure on the new servers doesn match that of
the
> old and so the locations of the other SQL built in DB's (model, MSDB,
tempdb)
> is now different. if i restore the Master DB (in single user mode
obviously)
> the services fail to then re-start because they cant locate these DB's.
> so my question.... is there any way to restart SQL service post a master
> restore, without it looking for these DB's so i can then modify the file
> location properties of these DB?
> OR
> is there any way to get stuff like the security logins, SQL agent jobs etc
> from one server to another without restoring the Master DB?
> many thanks for you help and advice.|||http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
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
"J Hotch" <jonathan.hotchkiss-remove-@.tesco.net> wrote in message
news:990A6094-3F08-4731-BF71-EAD9D0B343AF@.microsoft.com...
> Hi there, I'm hoping you can help a SQL novice......
> i need to move the SQL installs for a couple of our SQL7 servers (and
> sadly
> no, they cant be upgraded).
> i've restored the DB's no problems but i'm having problems with the master
> DB and/ or its contents. i want to get all the logins, agents jobs etc
> from
> the old servers to the new ones. restoring the master DB would do this,
> but
> unfortunately, the drive structure on the new servers doesn match that of
> the
> old and so the locations of the other SQL built in DB's (model, MSDB,
> tempdb)
> is now different. if i restore the Master DB (in single user mode
> obviously)
> the services fail to then re-start because they cant locate these DB's.
> so my question.... is there any way to restart SQL service post a master
> restore, without it looking for these DB's so i can then modify the file
> location properties of these DB?
> OR
> is there any way to get stuff like the security logins, SQL agent jobs etc
> from one server to another without restoring the Master DB?
> many thanks for you help and advice.|||thankyou Uri and Andrew, you've both given me plenty to work with.
many thanks
"Andrew J. Kelly" wrote:
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> 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
>
> "J Hotch" <jonathan.hotchkiss-remove-@.tesco.net> wrote in message
> news:990A6094-3F08-4731-BF71-EAD9D0B343AF@.microsoft.com...
> > Hi there, I'm hoping you can help a SQL novice......
> >
> > i need to move the SQL installs for a couple of our SQL7 servers (and
> > sadly
> > no, they cant be upgraded).
> >
> > i've restored the DB's no problems but i'm having problems with the master
> > DB and/ or its contents. i want to get all the logins, agents jobs etc
> > from
> > the old servers to the new ones. restoring the master DB would do this,
> > but
> > unfortunately, the drive structure on the new servers doesn match that of
> > the
> > old and so the locations of the other SQL built in DB's (model, MSDB,
> > tempdb)
> > is now different. if i restore the Master DB (in single user mode
> > obviously)
> > the services fail to then re-start because they cant locate these DB's.
> >
> > so my question.... is there any way to restart SQL service post a master
> > restore, without it looking for these DB's so i can then modify the file
> > location properties of these DB?
> > OR
> > is there any way to get stuff like the security logins, SQL agent jobs etc
> > from one server to another without restoring the Master DB?
> >
> > many thanks for you help and advice.
>
>

Restoring databases on SQL Server 2000 while trying not to restore to the wrong database

Experts,
Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a couple
of development teams using databases on the same SQL Server. While they can
backup their databases, they cannot restore their own databases. I have
noticed that the SQL server role overwrite the DB roles on each user
database. Users have to have DB creator permission in order to restore the
existing database. However, this role also grants users to ability to
restore DB to other users database's. Is there any way around this, so that
I could give my development users rights to restore THEIR OWN database but
not right to restore database created by someone else?
--
SpinHi
You could do this by letting them have their own instance, but then you may
as well get copies of developer edition for them!
Why do you not trust them?
John
"Spin" wrote:
> Experts,
> Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a couple
> of development teams using databases on the same SQL Server. While they can
> backup their databases, they cannot restore their own databases. I have
> noticed that the SQL server role overwrite the DB roles on each user
> database. Users have to have DB creator permission in order to restore the
> existing database. However, this role also grants users to ability to
> restore DB to other users database's. Is there any way around this, so that
> I could give my development users rights to restore THEIR OWN database but
> not right to restore database created by someone else?
> --
> Spin
>
>|||I do not want them accidentally restoring onto someone else's database.
--
Spin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:30C46FD5-B979-4B35-AEAE-0B6885F9EA57@.microsoft.com...
> Hi
> You could do this by letting them have their own instance, but then you
> may
> as well get copies of developer edition for them!
> Why do you not trust them?
> John
>
> "Spin" wrote:
>> Experts,
>> Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a
>> couple
>> of development teams using databases on the same SQL Server. While they
>> can
>> backup their databases, they cannot restore their own databases. I have
>> noticed that the SQL server role overwrite the DB roles on each user
>> database. Users have to have DB creator permission in order to restore
>> the
>> existing database. However, this role also grants users to ability to
>> restore DB to other users database's. Is there any way around this, so
>> that
>> I could give my development users rights to restore THEIR OWN database
>> but
>> not right to restore database created by someone else?
>> --
>> Spin
>>|||Hi
Proper use of a source code contol system, and a regular backup regime for
you development server would reduce the potential for loss if this does
occur. Maybe you should allocate the privileges to more senior members of the
team only?
John
"Spin" wrote:
> I do not want them accidentally restoring onto someone else's database.
> --
> Spin
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:30C46FD5-B979-4B35-AEAE-0B6885F9EA57@.microsoft.com...
> > Hi
> >
> > You could do this by letting them have their own instance, but then you
> > may
> > as well get copies of developer edition for them!
> >
> > Why do you not trust them?
> >
> > John
> >
> >
> > "Spin" wrote:
> >
> >> Experts,
> >>
> >> Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a
> >> couple
> >> of development teams using databases on the same SQL Server. While they
> >> can
> >> backup their databases, they cannot restore their own databases. I have
> >> noticed that the SQL server role overwrite the DB roles on each user
> >> database. Users have to have DB creator permission in order to restore
> >> the
> >> existing database. However, this role also grants users to ability to
> >> restore DB to other users database's. Is there any way around this, so
> >> that
> >> I could give my development users rights to restore THEIR OWN database
> >> but
> >> not right to restore database created by someone else?
> >>
> >> --
> >> Spin
> >>
> >>
> >>
>
>