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 Master Database

Hi !
I'm about to move a SQL Server installation to a new server. Is using
backup/restore to move all databases. Both servers is SQL Server 2000 with
SP4.
I have read all books online topics about this (and I have done this kind of
operation before) but somehow I'm missing out on something, hope you can see
what I'm missing =)
This will descibe the actions I have taken
1. Full backup of all databases on server1.
2. Copy all backupfiles to server2.
3. Starting SQL Server with the -c -m switch for Single mode on server2
4. Restoring master database on server2 with the following syntax
RESTORE DATABASE master FROM disk='E:\MSSQL\backup\master.bak'
WITH MOVE 'master' to 'E:\mssql\data\master.mdf',
MOVE 'mastlog' to 'E:\mssql\data\mastlog.ldf',
REPLACE
go
5. Message from QU says that restore worked fine and that SQL Server will be
shut down.
Then the problem starts! I can't get SQL Server to start again... The
service goes to "starting" mode and the directly to "stopping". Of cource
there are several messages in logs saying that the "user" databases can't be
recovered. But that's "normal" right? Since the Master database is pointing
out database that I havn't restored yet.
But what do I need to do more to be able to restore user databases and get
server up and running'
Regards FredrikThe problem is probably that you also have file references for model and tempdb in the restored
master database. I think you have some good info about thin is KB 224071.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:D484D6E7-6627-47E2-A070-95E969267BC1@.microsoft.com...
> Hi !
> I'm about to move a SQL Server installation to a new server. Is using
> backup/restore to move all databases. Both servers is SQL Server 2000 with
> SP4.
> I have read all books online topics about this (and I have done this kind of
> operation before) but somehow I'm missing out on something, hope you can see
> what I'm missing =)
> This will descibe the actions I have taken
> 1. Full backup of all databases on server1.
> 2. Copy all backupfiles to server2.
> 3. Starting SQL Server with the -c -m switch for Single mode on server2
> 4. Restoring master database on server2 with the following syntax
> RESTORE DATABASE master FROM disk='E:\MSSQL\backup\master.bak'
> WITH MOVE 'master' to 'E:\mssql\data\master.mdf',
> MOVE 'mastlog' to 'E:\mssql\data\mastlog.ldf',
> REPLACE
> go
> 5. Message from QU says that restore worked fine and that SQL Server will be
> shut down.
> Then the problem starts! I can't get SQL Server to start again... The
> service goes to "starting" mode and the directly to "stopping". Of cource
> there are several messages in logs saying that the "user" databases can't be
> recovered. But that's "normal" right? Since the Master database is pointing
> out database that I havn't restored yet.
> But what do I need to do more to be able to restore user databases and get
> server up and running'
> Regards Fredrik
>
>|||Thanx Tibor. That really pointed me in the right direction and saved me a lot
of time!
Thanks again!
/Fredrik
"Tibor Karaszi" wrote:
> The problem is probably that you also have file references for model and tempdb in the restored
> master database. I think you have some good info about thin is KB 224071.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
> news:D484D6E7-6627-47E2-A070-95E969267BC1@.microsoft.com...
> > Hi !
> >
> > I'm about to move a SQL Server installation to a new server. Is using
> > backup/restore to move all databases. Both servers is SQL Server 2000 with
> > SP4.
> >
> > I have read all books online topics about this (and I have done this kind of
> > operation before) but somehow I'm missing out on something, hope you can see
> > what I'm missing =)
> >
> > This will descibe the actions I have taken
> >
> > 1. Full backup of all databases on server1.
> > 2. Copy all backupfiles to server2.
> > 3. Starting SQL Server with the -c -m switch for Single mode on server2
> > 4. Restoring master database on server2 with the following syntax
> > RESTORE DATABASE master FROM disk='E:\MSSQL\backup\master.bak'
> > WITH MOVE 'master' to 'E:\mssql\data\master.mdf',
> > MOVE 'mastlog' to 'E:\mssql\data\mastlog.ldf',
> > REPLACE
> > go
> > 5. Message from QU says that restore worked fine and that SQL Server will be
> > shut down.
> >
> > Then the problem starts! I can't get SQL Server to start again... The
> > service goes to "starting" mode and the directly to "stopping". Of cource
> > there are several messages in logs saying that the "user" databases can't be
> > recovered. But that's "normal" right? Since the Master database is pointing
> > out database that I havn't restored yet.
> >
> > But what do I need to do more to be able to restore user databases and get
> > server up and running'
> >
> > Regards Fredrik
> >
> >
> >
>
>

Restoring Master Database

Hi !
I'm about to move a SQL Server installation to a new server. Is using
backup/restore to move all databases. Both servers is SQL Server 2000 with
SP4.
I have read all books online topics about this (and I have done this kind of
operation before) but somehow I'm missing out on something, hope you can see
what I'm missing =)
This will descibe the actions I have taken
1. Full backup of all databases on server1.
2. Copy all backupfiles to server2.
3. Starting SQL Server with the -c -m switch for Single mode on server2
4. Restoring master database on server2 with the following syntax
RESTORE DATABASE master FROM disk='E:\MSSQL\backup\master.bak'
WITH MOVE 'master' to 'E:\mssql\data\master.mdf',
MOVE 'mastlog' to 'E:\mssql\data\mastlog.ldf',
REPLACE
go
5. Message from QU says that restore worked fine and that SQL Server will be
shut down.
Then the problem starts! I can't get SQL Server to start again... The
service goes to "starting" mode and the directly to "stopping". Of cource
there are several messages in logs saying that the "user" databases can't be
recovered. But that's "normal" right? Since the Master database is pointing
out database that I havn't restored yet.
But what do I need to do more to be able to restore user databases and get
server up and running'
Regards FredrikThe problem is probably that you also have file references for model and tem
pdb in the restored
master database. I think you have some good info about thin is KB 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:D484D6E7-6627-47E2-A070-95E969267BC1@.microsoft.com...
> Hi !
> I'm about to move a SQL Server installation to a new server. Is using
> backup/restore to move all databases. Both servers is SQL Server 2000 with
> SP4.
> I have read all books online topics about this (and I have done this kind
of
> operation before) but somehow I'm missing out on something, hope you can s
ee
> what I'm missing =)
> This will descibe the actions I have taken
> 1. Full backup of all databases on server1.
> 2. Copy all backupfiles to server2.
> 3. Starting SQL Server with the -c -m switch for Single mode on server2
> 4. Restoring master database on server2 with the following syntax
> RESTORE DATABASE master FROM disk='E:\MSSQL\backup\master.bak'
> WITH MOVE 'master' to 'E:\mssql\data\master.mdf',
> MOVE 'mastlog' to 'E:\mssql\data\mastlog.ldf',
> REPLACE
> go
> 5. Message from QU says that restore worked fine and that SQL Server will
be
> shut down.
> Then the problem starts! I can't get SQL Server to start again... The
> service goes to "starting" mode and the directly to "stopping". Of cource
> there are several messages in logs saying that the "user" databases can't
be
> recovered. But that's "normal" right? Since the Master database is pointin
g
> out database that I havn't restored yet.
> But what do I need to do more to be able to restore user databases and get
> server up and running'
> Regards Fredrik
>
>|||Thanx Tibor. That really pointed me in the right direction and saved me a lo
t
of time!
Thanks again!
/Fredrik
"Tibor Karaszi" wrote:

> The problem is probably that you also have file references for model and t
empdb in the restored
> master database. I think you have some good info about thin is KB 224071.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
> news:D484D6E7-6627-47E2-A070-95E969267BC1@.microsoft.com...
>
>

Restoring from a CD

There are two SQL 2000 servers, Server A and Server B. I backed up all the databases on Server A and then burned the backups to a CD. Now I am trying to restore one of the databases from Server A on Server B but I am having problems and getting error messages.
How do I restore a database backup file (*.bak) from a CD if the database backup is from another SQL Server?What problem are you having, specifically?
Don't forget to go into the options tab and change the file paths for the
physical file names -- in my experience that's the most common thing people
forget about when restoring databases from other servers.
"Evan" <anonymous@.discussions.microsoft.com> wrote in message
news:01075679-9221-4095-ABAF-0DB0C4CED06C@.microsoft.com...
> There are two SQL 2000 servers, Server A and Server B. I backed up all the
databases on Server A and then burned the backups to a CD. Now I am trying
to restore one of the databases from Server A on Server B but I am having
problems and getting error messages.
> How do I restore a database backup file (*.bak) from a CD if the database
backup is from another SQL Server?
>|||I'm not sure how to restore the backup so I tried a couple different methods but none of them worked. I'd like directions on how to restore the databases.
Evan
-- Adam Machanic wrote: --
What problem are you having, specifically?
Don't forget to go into the options tab and change the file paths for the
physical file names -- in my experience that's the most common thing people
forget about when restoring databases from other servers.
"Evan" <anonymous@.discussions.microsoft.com> wrote in message
news:01075679-9221-4095-ABAF-0DB0C4CED06C@.microsoft.com...
> There are two SQL 2000 servers, Server A and Server B. I backed up all the
databases on Server A and then burned the backups to a CD. Now I am trying
to restore one of the databases from Server A on Server B but I am having
problems and getting error messages.
>> How do I restore a database backup file (*.bak) from a CD if the database
backup is from another SQL Server?
>|||In Enterprise Manager:
Create a new database with whatever name you want.
Right-click on it. Click "All Tasks" -> "Restore Database".
Select "From device".
Click "Select Device", then "Add..." and browse the file you want to
restore.
Click "OK" twice. Then click the "Options" tab. Select "Force restore over
existing database". Make sure the paths in "Move to physical file name"
exist on your server. Edit the paths if necessary.
Click "OK". At this point it should restore...
"Evan" <anonymous@.discussions.microsoft.com> wrote in message
news:29FAAB9A-E962-40D2-8733-F476D8650F7B@.microsoft.com...
> I'm not sure how to restore the backup so I tried a couple different
methods but none of them worked. I'd like directions on how to restore the
databases.
> Evan
>
> -- Adam Machanic wrote: --
> What problem are you having, specifically?
> Don't forget to go into the options tab and change the file paths for
the
> physical file names -- in my experience that's the most common thing
people
> forget about when restoring databases from other servers.
>
> "Evan" <anonymous@.discussions.microsoft.com> wrote in message
> news:01075679-9221-4095-ABAF-0DB0C4CED06C@.microsoft.com...
> > There are two SQL 2000 servers, Server A and Server B. I backed up
all the
> databases on Server A and then burned the backups to a CD. Now I am
trying
> to restore one of the databases from Server A on Server B but I am
having
> problems and getting error messages.
> >> How do I restore a database backup file (*.bak) from a CD if the
database
> backup is from another SQL Server?
> >|||It worked! Thanks a million.
I tried that procedure before but it didn't work - the file paths are a gotcha.
Evan
-- Adam Machanic wrote: --
In Enterprise Manager:
Create a new database with whatever name you want.
Right-click on it. Click "All Tasks" -> "Restore Database".
Select "From device".
Click "Select Device", then "Add..." and browse the file you want to
restore.
Click "OK" twice. Then click the "Options" tab. Select "Force restore over
existing database". Make sure the paths in "Move to physical file name"
exist on your server. Edit the paths if necessary.
Click "OK". At this point it should restore...
"Evan" <anonymous@.discussions.microsoft.com> wrote in message
news:29FAAB9A-E962-40D2-8733-F476D8650F7B@.microsoft.com...
> I'm not sure how to restore the backup so I tried a couple different
methods but none of them worked. I'd like directions on how to restore the
databases.
>> Evan
>> -- Adam Machanic wrote: --
>> What problem are you having, specifically?
>> Don't forget to go into the options tab and change the file paths for
the
> physical file names -- in my experience that's the most common thing
people
> forget about when restoring databases from other servers.
>> "Evan" <anonymous@.discussions.microsoft.com> wrote in message
> news:01075679-9221-4095-ABAF-0DB0C4CED06C@.microsoft.com...
>> There are two SQL 2000 servers, Server A and Server B. I backed up
all the
> databases on Server A and then burned the backups to a CD. Now I am
trying
> to restore one of the databases from Server A on Server B but I am
having
> problems and getting error messages.
>> How do I restore a database backup file (*.bak) from a CD if the
database
> backup is from another SQL Server?
>>

Restoring encrypted databases between different servers

Hello:

I'm working with two sql servers instances, ServerA and ServerB, which run under two different service accounts on different machines. They both have a database, DatabaseA, that has some encrypted fields.

If I take a backup of DatabaseA on ServerA and restore it on ServerB, I need to re-encrypt the Database Master Key (DMK) with the Service Master Key (SMK) as follows:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd used to encrypt DMK'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

However, if I again take a backup on ServerB and then restore it on ServerA, I can use the DMK on ServerA without re-encrypting it with the SMK.

Shouldn't I have to re-encrypt the DMK with the SMK everytime I restore from a backup that was generated from a different server?

Thanks,

Cyndi

After the initial RESTORE and ALTER MASTER KEY … ADD ENCRYPTION BY SERVER MASTER KEY on ServerB, a redundant copy of the DBMK (protected by ServerB SMK) will be stored in the mater DB; and as long as the DBMK is the same, the copies should be synchronized. This redundant copy is there to minimize the DBMK management after the initial setup (via RESTORE).

I hope this information helps. Please let us know if you have any additional questions and/or if you have any additional feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Restoring Databases to New Servers

I only have one Server running SQL. Daily I am performing full SQL backups
of the databases and moving them to tape offsite. If my the Server is
destroyed I will obtain a new machine. load SQL Sever and intend to restore
the backups off tape. I am told there is no clean way to do this because of
the GUID problems. Does anyone know of a clean way to do this or can direct
me to instructions as to how to do it?
Thanks.
Hi,
No issues at all. All you have to do is:-
1. Install SQL Server with same service pack as old
2. Restore Master database
3. Restore MSDB Database
4. Now Restore the user database one by one. If you want to apply
transaction log backups then use NORECOVERY option along with Restore
Database/
Restore Log command
Since you are restoring the MASTER database as well.. automatically Logins
and users will be syncronized.
Thanks
Hari
SQL Server MVP
"KED Florida" <KED Florida@.discussions.microsoft.com> wrote in message
news:0CCDB4D9-144C-4FB4-A345-141286B73983@.microsoft.com...
>I only have one Server running SQL. Daily I am performing full SQL backups
> of the databases and moving them to tape offsite. If my the Server is
> destroyed I will obtain a new machine. load SQL Sever and intend to
> restore
> the backups off tape. I am told there is no clean way to do this because
> of
> the GUID problems. Does anyone know of a clean way to do this or can
> direct
> me to instructions as to how to do it?
> Thanks.

Restoring Databases to New Servers

I only have one Server running SQL. Daily I am performing full SQL backups
of the databases and moving them to tape offsite. If my the Server is
destroyed I will obtain a new machine. load SQL Sever and intend to restore
the backups off tape. I am told there is no clean way to do this because of
the GUID problems. Does anyone know of a clean way to do this or can direct
me to instructions as to how to do it?
Thanks.Hi,
No issues at all. All you have to do is:-
1. Install SQL Server with same service pack as old
2. Restore Master database
3. Restore MSDB Database
4. Now Restore the user database one by one. If you want to apply
transaction log backups then use NORECOVERY option along with Restore
Database/
Restore Log command
Since you are restoring the MASTER database as well.. automatically Logins
and users will be syncronized.
Thanks
Hari
SQL Server MVP
"KED Florida" <KED Florida@.discussions.microsoft.com> wrote in message
news:0CCDB4D9-144C-4FB4-A345-141286B73983@.microsoft.com...
>I only have one Server running SQL. Daily I am performing full SQL backups
> of the databases and moving them to tape offsite. If my the Server is
> destroyed I will obtain a new machine. load SQL Sever and intend to
> restore
> the backups off tape. I am told there is no clean way to do this because
> of
> the GUID problems. Does anyone know of a clean way to do this or can
> direct
> me to instructions as to how to do it?
> Thanks.

Restoring Databases to New Servers

I only have one Server running SQL. Daily I am performing full SQL backups
of the databases and moving them to tape offsite. If my the Server is
destroyed I will obtain a new machine. load SQL Sever and intend to restore
the backups off tape. I am told there is no clean way to do this because of
the GUID problems. Does anyone know of a clean way to do this or can direct
me to instructions as to how to do it?
Thanks.Hi,
No issues at all. All you have to do is:-
1. Install SQL Server with same service pack as old
2. Restore Master database
3. Restore MSDB Database
4. Now Restore the user database one by one. If you want to apply
transaction log backups then use NORECOVERY option along with Restore
Database/
Restore Log command
Since you are restoring the MASTER database as well.. automatically Logins
and users will be syncronized.
Thanks
Hari
SQL Server MVP
"KED Florida" <KED Florida@.discussions.microsoft.com> wrote in message
news:0CCDB4D9-144C-4FB4-A345-141286B73983@.microsoft.com...
>I only have one Server running SQL. Daily I am performing full SQL backups
> of the databases and moving them to tape offsite. If my the Server is
> destroyed I will obtain a new machine. load SQL Sever and intend to
> restore
> the backups off tape. I am told there is no clean way to do this because
> of
> the GUID problems. Does anyone know of a clean way to do this or can
> direct
> me to instructions as to how to do it?
> Thanks.

Restoring database receives UNICODE error

Hi,
I am having problem while restoring database to another SQL Server. I
receives below given message. My both SQL Servers are 7.0.
MESSAGE
SQL DMO (ODBC SQL Stae 42000)
The database you are attmpting to restore was backup under a different unicode
Locale ID (1033) or Unicode Comparision Style 196609) Then the Unicode
Locale ID
1033 or Unicode comparion style 196611) Currently running on this server.
Backup or restore Operation terminating abnormally.
Ok Button.
MESSAGE OVER
Anyone who can solve this problem.
Nilkanth Desai
I think that although the error message is from ODBC the issue is in the
tool you're using. Perhaps you would get a better response posting to a
group with knowledge of the tools you're using - or to the SQL Server team
in general.
| Thread-Topic: Restoring database receives UNICODE error
| thread-index: AcXKdpDDNRh+0thOR76Cjqm0RNXUxA==
| X-WBNR-Posting-Host: 61.1.58.32
| From: "=?Utf-8?B?Tmlsa2FudGggRGVzYWk=?="
<NilkanthDesai@.discussions.microsoft.com>
| Subject: Restoring database receives UNICODE error
| Date: Thu, 6 Oct 2005 06:05:03 -0700
| Lines: 23
| Message-ID: <D3F9E543-916B-44A7-ACD4-AD3200C10CA6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2822
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hi,
| I am having problem while restoring database to another SQL Server.
I
| receives below given message. My both SQL Servers are 7.0.
|
| MESSAGE
| SQL DMO (ODBC SQL Stae 42000)
| The database you are attmpting to restore was backup under a different
unicode
| Locale ID (1033) or Unicode Comparision Style 196609) Then the Unicode
| Locale ID
| 1033 or Unicode comparion style 196611) Currently running on this server.
|
| Backup or restore Operation terminating abnormally.
|
| Ok Button.
|
|
| MESSAGE OVER
|
| Anyone who can solve this problem.
|
| Nilkanth Desai
|
|
|

Restoring database receives UNICODE error

Hi,
I am having problem while restoring database to another SQL Server. I
receives below given message. My both SQL Servers are 7.0.
MESSAGE
SQL DMO (ODBC SQL Stae 42000)
The database you are attmpting to restore was backup under a different unico
de
Locale ID (1033) or Unicode Comparision Style 196609) Then the Unicode
Locale ID
1033 or Unicode comparion style 196611) Currently running on this server.
Backup or restore Operation terminating abnormally.
Ok Button.
MESSAGE OVER
Anyone who can solve this problem.
Nilkanth DesaiI think that although the error message is from ODBC the issue is in the
tool you're using. Perhaps you would get a better response posting to a
group with knowledge of the tools you're using - or to the SQL Server team
in general.
--
| Thread-Topic: Restoring database receives UNICODE error
| thread-index: AcXKdpDDNRh+0thOR76Cjqm0RNXUxA==
| X-WBNR-Posting-Host: 61.1.58.32
| From: "examnotes"
<NilkanthDesai@.discussions.microsoft.com>
| Subject: Restoring database receives UNICODE error
| Date: Thu, 6 Oct 2005 06:05:03 -0700
| Lines: 23
| Message-ID: <D3F9E543-916B-44A7-ACD4-AD3200C10CA6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2822
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hi,
| I am having problem while restoring database to another SQL Server.
I
| receives below given message. My both SQL Servers are 7.0.
|
| MESSAGE
| SQL DMO (ODBC SQL Stae 42000)
| The database you are attmpting to restore was backup under a different
unicode
| Locale ID (1033) or Unicode Comparision Style 196609) Then the Unicode
| Locale ID
| 1033 or Unicode comparion style 196611) Currently running on this server.
|
| Backup or restore Operation terminating abnormally.
|
| Ok Button.
|
|
| MESSAGE OVER
|
| Anyone who can solve this problem.
|
| Nilkanth Desai
|
|
|