I need to install EM and QA on some users machines, but I only want them to
have access to certain db's, What is best way to manage this? ALso, in QA I
want them to only be able to run Select/Update/Make Table queries. How do
I force a user to logon whe
n they open EM or QA with a certain login account?Hi,
1. Create a new login in sql server (Use enterprise manager -- Security
option)
2. Go to database and select users option and add a new user
3. Assign that user db_reader and db_writer role (this will only allow that
user to browse and update/insert/delete contents in this database)
4. Login to QA as SA user
5. Select the database in which the user need create table prev.
6. Execute "Grant CREATE TABLE to <User name>
If you need to restrict the Insert and Delete from tables - Have a look into
DENY command .
Tahnks
Hari
MCDBA
"mikeb" <anonymous@.discussions.microsoft.com> wrote in message
news:AAC88C32-6D4C-44C8-B6D8-607F078F6A7A@.microsoft.com...
> I need to install EM and QA on some users machines, but I only want them
to have access to certain db's, What is best way to manage this? ALso, in
QA I want them to only be able to run Select/Update/Make Table queries. How
do I force a user to logon when they open EM or QA with a certain login
account?
Showing posts with label install. Show all posts
Showing posts with label install. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
restoring system databases
I've been testing a new 2005 server. After the install, I backed up the
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
the[vbcol=seagreen]
and[vbcol=seagreen]
before[vbcol=seagreen]
>|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> with
> the
> and
> before
>|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
disappear?[vbcol=seagreen]
>sql
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
the[vbcol=seagreen]
and[vbcol=seagreen]
before[vbcol=seagreen]
>|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> with
> the
> and
> before
>|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
disappear?[vbcol=seagreen]
>sql
restoring system databases
I've been testing a new 2005 server. After the install, I backed up the
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?
If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>
|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with[vbcol=seagreen]
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
the[vbcol=seagreen]
and[vbcol=seagreen]
before
>
|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> with
> the
> and
> before
>
|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it[vbcol=seagreen]
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
disappear?
>
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?
If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>
|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with[vbcol=seagreen]
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
the[vbcol=seagreen]
and[vbcol=seagreen]
before
>
|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> with
> the
> and
> before
>
|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it[vbcol=seagreen]
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
disappear?
>
restoring system databases
I've been testing a new 2005 server. After the install, I backed up the
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> > I've been testing a new 2005 server. After the install, I backed up
the
> > system databases. I've done a lot of junk testing stuff since then,
and
> > now I'm wondering if I should do a restore of the system databases
before
> > I
> > put the machine into production. If so, which ones?
> >
> >
>|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
--
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
>> If you want a clean start, consider running rebuildmaster - rerun setup
> with
>> rebuild flag.
>> e.g.
>> setup.exe REBUILDDATABASE=1
>>
>> --
>> -oj
>>
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
>> > I've been testing a new 2005 server. After the install, I backed up
> the
>> > system databases. I've done a lot of junk testing stuff since then,
> and
>> > now I'm wondering if I should do a restore of the system databases
> before
>> > I
>> > put the machine into production. If so, which ones?
>> >
>> >
>>
>|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> > Does that put it into the same state as just after an install?
> >
> > If I had a test database still showing, for example, would that
disappear?
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> >> If you want a clean start, consider running rebuildmaster - rerun setup
> > with
> >> rebuild flag.
> >>
> >> e.g.
> >> setup.exe REBUILDDATABASE=1
> >>
> >>
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "HK" <replywithingroup@.notreal.com> wrote in message
> >> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> >> > I've been testing a new 2005 server. After the install, I backed up
> > the
> >> > system databases. I've done a lot of junk testing stuff since then,
> > and
> >> > now I'm wondering if I should do a restore of the system databases
> > before
> >> > I
> >> > put the machine into production. If so, which ones?
> >> >
> >> >
> >>
> >>
> >
> >
>
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> > I've been testing a new 2005 server. After the install, I backed up
the
> > system databases. I've done a lot of junk testing stuff since then,
and
> > now I'm wondering if I should do a restore of the system databases
before
> > I
> > put the machine into production. If so, which ones?
> >
> >
>|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
--
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
>> If you want a clean start, consider running rebuildmaster - rerun setup
> with
>> rebuild flag.
>> e.g.
>> setup.exe REBUILDDATABASE=1
>>
>> --
>> -oj
>>
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
>> > I've been testing a new 2005 server. After the install, I backed up
> the
>> > system databases. I've done a lot of junk testing stuff since then,
> and
>> > now I'm wondering if I should do a restore of the system databases
> before
>> > I
>> > put the machine into production. If so, which ones?
>> >
>> >
>>
>|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> > Does that put it into the same state as just after an install?
> >
> > If I had a test database still showing, for example, would that
disappear?
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> >> If you want a clean start, consider running rebuildmaster - rerun setup
> > with
> >> rebuild flag.
> >>
> >> e.g.
> >> setup.exe REBUILDDATABASE=1
> >>
> >>
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "HK" <replywithingroup@.notreal.com> wrote in message
> >> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> >> > I've been testing a new 2005 server. After the install, I backed up
> > the
> >> > system databases. I've done a lot of junk testing stuff since then,
> > and
> >> > now I'm wondering if I should do a restore of the system databases
> > before
> >> > I
> >> > put the machine into production. If so, which ones?
> >> >
> >> >
> >>
> >>
> >
> >
>
Monday, March 12, 2012
Restoring master db from old install of different SQL version
All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM
Restoring master db from old install of different SQL version
All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPM
Hi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default...;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPM
Hi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default...;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM
Restoring master db from old install of different SQL version
All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piec
e
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/defaul...b;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piec
e
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/defaul...b;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM
Friday, March 9, 2012
restoring from network drive
Ok -I'm drawing a huge blank.
After a new install of SQL2K and SP3a, I'm unable to restore a backup from a
network drive.
SQL services are logging on with a domain account
The SQL service account has also been granted Local admin access on the PC
When trying to restore a database from Device, the only drive listed is the
local C: drive - none of the mapped drives are available.
JTTry entering a UNC name for the name of the backup, something like:
\\backupserver\sharename\backup\database
.bak
Also note the MSSQLSERVER service account will need access to the network
share.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"JT" <JT_*n*o*s*p*a*m*Goalie33@.cox.net> wrote in message
news:WAZzc.4489$cj3.1501@.lakeread01...
> Ok -I'm drawing a huge blank.
> After a new install of SQL2K and SP3a, I'm unable to restore a backup from
a
> network drive.
> SQL services are logging on with a domain account
> The SQL service account has also been granted Local admin access on the PC
> When trying to restore a database from Device, the only drive listed is
the
> local C: drive - none of the mapped drives are available.
> JT
>
After a new install of SQL2K and SP3a, I'm unable to restore a backup from a
network drive.
SQL services are logging on with a domain account
The SQL service account has also been granted Local admin access on the PC
When trying to restore a database from Device, the only drive listed is the
local C: drive - none of the mapped drives are available.
JTTry entering a UNC name for the name of the backup, something like:
\\backupserver\sharename\backup\database
.bak
Also note the MSSQLSERVER service account will need access to the network
share.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"JT" <JT_*n*o*s*p*a*m*Goalie33@.cox.net> wrote in message
news:WAZzc.4489$cj3.1501@.lakeread01...
> Ok -I'm drawing a huge blank.
> After a new install of SQL2K and SP3a, I'm unable to restore a backup from
a
> network drive.
> SQL services are logging on with a domain account
> The SQL service account has also been granted Local admin access on the PC
> When trying to restore a database from Device, the only drive listed is
the
> local C: drive - none of the mapped drives are available.
> JT
>
restoring from network drive
Ok -I'm drawing a huge blank.
After a new install of SQL2K and SP3a, I'm unable to restore a backup from a
network drive.
SQL services are logging on with a domain account
The SQL service account has also been granted Local admin access on the PC
When trying to restore a database from Device, the only drive listed is the
local C: drive - none of the mapped drives are available.
JT
Try entering a UNC name for the name of the backup, something like:
\\backupserver\sharename\backup\database.bak
Also note the MSSQLSERVER service account will need access to the network
share.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"JT" <JT_*n*o*s*p*a*m*Goalie33@.cox.net> wrote in message
news:WAZzc.4489$cj3.1501@.lakeread01...
> Ok -I'm drawing a huge blank.
> After a new install of SQL2K and SP3a, I'm unable to restore a backup from
a
> network drive.
> SQL services are logging on with a domain account
> The SQL service account has also been granted Local admin access on the PC
> When trying to restore a database from Device, the only drive listed is
the
> local C: drive - none of the mapped drives are available.
> JT
>
After a new install of SQL2K and SP3a, I'm unable to restore a backup from a
network drive.
SQL services are logging on with a domain account
The SQL service account has also been granted Local admin access on the PC
When trying to restore a database from Device, the only drive listed is the
local C: drive - none of the mapped drives are available.
JT
Try entering a UNC name for the name of the backup, something like:
\\backupserver\sharename\backup\database.bak
Also note the MSSQLSERVER service account will need access to the network
share.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"JT" <JT_*n*o*s*p*a*m*Goalie33@.cox.net> wrote in message
news:WAZzc.4489$cj3.1501@.lakeread01...
> Ok -I'm drawing a huge blank.
> After a new install of SQL2K and SP3a, I'm unable to restore a backup from
a
> network drive.
> SQL services are logging on with a domain account
> The SQL service account has also been granted Local admin access on the PC
> When trying to restore a database from Device, the only drive listed is
the
> local C: drive - none of the mapped drives are available.
> JT
>
restoring from network drive
Ok -I'm drawing a huge blank.
After a new install of SQL2K and SP3a, I'm unable to restore a backup from a
network drive.
SQL services are logging on with a domain account
The SQL service account has also been granted Local admin access on the PC
When trying to restore a database from Device, the only drive listed is the
local C: drive - none of the mapped drives are available.
JTTry entering a UNC name for the name of the backup, something like:
\\backupserver\sharename\backup\database.bak
Also note the MSSQLSERVER service account will need access to the network
share.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"JT" <JT_*n*o*s*p*a*m*Goalie33@.cox.net> wrote in message
news:WAZzc.4489$cj3.1501@.lakeread01...
> Ok -I'm drawing a huge blank.
> After a new install of SQL2K and SP3a, I'm unable to restore a backup from
a
> network drive.
> SQL services are logging on with a domain account
> The SQL service account has also been granted Local admin access on the PC
> When trying to restore a database from Device, the only drive listed is
the
> local C: drive - none of the mapped drives are available.
> JT
>|||SQL Server will only show local drives/partitions on the
computer you are trying to restore to...Use UNC names/IP
adresses when restoring from a network drive.
EX:
RESTORE DATABASE MYDATABASE
FROM DISK = '\\SERVER1\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDATABASE\MYDATABASE.BAK'
WITH
DBO_ONLY,
REPLACE,
STANDBY = '\\SERVER1\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDATABASE\'UNDO_MYDATABASE.ldf',
MOVE 'MYDATABASE_Data' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\MYDATABASE_Data.mdf',
MOVE 'MYDATABASE_Log' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\MYDATABASE_Log.ldf'
e.t.c
>--Original Message--
>Ok -I'm drawing a huge blank.
>After a new install of SQL2K and SP3a, I'm unable to
restore a backup from a
>network drive.
>SQL services are logging on with a domain account
>The SQL service account has also been granted Local admin
access on the PC
>When trying to restore a database from Device, the only
drive listed is the
>local C: drive - none of the mapped drives are available.
>JT
>
>.
>
After a new install of SQL2K and SP3a, I'm unable to restore a backup from a
network drive.
SQL services are logging on with a domain account
The SQL service account has also been granted Local admin access on the PC
When trying to restore a database from Device, the only drive listed is the
local C: drive - none of the mapped drives are available.
JTTry entering a UNC name for the name of the backup, something like:
\\backupserver\sharename\backup\database.bak
Also note the MSSQLSERVER service account will need access to the network
share.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"JT" <JT_*n*o*s*p*a*m*Goalie33@.cox.net> wrote in message
news:WAZzc.4489$cj3.1501@.lakeread01...
> Ok -I'm drawing a huge blank.
> After a new install of SQL2K and SP3a, I'm unable to restore a backup from
a
> network drive.
> SQL services are logging on with a domain account
> The SQL service account has also been granted Local admin access on the PC
> When trying to restore a database from Device, the only drive listed is
the
> local C: drive - none of the mapped drives are available.
> JT
>|||SQL Server will only show local drives/partitions on the
computer you are trying to restore to...Use UNC names/IP
adresses when restoring from a network drive.
EX:
RESTORE DATABASE MYDATABASE
FROM DISK = '\\SERVER1\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDATABASE\MYDATABASE.BAK'
WITH
DBO_ONLY,
REPLACE,
STANDBY = '\\SERVER1\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDATABASE\'UNDO_MYDATABASE.ldf',
MOVE 'MYDATABASE_Data' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\MYDATABASE_Data.mdf',
MOVE 'MYDATABASE_Log' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\MYDATABASE_Log.ldf'
e.t.c
>--Original Message--
>Ok -I'm drawing a huge blank.
>After a new install of SQL2K and SP3a, I'm unable to
restore a backup from a
>network drive.
>SQL services are logging on with a domain account
>The SQL service account has also been granted Local admin
access on the PC
>When trying to restore a database from Device, the only
drive listed is the
>local C: drive - none of the mapped drives are available.
>JT
>
>.
>
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
>
Restoring database to Win 2003 Server
We are restoring a database to a Windows 2003 Server. After the install it
created a Data folder at E:\Microsoft SQL Server. I'm a local admin on the
machine and yet whenever I try to drill down into E:\Microsoft SQL
Server\MSSQL\, it gives me the error message "E:\Microsoft SQL Server\MSSQL
is not accessible. Access is denied."
I was hoping to create several subdirectories in this folder to structure
how our database .mdf files are structured. Suggestions? Am I missing
something? Is there something about SQL Server on 2003 that we should be
aware of?
Thanks in advance!
Mark
field027@.umn.eduMy guess is that the SQL Server service account lacks some permission on
that drive/directory.
--
Tibor Karaszi
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
news:ubJmEvkoDHA.2188@.TK2MSFTNGP11.phx.gbl...
> We are restoring a database to a Windows 2003 Server. After the install
it
> created a Data folder at E:\Microsoft SQL Server. I'm a local admin on
the
> machine and yet whenever I try to drill down into E:\Microsoft SQL
> Server\MSSQL\, it gives me the error message "E:\Microsoft SQL
Server\MSSQL
> is not accessible. Access is denied."
> I was hoping to create several subdirectories in this folder to structure
> how our database .mdf files are structured. Suggestions? Am I missing
> something? Is there something about SQL Server on 2003 that we should be
> aware of?
> Thanks in advance!
> Mark
> field027@.umn.edu
>
created a Data folder at E:\Microsoft SQL Server. I'm a local admin on the
machine and yet whenever I try to drill down into E:\Microsoft SQL
Server\MSSQL\, it gives me the error message "E:\Microsoft SQL Server\MSSQL
is not accessible. Access is denied."
I was hoping to create several subdirectories in this folder to structure
how our database .mdf files are structured. Suggestions? Am I missing
something? Is there something about SQL Server on 2003 that we should be
aware of?
Thanks in advance!
Mark
field027@.umn.eduMy guess is that the SQL Server service account lacks some permission on
that drive/directory.
--
Tibor Karaszi
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
news:ubJmEvkoDHA.2188@.TK2MSFTNGP11.phx.gbl...
> We are restoring a database to a Windows 2003 Server. After the install
it
> created a Data folder at E:\Microsoft SQL Server. I'm a local admin on
the
> machine and yet whenever I try to drill down into E:\Microsoft SQL
> Server\MSSQL\, it gives me the error message "E:\Microsoft SQL
Server\MSSQL
> is not accessible. Access is denied."
> I was hoping to create several subdirectories in this folder to structure
> how our database .mdf files are structured. Suggestions? Am I missing
> something? Is there something about SQL Server on 2003 that we should be
> aware of?
> Thanks in advance!
> Mark
> field027@.umn.edu
>
Subscribe to:
Posts (Atom)