After reinstall of sql server and applying SP3a I can't restore a saved
copy of Master. Receiving massage " the backup of the system database
on device '*:\***\...bak' cannot be restored because it was created by a
different version of the server (134218646) than this server(134218488).
Restore database is terminating abnormally."
Which SP do I need to get to SoftwareVersionBuild = 818
Hi Jim,
I believe the recipe for Build 8.00.818 is:
SP3 + Q821277/Q821337/Q818388/Q826161/Q821280
Steven Hutchinson,
IT Support Engineer,
Tayside Fire Brigade.
"Jim C." <Jim C.@.discussions.microsoft.com> wrote in message
news:32E29BC8-A586-496C-A191-D2CE5CC7E3F9@.microsoft.com...
> After reinstall of sql server and applying SP3a I can't restore a saved
> copy of Master. Receiving massage " the backup of the system database
> on device '*:\***\...bak' cannot be restored because it was created by a
> different version of the server (134218646) than this server(134218488).
> Restore database is terminating abnormally."
> Which SP do I need to get to SoftwareVersionBuild = 818
|||Thanks Steve. I tried SP3A and that didn't do it.
"Steven Hutchinson" wrote:
> Hi Jim,
> I believe the recipe for Build 8.00.818 is:
> SP3 + Q821277/Q821337/Q818388/Q826161/Q821280
>
> --
> Steven Hutchinson,
> IT Support Engineer,
> Tayside Fire Brigade.
>
> "Jim C." <Jim C.@.discussions.microsoft.com> wrote in message
> news:32E29BC8-A586-496C-A191-D2CE5CC7E3F9@.microsoft.com...
>
>
Showing posts with label cant. Show all posts
Showing posts with label cant. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
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...
>
>
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...
>
>
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.
>
>
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 db after Reinstall
After reinstall of sql server and applying SP3a I can't restore a saved
copy of Master. Receiving massage " the backup of the system database
on device '*:\***\...bak' cannot be restored because it was created by a
different version of the server (134218646) than this server(134218488).
Restore database is terminating abnormally."
Which SP do I need to get to SoftwareVersionBuild = 818
Hi Jim,
I believe the recipe for Build 8.00.818 is:
SP3 + Q821277/Q821337/Q818388/Q826161/Q821280
Steven Hutchinson,
IT Support Engineer,
Tayside Fire Brigade.
"Jim C." <Jim C.@.discussions.microsoft.com> wrote in message
news:32E29BC8-A586-496C-A191-D2CE5CC7E3F9@.microsoft.com...
> After reinstall of sql server and applying SP3a I can't restore a saved
> copy of Master. Receiving massage " the backup of the system database
> on device '*:\***\...bak' cannot be restored because it was created by a
> different version of the server (134218646) than this server(134218488).
> Restore database is terminating abnormally."
> Which SP do I need to get to SoftwareVersionBuild = 818
|||Thanks Steve. I tried SP3A and that didn't do it.
"Steven Hutchinson" wrote:
> Hi Jim,
> I believe the recipe for Build 8.00.818 is:
> SP3 + Q821277/Q821337/Q818388/Q826161/Q821280
>
> --
> Steven Hutchinson,
> IT Support Engineer,
> Tayside Fire Brigade.
>
> "Jim C." <Jim C.@.discussions.microsoft.com> wrote in message
> news:32E29BC8-A586-496C-A191-D2CE5CC7E3F9@.microsoft.com...
>
>
copy of Master. Receiving massage " the backup of the system database
on device '*:\***\...bak' cannot be restored because it was created by a
different version of the server (134218646) than this server(134218488).
Restore database is terminating abnormally."
Which SP do I need to get to SoftwareVersionBuild = 818
Hi Jim,
I believe the recipe for Build 8.00.818 is:
SP3 + Q821277/Q821337/Q818388/Q826161/Q821280
Steven Hutchinson,
IT Support Engineer,
Tayside Fire Brigade.
"Jim C." <Jim C.@.discussions.microsoft.com> wrote in message
news:32E29BC8-A586-496C-A191-D2CE5CC7E3F9@.microsoft.com...
> After reinstall of sql server and applying SP3a I can't restore a saved
> copy of Master. Receiving massage " the backup of the system database
> on device '*:\***\...bak' cannot be restored because it was created by a
> different version of the server (134218646) than this server(134218488).
> Restore database is terminating abnormally."
> Which SP do I need to get to SoftwareVersionBuild = 818
|||Thanks Steve. I tried SP3A and that didn't do it.
"Steven Hutchinson" wrote:
> Hi Jim,
> I believe the recipe for Build 8.00.818 is:
> SP3 + Q821277/Q821337/Q818388/Q826161/Q821280
>
> --
> Steven Hutchinson,
> IT Support Engineer,
> Tayside Fire Brigade.
>
> "Jim C." <Jim C.@.discussions.microsoft.com> wrote in message
> news:32E29BC8-A586-496C-A191-D2CE5CC7E3F9@.microsoft.com...
>
>
Saturday, February 25, 2012
Restoring DB for files...
Hi
In order to copy a database from a server to a server, I cant detach the database as there are many users connected to it. The transaction log is large file while the data is about 46 MB, I made a backup for the data file only, zipped it and copied it to the target server.
Ex: the name of the source DB on the source server is x, the file I took as a backup for is x-data
From the Enterprise Manager, right-click the x DB, All tasks, Backup database, in the backup option click the File and Filegroup option and chose the primary file group with x_data file.
Then, I created a new database name it y with y_data , y_log files on primary filegroup. I want to restore my backup to have the DB x on my target server,
I tried:
RESTORE DATABASE y
FILE = 'y_data',
FILEGROUP = 'primary'
FROM disk= 'E:\VMS\DBFiles\test'
GO
But it did not work. Any help?
Thanks,what is the error you are getting ?
In order to copy a database from a server to a server, I cant detach the database as there are many users connected to it. The transaction log is large file while the data is about 46 MB, I made a backup for the data file only, zipped it and copied it to the target server.
Ex: the name of the source DB on the source server is x, the file I took as a backup for is x-data
From the Enterprise Manager, right-click the x DB, All tasks, Backup database, in the backup option click the File and Filegroup option and chose the primary file group with x_data file.
Then, I created a new database name it y with y_data , y_log files on primary filegroup. I want to restore my backup to have the DB x on my target server,
I tried:
RESTORE DATABASE y
FILE = 'y_data',
FILEGROUP = 'primary'
FROM disk= 'E:\VMS\DBFiles\test'
GO
But it did not work. Any help?
Thanks,what is the error you are getting ?
Tuesday, February 21, 2012
Restoring database with different COLLATION
I make backup from SQL Server 2000 and distribute this backup to our
customers.
They install MSDE 2000, and I can't garantee what COLLATION they will use.
How I can modify the COLLATION of the database after restoring it?
Thanks in advance.
hi Jorge,
"Jorge Brizuela" <jorge_brizuela@.spymac.com> ha scritto nel messaggio
news:OckM6w$pEHA.2948@.TK2MSFTNGP11.phx.gbl
> I make backup from SQL Server 2000 and distribute this backup to our
> customers.
> They install MSDE 2000, and I can't garantee what COLLATION they will
> use.
> How I can modify the COLLATION of the database after restoring it?
> Thanks in advance.
as SQL Server 2000 supports different collations and sort orders for each
database, and even different collations at column level granularity, you
should not require altering that setting for your distributed database...
you can however use the
ALTER DATABASE db_name
COLLATE new_collation...
ALTER TABLE tbname
ALTER COLUMN colname type
COLLATE new_collation
so you have to alter the database setting, and then modify all varchar(n),
char(n) and text datatype columns accordingly...
that's to say
SET NOCOUNT ON
SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master an sys
databases collation]
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.TestTB (
ID INT NOT NULL ,
name VARCHAR(10) NOT NULL
)
INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
CREATE TABLE dbo.TestTB2 (
ID INT NOT NULL ,
name VARCHAR(10) NOT NULL
)
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
original]
GO
ALTER DATABASE TEST
COLLATE Latin1_General_CS_AI_KS_WS
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
after changing]
GO
PRINT 'alter each varchar(n), char(n) to the defined collation by HAND'
ALTER TABLE dbo.TestTB2
ALTER COLUMN [name] VARCHAR(10)
COLLATE Latin1_General_CS_AI_KS_WS
ALTER TABLE dbo.TestTB
ALTER COLUMN [name] VARCHAR(10)
COLLATE Latin1_General_CS_AI_KS_WS
GO
PRINT ''
PRINT '--'
PRINT 'alter each varchar(n), char(n) to the defined collation using a
cursor'
PRINT 'an ALTER TABLE ALTER COLUMN script will be written (not executed)'
DECLARE @.sql VARCHAR(2000)
DECLARE @.table_schema VARCHAR(255),
@.table_name VARCHAR(255),
@.column_name VARCHAR(255),
@.is_nullable VARCHAR(255),
@.data_type VARCHAR(255),
@.character_maximum_length VARCHAR(255)
DECLARE myCur CURSOR FOR
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
AND TABLE_NAME NOT LIKE 'sys%'
OPEN myCur
FETCH NEXT FROM myCur INTO @.table_schema,
@.table_name,
@.column_name,
@.is_nullable,
@.data_type,
@.character_maximum_length
WHILE @.@.FETCH_STATUS <> -1
BEGIN
SET @.sql = 'ALTER TABLE ' + @.table_schema + '.' + @.table_name
+ ' ALTER COLUMN ' + @.column_name + ' '
+ @.data_type + '(' + @.character_maximum_length + ') '
+ 'COLLATE Latin1_General_CS_AI_KS_WS '
+ CASE @.is_nullable WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END
PRINT @.sql
FETCH NEXT FROM myCur INTO @.table_schema,
@.table_name,
@.column_name,
@.is_nullable,
@.data_type,
@.character_maximum_length
END
CLOSE myCur
DEALLOCATE myCur
GO
PRINT 'drop all'
USE master
GO
DROP DATABASE TEST
as you can see... you can do it by hand, changing each column setting or
doing something handy like letting SQL Server writing the actual
Transact-SQL ALTER TABLE ALTER COLUMN ... statements you have to execute to
perform the desired result...
the actual statements will be
ALTER TABLE dbo.TestTB ALTER COLUMN name varchar(10) COLLATE
Latin1_General_CS_AI_KS_WS NULL
ALTER TABLE dbo.TestTB2 ALTER COLUMN name varchar(10) COLLATE
Latin1_General_CS_AI_KS_WS NULL
I do not love cursors, but this is one of the cases I could not find another
way to have the job done...
by the way, the problem you describe (it's no more a problem in SQL Server
2000) is one of the reasons I do not deploy my databases using restore
and/or sp_attach_db features... you are not "inheriting" all users database
settings like general sort order, model database specifi settings and
objects the end user placed in that template database...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi again, Jorge,
actually it can be done without a cursor, but yesterday night I was too
tired... =;-D
I appologize...
SET NOCOUNT ON
SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master and sys
databases collation]
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.TestTB (
ID INT NOT NULL ,
name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
)
INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
CREATE TABLE dbo.TestTB2 (
ID INT NOT NULL ,
name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
)
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
original]
DECLARE @.sysCollation VARCHAR(128)
SELECT @.sysCollation = CONVERT(VARCHAR(128), DATABASEPROPERTYEX( 'TEST' ,
'Collation') )
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(10) + CHAR(9)
+ ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' +
CONVERT(VARCHAR(5) , CHARACTER_MAXIMUM_LENGTH ) + ') '
+ 'COLLATE ' + @.sysCollation + ' '
+ CASE IS_NULLABLE WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END +
CHAR(10) + 'GO'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
AND TABLE_NAME NOT LIKE 'sys%'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
GO
PRINT 'cleanup'
USE master
GO
DROP DATABASE TEST
the resultin ALTER TABLE statements,
ALTER TABLE dbo.TestTB
ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
GO
ALTER TABLE dbo.TestTB2
ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
GO
in this case, can be then clipped and executed to set the desired collation
setting...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very very much!!!
You help me a lot!!!!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> escribi en el mensaje
news:2s7q7pF1h8u71U1@.uni-berlin.de...
> hi again, Jorge,
> actually it can be done without a cursor, but yesterday night I was too
> tired... =;-D
> I appologize...
> SET NOCOUNT ON
> SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master and sys
> databases collation]
> GO
> CREATE DATABASE TEST
> GO
> USE TEST
> GO
> CREATE TABLE dbo.TestTB (
> ID INT NOT NULL ,
> name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
> )
> INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
> CREATE TABLE dbo.TestTB2 (
> ID INT NOT NULL ,
> name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
> )
> GO
> SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database
> collation
> original]
> DECLARE @.sysCollation VARCHAR(128)
> SELECT @.sysCollation = CONVERT(VARCHAR(128), DATABASEPROPERTYEX( 'TEST' ,
> 'Collation') )
> SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(10) +
> CHAR(9)
> + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' +
> CONVERT(VARCHAR(5) , CHARACTER_MAXIMUM_LENGTH ) + ') '
> + 'COLLATE ' + @.sysCollation + ' '
> + CASE IS_NULLABLE WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END +
> CHAR(10) + 'GO'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_CATALOG = 'TEST'
> AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
> AND TABLE_NAME NOT LIKE 'sys%'
> ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
> GO
> PRINT 'cleanup'
> USE master
> GO
> DROP DATABASE TEST
> the resultin ALTER TABLE statements,
> ALTER TABLE dbo.TestTB
> ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
> GO
> ALTER TABLE dbo.TestTB2
> ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
> GO
> in this case, can be then clipped and executed to set the desired
> collation
> setting...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
customers.
They install MSDE 2000, and I can't garantee what COLLATION they will use.
How I can modify the COLLATION of the database after restoring it?
Thanks in advance.
hi Jorge,
"Jorge Brizuela" <jorge_brizuela@.spymac.com> ha scritto nel messaggio
news:OckM6w$pEHA.2948@.TK2MSFTNGP11.phx.gbl
> I make backup from SQL Server 2000 and distribute this backup to our
> customers.
> They install MSDE 2000, and I can't garantee what COLLATION they will
> use.
> How I can modify the COLLATION of the database after restoring it?
> Thanks in advance.
as SQL Server 2000 supports different collations and sort orders for each
database, and even different collations at column level granularity, you
should not require altering that setting for your distributed database...
you can however use the
ALTER DATABASE db_name
COLLATE new_collation...
ALTER TABLE tbname
ALTER COLUMN colname type
COLLATE new_collation
so you have to alter the database setting, and then modify all varchar(n),
char(n) and text datatype columns accordingly...
that's to say
SET NOCOUNT ON
SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master an sys
databases collation]
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.TestTB (
ID INT NOT NULL ,
name VARCHAR(10) NOT NULL
)
INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
CREATE TABLE dbo.TestTB2 (
ID INT NOT NULL ,
name VARCHAR(10) NOT NULL
)
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
original]
GO
ALTER DATABASE TEST
COLLATE Latin1_General_CS_AI_KS_WS
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
after changing]
GO
PRINT 'alter each varchar(n), char(n) to the defined collation by HAND'
ALTER TABLE dbo.TestTB2
ALTER COLUMN [name] VARCHAR(10)
COLLATE Latin1_General_CS_AI_KS_WS
ALTER TABLE dbo.TestTB
ALTER COLUMN [name] VARCHAR(10)
COLLATE Latin1_General_CS_AI_KS_WS
GO
PRINT ''
PRINT '--'
PRINT 'alter each varchar(n), char(n) to the defined collation using a
cursor'
PRINT 'an ALTER TABLE ALTER COLUMN script will be written (not executed)'
DECLARE @.sql VARCHAR(2000)
DECLARE @.table_schema VARCHAR(255),
@.table_name VARCHAR(255),
@.column_name VARCHAR(255),
@.is_nullable VARCHAR(255),
@.data_type VARCHAR(255),
@.character_maximum_length VARCHAR(255)
DECLARE myCur CURSOR FOR
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
AND TABLE_NAME NOT LIKE 'sys%'
OPEN myCur
FETCH NEXT FROM myCur INTO @.table_schema,
@.table_name,
@.column_name,
@.is_nullable,
@.data_type,
@.character_maximum_length
WHILE @.@.FETCH_STATUS <> -1
BEGIN
SET @.sql = 'ALTER TABLE ' + @.table_schema + '.' + @.table_name
+ ' ALTER COLUMN ' + @.column_name + ' '
+ @.data_type + '(' + @.character_maximum_length + ') '
+ 'COLLATE Latin1_General_CS_AI_KS_WS '
+ CASE @.is_nullable WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END
PRINT @.sql
FETCH NEXT FROM myCur INTO @.table_schema,
@.table_name,
@.column_name,
@.is_nullable,
@.data_type,
@.character_maximum_length
END
CLOSE myCur
DEALLOCATE myCur
GO
PRINT 'drop all'
USE master
GO
DROP DATABASE TEST
as you can see... you can do it by hand, changing each column setting or
doing something handy like letting SQL Server writing the actual
Transact-SQL ALTER TABLE ALTER COLUMN ... statements you have to execute to
perform the desired result...
the actual statements will be
ALTER TABLE dbo.TestTB ALTER COLUMN name varchar(10) COLLATE
Latin1_General_CS_AI_KS_WS NULL
ALTER TABLE dbo.TestTB2 ALTER COLUMN name varchar(10) COLLATE
Latin1_General_CS_AI_KS_WS NULL
I do not love cursors, but this is one of the cases I could not find another
way to have the job done...
by the way, the problem you describe (it's no more a problem in SQL Server
2000) is one of the reasons I do not deploy my databases using restore
and/or sp_attach_db features... you are not "inheriting" all users database
settings like general sort order, model database specifi settings and
objects the end user placed in that template database...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi again, Jorge,
actually it can be done without a cursor, but yesterday night I was too
tired... =;-D
I appologize...
SET NOCOUNT ON
SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master and sys
databases collation]
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.TestTB (
ID INT NOT NULL ,
name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
)
INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
CREATE TABLE dbo.TestTB2 (
ID INT NOT NULL ,
name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
)
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
original]
DECLARE @.sysCollation VARCHAR(128)
SELECT @.sysCollation = CONVERT(VARCHAR(128), DATABASEPROPERTYEX( 'TEST' ,
'Collation') )
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(10) + CHAR(9)
+ ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' +
CONVERT(VARCHAR(5) , CHARACTER_MAXIMUM_LENGTH ) + ') '
+ 'COLLATE ' + @.sysCollation + ' '
+ CASE IS_NULLABLE WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END +
CHAR(10) + 'GO'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
AND TABLE_NAME NOT LIKE 'sys%'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
GO
PRINT 'cleanup'
USE master
GO
DROP DATABASE TEST
the resultin ALTER TABLE statements,
ALTER TABLE dbo.TestTB
ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
GO
ALTER TABLE dbo.TestTB2
ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
GO
in this case, can be then clipped and executed to set the desired collation
setting...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very very much!!!
You help me a lot!!!!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> escribi en el mensaje
news:2s7q7pF1h8u71U1@.uni-berlin.de...
> hi again, Jorge,
> actually it can be done without a cursor, but yesterday night I was too
> tired... =;-D
> I appologize...
> SET NOCOUNT ON
> SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master and sys
> databases collation]
> GO
> CREATE DATABASE TEST
> GO
> USE TEST
> GO
> CREATE TABLE dbo.TestTB (
> ID INT NOT NULL ,
> name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
> )
> INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
> CREATE TABLE dbo.TestTB2 (
> ID INT NOT NULL ,
> name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
> )
> GO
> SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database
> collation
> original]
> DECLARE @.sysCollation VARCHAR(128)
> SELECT @.sysCollation = CONVERT(VARCHAR(128), DATABASEPROPERTYEX( 'TEST' ,
> 'Collation') )
> SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(10) +
> CHAR(9)
> + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' +
> CONVERT(VARCHAR(5) , CHARACTER_MAXIMUM_LENGTH ) + ') '
> + 'COLLATE ' + @.sysCollation + ' '
> + CASE IS_NULLABLE WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END +
> CHAR(10) + 'GO'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_CATALOG = 'TEST'
> AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
> AND TABLE_NAME NOT LIKE 'sys%'
> ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
> GO
> PRINT 'cleanup'
> USE master
> GO
> DROP DATABASE TEST
> the resultin ALTER TABLE statements,
> ALTER TABLE dbo.TestTB
> ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
> GO
> ALTER TABLE dbo.TestTB2
> ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
> GO
> in this case, can be then clipped and executed to set the desired
> collation
> setting...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Subscribe to:
Posts (Atom)