In Oracle & Sybase you can set login resource limits to restrict, time of day, number of i-o's, cpu use etc for any login, at the login method. All actions done from a resource limit constrained login inherit the constraints.
Is there any way to implment this with any MS SQL Server version from 200-2005/SP2?
Before anyone answers, please do not suggest set rowcount etc.
thanks.I don't see this kind of feature available yet in any of the versions of SQL Server, however workaround are the only possible methods like the one you have suggested.sql
Showing posts with label method. Show all posts
Showing posts with label method. Show all posts
Friday, March 30, 2012
resolve login problem after moving db
I'm moving databases from one server to another server.
Then I use the method in microsoft article 246133, to transfer logins.
1. If I use transfer logins dts task, it creates orphaned users. but default
database goes to the right one.
2. If I use the script that make use of the sp: sp_help_revlogin
It seems work OK, no orphaned users. But I found the default database all
goes to
master, instead of other databases as in the old server.
Then I need to reset the default database manually one by one.
Is that the way the scrip works? It seems it solves one problem but have
another?
Any solutions
Thanks
You need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
defaults to what's available.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> I'm moving databases from one server to another server.
> Then I use the method in microsoft article 246133, to transfer logins.
> 1. If I use transfer logins dts task, it creates orphaned users. but
default
> database goes to the right one.
> 2. If I use the script that make use of the sp: sp_help_revlogin
> It seems work OK, no orphaned users. But I found the default database all
> goes to
> master, instead of other databases as in the old server.
> Then I need to reset the default database manually one by one.
> Is that the way the scrip works? It seems it solves one problem but have
> another?
> Any solutions
> Thanks
|||Thank you. But that's exactly what I did. The databases were all there first.
Then I ran the output of sp_help_revlogin generated script.
I also take a look at the script, there is nothing indicating default
database.
It just create logins with the original sid and password.
Any further help?
Thanks
"Derrick Leggett" wrote:
> You need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
> defaults to what's available.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> default
>
>
Then I use the method in microsoft article 246133, to transfer logins.
1. If I use transfer logins dts task, it creates orphaned users. but default
database goes to the right one.
2. If I use the script that make use of the sp: sp_help_revlogin
It seems work OK, no orphaned users. But I found the default database all
goes to
master, instead of other databases as in the old server.
Then I need to reset the default database manually one by one.
Is that the way the scrip works? It seems it solves one problem but have
another?
Any solutions
Thanks
You need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
defaults to what's available.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> I'm moving databases from one server to another server.
> Then I use the method in microsoft article 246133, to transfer logins.
> 1. If I use transfer logins dts task, it creates orphaned users. but
default
> database goes to the right one.
> 2. If I use the script that make use of the sp: sp_help_revlogin
> It seems work OK, no orphaned users. But I found the default database all
> goes to
> master, instead of other databases as in the old server.
> Then I need to reset the default database manually one by one.
> Is that the way the scrip works? It seems it solves one problem but have
> another?
> Any solutions
> Thanks
|||Thank you. But that's exactly what I did. The databases were all there first.
Then I ran the output of sp_help_revlogin generated script.
I also take a look at the script, there is nothing indicating default
database.
It just create logins with the original sid and password.
Any further help?
Thanks
"Derrick Leggett" wrote:
> You need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
> defaults to what's available.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> default
>
>
resolve login problem after moving db
I'm moving databases from one server to another server.
Then I use the method in microsoft article 246133, to transfer logins.
1. If I use transfer logins dts task, it creates orphaned users. but default
database goes to the right one.
2. If I use the script that make use of the sp: sp_help_revlogin
It seems work OK, no orphaned users. But I found the default database all
goes to
master, instead of other databases as in the old server.
Then I need to reset the default database manually one by one.
Is that the way the scrip works? It seems it solves one problem but have
another?
Any solutions
ThanksYou need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
defaults to what's available.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> I'm moving databases from one server to another server.
> Then I use the method in microsoft article 246133, to transfer logins.
> 1. If I use transfer logins dts task, it creates orphaned users. but
default
> database goes to the right one.
> 2. If I use the script that make use of the sp: sp_help_revlogin
> It seems work OK, no orphaned users. But I found the default database all
> goes to
> master, instead of other databases as in the old server.
> Then I need to reset the default database manually one by one.
> Is that the way the scrip works? It seems it solves one problem but have
> another?
> Any solutions
> Thanks|||Thank you. But that's exactly what I did. The databases were all there first
.
Then I ran the output of sp_help_revlogin generated script.
I also take a look at the script, there is nothing indicating default
database.
It just create logins with the original sid and password.
Any further help?
Thanks
"Derrick Leggett" wrote:
> You need to run sp_help_revlogin AFTER you add the databases. Otherwise,
it
> defaults to what's available.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> default
>
>sql
Then I use the method in microsoft article 246133, to transfer logins.
1. If I use transfer logins dts task, it creates orphaned users. but default
database goes to the right one.
2. If I use the script that make use of the sp: sp_help_revlogin
It seems work OK, no orphaned users. But I found the default database all
goes to
master, instead of other databases as in the old server.
Then I need to reset the default database manually one by one.
Is that the way the scrip works? It seems it solves one problem but have
another?
Any solutions
ThanksYou need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
defaults to what's available.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> I'm moving databases from one server to another server.
> Then I use the method in microsoft article 246133, to transfer logins.
> 1. If I use transfer logins dts task, it creates orphaned users. but
default
> database goes to the right one.
> 2. If I use the script that make use of the sp: sp_help_revlogin
> It seems work OK, no orphaned users. But I found the default database all
> goes to
> master, instead of other databases as in the old server.
> Then I need to reset the default database manually one by one.
> Is that the way the scrip works? It seems it solves one problem but have
> another?
> Any solutions
> Thanks|||Thank you. But that's exactly what I did. The databases were all there first
.
Then I ran the output of sp_help_revlogin generated script.
I also take a look at the script, there is nothing indicating default
database.
It just create logins with the original sid and password.
Any further help?
Thanks
"Derrick Leggett" wrote:
> You need to run sp_help_revlogin AFTER you add the databases. Otherwise,
it
> defaults to what's available.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> default
>
>sql
Wednesday, March 28, 2012
resolve login problem after moving db
I'm moving databases from one server to another server.
Then I use the method in microsoft article 246133, to transfer logins.
1. If I use transfer logins dts task, it creates orphaned users. but default
database goes to the right one.
2. If I use the script that make use of the sp: sp_help_revlogin
It seems work OK, no orphaned users. But I found the default database all
goes to
master, instead of other databases as in the old server.
Then I need to reset the default database manually one by one.
Is that the way the scrip works? It seems it solves one problem but have
another?
Any solutions
ThanksYou need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
defaults to what's available.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> I'm moving databases from one server to another server.
> Then I use the method in microsoft article 246133, to transfer logins.
> 1. If I use transfer logins dts task, it creates orphaned users. but
default
> database goes to the right one.
> 2. If I use the script that make use of the sp: sp_help_revlogin
> It seems work OK, no orphaned users. But I found the default database all
> goes to
> master, instead of other databases as in the old server.
> Then I need to reset the default database manually one by one.
> Is that the way the scrip works? It seems it solves one problem but have
> another?
> Any solutions
> Thanks|||Thank you. But that's exactly what I did. The databases were all there first.
Then I ran the output of sp_help_revlogin generated script.
I also take a look at the script, there is nothing indicating default
database.
It just create logins with the original sid and password.
Any further help?
Thanks
"Derrick Leggett" wrote:
> You need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
> defaults to what's available.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> > I'm moving databases from one server to another server.
> >
> > Then I use the method in microsoft article 246133, to transfer logins.
> > 1. If I use transfer logins dts task, it creates orphaned users. but
> default
> > database goes to the right one.
> > 2. If I use the script that make use of the sp: sp_help_revlogin
> > It seems work OK, no orphaned users. But I found the default database all
> > goes to
> > master, instead of other databases as in the old server.
> > Then I need to reset the default database manually one by one.
> > Is that the way the scrip works? It seems it solves one problem but have
> > another?
> >
> > Any solutions
> > Thanks
>
>
Then I use the method in microsoft article 246133, to transfer logins.
1. If I use transfer logins dts task, it creates orphaned users. but default
database goes to the right one.
2. If I use the script that make use of the sp: sp_help_revlogin
It seems work OK, no orphaned users. But I found the default database all
goes to
master, instead of other databases as in the old server.
Then I need to reset the default database manually one by one.
Is that the way the scrip works? It seems it solves one problem but have
another?
Any solutions
ThanksYou need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
defaults to what's available.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> I'm moving databases from one server to another server.
> Then I use the method in microsoft article 246133, to transfer logins.
> 1. If I use transfer logins dts task, it creates orphaned users. but
default
> database goes to the right one.
> 2. If I use the script that make use of the sp: sp_help_revlogin
> It seems work OK, no orphaned users. But I found the default database all
> goes to
> master, instead of other databases as in the old server.
> Then I need to reset the default database manually one by one.
> Is that the way the scrip works? It seems it solves one problem but have
> another?
> Any solutions
> Thanks|||Thank you. But that's exactly what I did. The databases were all there first.
Then I ran the output of sp_help_revlogin generated script.
I also take a look at the script, there is nothing indicating default
database.
It just create logins with the original sid and password.
Any further help?
Thanks
"Derrick Leggett" wrote:
> You need to run sp_help_revlogin AFTER you add the databases. Otherwise, it
> defaults to what's available.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:435B1BBE-E32B-4320-BAE7-9EF764044CC4@.microsoft.com...
> > I'm moving databases from one server to another server.
> >
> > Then I use the method in microsoft article 246133, to transfer logins.
> > 1. If I use transfer logins dts task, it creates orphaned users. but
> default
> > database goes to the right one.
> > 2. If I use the script that make use of the sp: sp_help_revlogin
> > It seems work OK, no orphaned users. But I found the default database all
> > goes to
> > master, instead of other databases as in the old server.
> > Then I need to reset the default database manually one by one.
> > Is that the way the scrip works? It seems it solves one problem but have
> > another?
> >
> > Any solutions
> > Thanks
>
>
Monday, March 26, 2012
Reseting Identity Seed
hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
Ansari
Check out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
Ansari
Check out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>
Reseting Identity Seed
hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.
gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that..
.
> one method is to truncate table... but if table is used as parent in forei
gn
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.
gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that..
.
> one method is to truncate table... but if table is used as parent in forei
gn
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>
Reseting Identity Seed
hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>
reset sa password
Hi,
Is there is a method to reset the 'sa' password? I have a
server that moved between few different domains and I am
not sure which domain accounts have access to it. And We
do not know the 'sa' password.
I thought there was a registry key that could be removed
to reset the password? Is that possible?
Thanks,If you log onto the server with a different system admin user, or a
local/domain windows administrator, you can use master..sp_password.
--
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,|||anonymous@.discussions.microsoft.com wrote:
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,
You need to connect using another admin account and then reset the
password on the "sa" account. If you do not have any other admin
accounts, I don't think you can reset the password. You may have to
reinstall SQL Server and restore/reattach your databases.
--
David G.|||If you can not connect via sa and you have removed
the 'builtin\administrators' account, you are going to be
out-of-luck.
It seems that you have to re-install and get your db's
back from backups.
hth
DeeJay
>--Original Message--
>Hi,
>Is there is a method to reset the 'sa' password? I have a
>server that moved between few different domains and I am
>not sure which domain accounts have access to it. And We
>do not know the 'sa' password.
>I thought there was a registry key that could be removed
>to reset the password? Is that possible?
>Thanks,
>.
>|||Well, you should be able to shut SQL Server down gracefully from the
Services control panel applet, so you might only have to re-attach your
existing MDF/LDF files after you reinstall.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
> If you can not connect via sa and you have removed
> the 'builtin\administrators' account, you are going to be
> out-of-luck.
> It seems that you have to re-install and get your db's
> back from backups.
> hth
> DeeJay
> >--Original Message--
> >Hi,
> >
> >Is there is a method to reset the 'sa' password? I have a
> >server that moved between few different domains and I am
> >not sure which domain accounts have access to it. And We
> >do not know the 'sa' password.
> >
> >I thought there was a registry key that could be removed
> >to reset the password? Is that possible?
> >
> >Thanks,
> >.
> >|||> It seems that you have to re-install and get your db's
> back from backups.
Or rebuildm.exe, which is essentially a reinstall without the file-copy phase... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
> If you can not connect via sa and you have removed
> the 'builtin\administrators' account, you are going to be
> out-of-luck.
> It seems that you have to re-install and get your db's
> back from backups.
> hth
> DeeJay
> >--Original Message--
> >Hi,
> >
> >Is there is a method to reset the 'sa' password? I have a
> >server that moved between few different domains and I am
> >not sure which domain accounts have access to it. And We
> >do not know the 'sa' password.
> >
> >I thought there was a registry key that could be removed
> >to reset the password? Is that possible?
> >
> >Thanks,
> >.
> >|||I thought the db's have to detached to be re-attached?
Also, attaching the db's is not going to help resolve
the 'sa' password. But it will keep downtime to minimum,
if he/she is able to attach the db's.
DeeJay
>--Original Message--
>Well, you should be able to shut SQL Server down
gracefully from the
>Services control panel applet, so you might only have to
re-attach your
>existing MDF/LDF files after you reinstall.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
>> If you can not connect via sa and you have removed
>> the 'builtin\administrators' account, you are going to
be
>> out-of-luck.
>> It seems that you have to re-install and get your db's
>> back from backups.
>> hth
>> DeeJay
>> >--Original Message--
>> >Hi,
>> >
>> >Is there is a method to reset the 'sa' password? I
have a
>> >server that moved between few different domains and I
am
>> >not sure which domain accounts have access to it. And
We
>> >do not know the 'sa' password.
>> >
>> >I thought there was a registry key that could be
removed
>> >to reset the password? Is that possible?
>> >
>> >Thanks,
>> >.
>> >
>
>.
>|||> I thought the db's have to detached to be re-attached?
In many cases, attaching work even if you didn't detach first. But you are right in saying that this is *not*
something to count on.
> Also, attaching the db's is not going to help resolve
> the 'sa' password.
It depends on which database we are talking about.
Sa is a login, stored in master.
You can rebuild the master database.
You now have a known password for sa.
And just attach the user databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message news:c90f01c48a20$9de73b60$a401280a@.phx.gbl...
> I thought the db's have to detached to be re-attached?
> Also, attaching the db's is not going to help resolve
> the 'sa' password. But it will keep downtime to minimum,
> if he/she is able to attach the db's.
> DeeJay
> >--Original Message--
> >Well, you should be able to shut SQL Server down
> gracefully from the
> >Services control panel applet, so you might only have to
> re-attach your
> >existing MDF/LDF files after you reinstall.
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> >news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
> >> If you can not connect via sa and you have removed
> >> the 'builtin\administrators' account, you are going to
> be
> >> out-of-luck.
> >>
> >> It seems that you have to re-install and get your db's
> >> back from backups.
> >>
> >> hth
> >>
> >> DeeJay
> >> >--Original Message--
> >> >Hi,
> >> >
> >> >Is there is a method to reset the 'sa' password? I
> have a
> >> >server that moved between few different domains and I
> am
> >> >not sure which domain accounts have access to it. And
> We
> >> >do not know the 'sa' password.
> >> >
> >> >I thought there was a registry key that could be
> removed
> >> >to reset the password? Is that possible?
> >> >
> >> >Thanks,
> >> >.
> >> >
> >
> >
> >.
> >|||That's true. I assumed we are talking about user databases
because its generally not a good idea to detach and attach
the system databases to a different server, is it?
Also, I was not sure what rebuilding the master db do the
sa login. Once the master db is rebuilt, what is the
password for 'sa'? blank? or initial password used during
sw installation?
DeeJay
>--Original Message--
>> I thought the db's have to detached to be re-attached?
>In many cases, attaching work even if you didn't detach
first. But you are right in saying that this is *not*
>something to count on.
>
>> Also, attaching the db's is not going to help resolve
>> the 'sa' password.
>It depends on which database we are talking about.
>Sa is a login, stored in master.
>You can rebuild the master database.
>You now have a known password for sa.
>And just attach the user databases.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:c90f01c48a20$9de73b60$a401280a@.phx.gbl...
>> I thought the db's have to detached to be re-attached?
>> Also, attaching the db's is not going to help resolve
>> the 'sa' password. But it will keep downtime to minimum,
>> if he/she is able to attach the db's.
>> DeeJay
>> >--Original Message--
>> >Well, you should be able to shut SQL Server down
>> gracefully from the
>> >Services control panel applet, so you might only have
to
>> re-attach your
>> >existing MDF/LDF files after you reinstall.
>> >
>> >--
>> >http://www.aspfaq.com/
>> >(Reverse address to reply.)
>> >
>> >
>> >
>> >
>> >"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>> >news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
>> >> If you can not connect via sa and you have removed
>> >> the 'builtin\administrators' account, you are going
to
>> be
>> >> out-of-luck.
>> >>
>> >> It seems that you have to re-install and get your
db's
>> >> back from backups.
>> >>
>> >> hth
>> >>
>> >> DeeJay
>> >> >--Original Message--
>> >> >Hi,
>> >> >
>> >> >Is there is a method to reset the 'sa' password? I
>> have a
>> >> >server that moved between few different domains and
I
>> am
>> >> >not sure which domain accounts have access to it.
And
>> We
>> >> >do not know the 'sa' password.
>> >> >
>> >> >I thought there was a registry key that could be
>> removed
>> >> >to reset the password? Is that possible?
>> >> >
>> >> >Thanks,
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||> I thought the db's have to detached to be re-attached?
I'm pretty sure that if SQL Server is allowed to be shut down successfully,
that the files will be detached successfully. No guarantee, of course, but
it has worked in all cases where I tried it.
> Also, attaching the db's is not going to help resolve
> the 'sa' password.
No, and that's not why I mentioned it. I was only commenting on the
"re-install and get your db's back from backups" comment.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Sounds Good!
>--Original Message--
>> I thought the db's have to detached to be re-attached?
>I'm pretty sure that if SQL Server is allowed to be shut
down successfully,
>that the files will be detached successfully. No
guarantee, of course, but
>it has worked in all cases where I tried it.
>> Also, attaching the db's is not going to help resolve
>> the 'sa' password.
>No, and that's not why I mentioned it. I was only
commenting on the
>"re-install and get your db's back from backups" comment.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||I lost my password for sa. So, I do the following, but it doesn't seem to
work.
1) I login to SQL Server thru the query analyzer using Windows
Authentication.
2) sp_password NULL, 'password', 'sa'
3) It indicates with "Password changed"
4) I log out from the query analyzer.
5) I try login to SQL Server thru query analyzer using SQL Server
authentication.
6) It indicates with a login error, and it says "Reason: Not associated
with a trusted SQL Server connection.
Does anyone shine some light?
Thanks.
J
"Aaron [SQL Server MVP]" wrote:
> If you log onto the server with a different system admin user, or a
> local/domain windows administrator, you can use master..sp_password.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> > Hi,
> >
> > Is there is a method to reset the 'sa' password? I have a
> > server that moved between few different domains and I am
> > not sure which domain accounts have access to it. And We
> > do not know the 'sa' password.
> >
> > I thought there was a registry key that could be removed
> > to reset the password? Is that possible?
> >
> > Thanks,
>
>|||Already answered in another thread...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejackofall" <thejackofall@.discussions.microsoft.com> wrote in message
news:05AFF2D7-8F2A-4D75-9A0F-83DFB40E17CF@.microsoft.com...
> I lost my password for sa. So, I do the following, but it doesn't seem to
> work.
> 1) I login to SQL Server thru the query analyzer using Windows
> Authentication.
> 2) sp_password NULL, 'password', 'sa'
> 3) It indicates with "Password changed"
> 4) I log out from the query analyzer.
> 5) I try login to SQL Server thru query analyzer using SQL Server
> authentication.
> 6) It indicates with a login error, and it says "Reason: Not associated
> with a trusted SQL Server connection.
> Does anyone shine some light?
> Thanks.
> J
> "Aaron [SQL Server MVP]" wrote:
> > If you log onto the server with a different system admin user, or a
> > local/domain windows administrator, you can use master..sp_password.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > <anonymous@.discussions.microsoft.com> wrote in message
> > news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> > > Hi,
> > >
> > > Is there is a method to reset the 'sa' password? I have a
> > > server that moved between few different domains and I am
> > > not sure which domain accounts have access to it. And We
> > > do not know the 'sa' password.
> > >
> > > I thought there was a registry key that could be removed
> > > to reset the password? Is that possible?
> > >
> > > Thanks,
> >
> >
> >
Is there is a method to reset the 'sa' password? I have a
server that moved between few different domains and I am
not sure which domain accounts have access to it. And We
do not know the 'sa' password.
I thought there was a registry key that could be removed
to reset the password? Is that possible?
Thanks,If you log onto the server with a different system admin user, or a
local/domain windows administrator, you can use master..sp_password.
--
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,|||anonymous@.discussions.microsoft.com wrote:
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,
You need to connect using another admin account and then reset the
password on the "sa" account. If you do not have any other admin
accounts, I don't think you can reset the password. You may have to
reinstall SQL Server and restore/reattach your databases.
--
David G.|||If you can not connect via sa and you have removed
the 'builtin\administrators' account, you are going to be
out-of-luck.
It seems that you have to re-install and get your db's
back from backups.
hth
DeeJay
>--Original Message--
>Hi,
>Is there is a method to reset the 'sa' password? I have a
>server that moved between few different domains and I am
>not sure which domain accounts have access to it. And We
>do not know the 'sa' password.
>I thought there was a registry key that could be removed
>to reset the password? Is that possible?
>Thanks,
>.
>|||Well, you should be able to shut SQL Server down gracefully from the
Services control panel applet, so you might only have to re-attach your
existing MDF/LDF files after you reinstall.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
> If you can not connect via sa and you have removed
> the 'builtin\administrators' account, you are going to be
> out-of-luck.
> It seems that you have to re-install and get your db's
> back from backups.
> hth
> DeeJay
> >--Original Message--
> >Hi,
> >
> >Is there is a method to reset the 'sa' password? I have a
> >server that moved between few different domains and I am
> >not sure which domain accounts have access to it. And We
> >do not know the 'sa' password.
> >
> >I thought there was a registry key that could be removed
> >to reset the password? Is that possible?
> >
> >Thanks,
> >.
> >|||> It seems that you have to re-install and get your db's
> back from backups.
Or rebuildm.exe, which is essentially a reinstall without the file-copy phase... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
> If you can not connect via sa and you have removed
> the 'builtin\administrators' account, you are going to be
> out-of-luck.
> It seems that you have to re-install and get your db's
> back from backups.
> hth
> DeeJay
> >--Original Message--
> >Hi,
> >
> >Is there is a method to reset the 'sa' password? I have a
> >server that moved between few different domains and I am
> >not sure which domain accounts have access to it. And We
> >do not know the 'sa' password.
> >
> >I thought there was a registry key that could be removed
> >to reset the password? Is that possible?
> >
> >Thanks,
> >.
> >|||I thought the db's have to detached to be re-attached?
Also, attaching the db's is not going to help resolve
the 'sa' password. But it will keep downtime to minimum,
if he/she is able to attach the db's.
DeeJay
>--Original Message--
>Well, you should be able to shut SQL Server down
gracefully from the
>Services control panel applet, so you might only have to
re-attach your
>existing MDF/LDF files after you reinstall.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
>> If you can not connect via sa and you have removed
>> the 'builtin\administrators' account, you are going to
be
>> out-of-luck.
>> It seems that you have to re-install and get your db's
>> back from backups.
>> hth
>> DeeJay
>> >--Original Message--
>> >Hi,
>> >
>> >Is there is a method to reset the 'sa' password? I
have a
>> >server that moved between few different domains and I
am
>> >not sure which domain accounts have access to it. And
We
>> >do not know the 'sa' password.
>> >
>> >I thought there was a registry key that could be
removed
>> >to reset the password? Is that possible?
>> >
>> >Thanks,
>> >.
>> >
>
>.
>|||> I thought the db's have to detached to be re-attached?
In many cases, attaching work even if you didn't detach first. But you are right in saying that this is *not*
something to count on.
> Also, attaching the db's is not going to help resolve
> the 'sa' password.
It depends on which database we are talking about.
Sa is a login, stored in master.
You can rebuild the master database.
You now have a known password for sa.
And just attach the user databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message news:c90f01c48a20$9de73b60$a401280a@.phx.gbl...
> I thought the db's have to detached to be re-attached?
> Also, attaching the db's is not going to help resolve
> the 'sa' password. But it will keep downtime to minimum,
> if he/she is able to attach the db's.
> DeeJay
> >--Original Message--
> >Well, you should be able to shut SQL Server down
> gracefully from the
> >Services control panel applet, so you might only have to
> re-attach your
> >existing MDF/LDF files after you reinstall.
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> >news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
> >> If you can not connect via sa and you have removed
> >> the 'builtin\administrators' account, you are going to
> be
> >> out-of-luck.
> >>
> >> It seems that you have to re-install and get your db's
> >> back from backups.
> >>
> >> hth
> >>
> >> DeeJay
> >> >--Original Message--
> >> >Hi,
> >> >
> >> >Is there is a method to reset the 'sa' password? I
> have a
> >> >server that moved between few different domains and I
> am
> >> >not sure which domain accounts have access to it. And
> We
> >> >do not know the 'sa' password.
> >> >
> >> >I thought there was a registry key that could be
> removed
> >> >to reset the password? Is that possible?
> >> >
> >> >Thanks,
> >> >.
> >> >
> >
> >
> >.
> >|||That's true. I assumed we are talking about user databases
because its generally not a good idea to detach and attach
the system databases to a different server, is it?
Also, I was not sure what rebuilding the master db do the
sa login. Once the master db is rebuilt, what is the
password for 'sa'? blank? or initial password used during
sw installation?
DeeJay
>--Original Message--
>> I thought the db's have to detached to be re-attached?
>In many cases, attaching work even if you didn't detach
first. But you are right in saying that this is *not*
>something to count on.
>
>> Also, attaching the db's is not going to help resolve
>> the 'sa' password.
>It depends on which database we are talking about.
>Sa is a login, stored in master.
>You can rebuild the master database.
>You now have a known password for sa.
>And just attach the user databases.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:c90f01c48a20$9de73b60$a401280a@.phx.gbl...
>> I thought the db's have to detached to be re-attached?
>> Also, attaching the db's is not going to help resolve
>> the 'sa' password. But it will keep downtime to minimum,
>> if he/she is able to attach the db's.
>> DeeJay
>> >--Original Message--
>> >Well, you should be able to shut SQL Server down
>> gracefully from the
>> >Services control panel applet, so you might only have
to
>> re-attach your
>> >existing MDF/LDF files after you reinstall.
>> >
>> >--
>> >http://www.aspfaq.com/
>> >(Reverse address to reply.)
>> >
>> >
>> >
>> >
>> >"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>> >news:c99901c48a0b$49ccfa20$a601280a@.phx.gbl...
>> >> If you can not connect via sa and you have removed
>> >> the 'builtin\administrators' account, you are going
to
>> be
>> >> out-of-luck.
>> >>
>> >> It seems that you have to re-install and get your
db's
>> >> back from backups.
>> >>
>> >> hth
>> >>
>> >> DeeJay
>> >> >--Original Message--
>> >> >Hi,
>> >> >
>> >> >Is there is a method to reset the 'sa' password? I
>> have a
>> >> >server that moved between few different domains and
I
>> am
>> >> >not sure which domain accounts have access to it.
And
>> We
>> >> >do not know the 'sa' password.
>> >> >
>> >> >I thought there was a registry key that could be
>> removed
>> >> >to reset the password? Is that possible?
>> >> >
>> >> >Thanks,
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||> I thought the db's have to detached to be re-attached?
I'm pretty sure that if SQL Server is allowed to be shut down successfully,
that the files will be detached successfully. No guarantee, of course, but
it has worked in all cases where I tried it.
> Also, attaching the db's is not going to help resolve
> the 'sa' password.
No, and that's not why I mentioned it. I was only commenting on the
"re-install and get your db's back from backups" comment.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Sounds Good!
>--Original Message--
>> I thought the db's have to detached to be re-attached?
>I'm pretty sure that if SQL Server is allowed to be shut
down successfully,
>that the files will be detached successfully. No
guarantee, of course, but
>it has worked in all cases where I tried it.
>> Also, attaching the db's is not going to help resolve
>> the 'sa' password.
>No, and that's not why I mentioned it. I was only
commenting on the
>"re-install and get your db's back from backups" comment.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||I lost my password for sa. So, I do the following, but it doesn't seem to
work.
1) I login to SQL Server thru the query analyzer using Windows
Authentication.
2) sp_password NULL, 'password', 'sa'
3) It indicates with "Password changed"
4) I log out from the query analyzer.
5) I try login to SQL Server thru query analyzer using SQL Server
authentication.
6) It indicates with a login error, and it says "Reason: Not associated
with a trusted SQL Server connection.
Does anyone shine some light?
Thanks.
J
"Aaron [SQL Server MVP]" wrote:
> If you log onto the server with a different system admin user, or a
> local/domain windows administrator, you can use master..sp_password.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> > Hi,
> >
> > Is there is a method to reset the 'sa' password? I have a
> > server that moved between few different domains and I am
> > not sure which domain accounts have access to it. And We
> > do not know the 'sa' password.
> >
> > I thought there was a registry key that could be removed
> > to reset the password? Is that possible?
> >
> > Thanks,
>
>|||Already answered in another thread...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejackofall" <thejackofall@.discussions.microsoft.com> wrote in message
news:05AFF2D7-8F2A-4D75-9A0F-83DFB40E17CF@.microsoft.com...
> I lost my password for sa. So, I do the following, but it doesn't seem to
> work.
> 1) I login to SQL Server thru the query analyzer using Windows
> Authentication.
> 2) sp_password NULL, 'password', 'sa'
> 3) It indicates with "Password changed"
> 4) I log out from the query analyzer.
> 5) I try login to SQL Server thru query analyzer using SQL Server
> authentication.
> 6) It indicates with a login error, and it says "Reason: Not associated
> with a trusted SQL Server connection.
> Does anyone shine some light?
> Thanks.
> J
> "Aaron [SQL Server MVP]" wrote:
> > If you log onto the server with a different system admin user, or a
> > local/domain windows administrator, you can use master..sp_password.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > <anonymous@.discussions.microsoft.com> wrote in message
> > news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> > > Hi,
> > >
> > > Is there is a method to reset the 'sa' password? I have a
> > > server that moved between few different domains and I am
> > > not sure which domain accounts have access to it. And We
> > > do not know the 'sa' password.
> > >
> > > I thought there was a registry key that could be removed
> > > to reset the password? Is that possible?
> > >
> > > Thanks,
> >
> >
> >
reset sa password
Hi,
Is there is a method to reset the 'sa' password? I have a
server that moved between few different domains and I am
not sure which domain accounts have access to it. And We
do not know the 'sa' password.
I thought there was a registry key that could be removed
to reset the password? Is that possible?
Thanks,
If you log onto the server with a different system admin user, or a
local/domain windows administrator, you can use master..sp_password.
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,
|||anonymous@.discussions.microsoft.com wrote:
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,
You need to connect using another admin account and then reset the
password on the "sa" account. If you do not have any other admin
accounts, I don't think you can reset the password. You may have to
reinstall SQL Server and restore/reattach your databases.
David G.
|||I lost my password for sa. So, I do the following, but it doesn't seem to
work.
1) I login to SQL Server thru the query analyzer using Windows
Authentication.
2) sp_password NULL, 'password', 'sa'
3) It indicates with "Password changed"
4) I log out from the query analyzer.
5) I try login to SQL Server thru query analyzer using SQL Server
authentication.
6) It indicates with a login error, and it says "Reason: Not associated
with a trusted SQL Server connection.
Does anyone shine some light?
Thanks.
J
"Aaron [SQL Server MVP]" wrote:
> If you log onto the server with a different system admin user, or a
> local/domain windows administrator, you can use master..sp_password.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
>
>
|||Already answered in another thread...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejackofall" <thejackofall@.discussions.microsoft.com> wrote in message
news:05AFF2D7-8F2A-4D75-9A0F-83DFB40E17CF@.microsoft.com...[vbcol=seagreen]
> I lost my password for sa. So, I do the following, but it doesn't seem to
> work.
> 1) I login to SQL Server thru the query analyzer using Windows
> Authentication.
> 2) sp_password NULL, 'password', 'sa'
> 3) It indicates with "Password changed"
> 4) I log out from the query analyzer.
> 5) I try login to SQL Server thru query analyzer using SQL Server
> authentication.
> 6) It indicates with a login error, and it says "Reason: Not associated
> with a trusted SQL Server connection.
> Does anyone shine some light?
> Thanks.
> J
> "Aaron [SQL Server MVP]" wrote:
Is there is a method to reset the 'sa' password? I have a
server that moved between few different domains and I am
not sure which domain accounts have access to it. And We
do not know the 'sa' password.
I thought there was a registry key that could be removed
to reset the password? Is that possible?
Thanks,
If you log onto the server with a different system admin user, or a
local/domain windows administrator, you can use master..sp_password.
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,
|||anonymous@.discussions.microsoft.com wrote:
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,
You need to connect using another admin account and then reset the
password on the "sa" account. If you do not have any other admin
accounts, I don't think you can reset the password. You may have to
reinstall SQL Server and restore/reattach your databases.
David G.
|||I lost my password for sa. So, I do the following, but it doesn't seem to
work.
1) I login to SQL Server thru the query analyzer using Windows
Authentication.
2) sp_password NULL, 'password', 'sa'
3) It indicates with "Password changed"
4) I log out from the query analyzer.
5) I try login to SQL Server thru query analyzer using SQL Server
authentication.
6) It indicates with a login error, and it says "Reason: Not associated
with a trusted SQL Server connection.
Does anyone shine some light?
Thanks.
J
"Aaron [SQL Server MVP]" wrote:
> If you log onto the server with a different system admin user, or a
> local/domain windows administrator, you can use master..sp_password.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
>
>
|||Already answered in another thread...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejackofall" <thejackofall@.discussions.microsoft.com> wrote in message
news:05AFF2D7-8F2A-4D75-9A0F-83DFB40E17CF@.microsoft.com...[vbcol=seagreen]
> I lost my password for sa. So, I do the following, but it doesn't seem to
> work.
> 1) I login to SQL Server thru the query analyzer using Windows
> Authentication.
> 2) sp_password NULL, 'password', 'sa'
> 3) It indicates with "Password changed"
> 4) I log out from the query analyzer.
> 5) I try login to SQL Server thru query analyzer using SQL Server
> authentication.
> 6) It indicates with a login error, and it says "Reason: Not associated
> with a trusted SQL Server connection.
> Does anyone shine some light?
> Thanks.
> J
> "Aaron [SQL Server MVP]" wrote:
Friday, March 23, 2012
reset sa password
Hi,
Is there is a method to reset the 'sa' password? I have a
server that moved between few different domains and I am
not sure which domain accounts have access to it. And We
do not know the 'sa' password.
I thought there was a registry key that could be removed
to reset the password? Is that possible?
Thanks,If you log onto the server with a different system admin user, or a
local/domain windows administrator, you can use master..sp_password.
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,|||anonymous@.discussions.microsoft.com wrote:
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,
You need to connect using another admin account and then reset the
password on the "sa" account. If you do not have any other admin
accounts, I don't think you can reset the password. You may have to
reinstall SQL Server and restore/reattach your databases.
David G.|||I lost my password for sa. So, I do the following, but it doesn't seem to
work.
1) I login to SQL Server thru the query analyzer using Windows
Authentication.
2) sp_password NULL, 'password', 'sa'
3) It indicates with "Password changed"
4) I log out from the query analyzer.
5) I try login to SQL Server thru query analyzer using SQL Server
authentication.
6) It indicates with a login error, and it says "Reason: Not associated
with a trusted SQL Server connection.
Does anyone shine some light?
Thanks.
J
"Aaron [SQL Server MVP]" wrote:
> If you log onto the server with a different system admin user, or a
> local/domain windows administrator, you can use master..sp_password.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
>
>|||Already answered in another thread...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejackofall" <thejackofall@.discussions.microsoft.com> wrote in message
news:05AFF2D7-8F2A-4D75-9A0F-83DFB40E17CF@.microsoft.com...[vbcol=seagreen]
> I lost my password for sa. So, I do the following, but it doesn't seem to
> work.
> 1) I login to SQL Server thru the query analyzer using Windows
> Authentication.
> 2) sp_password NULL, 'password', 'sa'
> 3) It indicates with "Password changed"
> 4) I log out from the query analyzer.
> 5) I try login to SQL Server thru query analyzer using SQL Server
> authentication.
> 6) It indicates with a login error, and it says "Reason: Not associated
> with a trusted SQL Server connection.
> Does anyone shine some light?
> Thanks.
> J
> "Aaron [SQL Server MVP]" wrote:
>
Is there is a method to reset the 'sa' password? I have a
server that moved between few different domains and I am
not sure which domain accounts have access to it. And We
do not know the 'sa' password.
I thought there was a registry key that could be removed
to reset the password? Is that possible?
Thanks,If you log onto the server with a different system admin user, or a
local/domain windows administrator, you can use master..sp_password.
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,|||anonymous@.discussions.microsoft.com wrote:
> Hi,
> Is there is a method to reset the 'sa' password? I have a
> server that moved between few different domains and I am
> not sure which domain accounts have access to it. And We
> do not know the 'sa' password.
> I thought there was a registry key that could be removed
> to reset the password? Is that possible?
> Thanks,
You need to connect using another admin account and then reset the
password on the "sa" account. If you do not have any other admin
accounts, I don't think you can reset the password. You may have to
reinstall SQL Server and restore/reattach your databases.
David G.|||I lost my password for sa. So, I do the following, but it doesn't seem to
work.
1) I login to SQL Server thru the query analyzer using Windows
Authentication.
2) sp_password NULL, 'password', 'sa'
3) It indicates with "Password changed"
4) I log out from the query analyzer.
5) I try login to SQL Server thru query analyzer using SQL Server
authentication.
6) It indicates with a login error, and it says "Reason: Not associated
with a trusted SQL Server connection.
Does anyone shine some light?
Thanks.
J
"Aaron [SQL Server MVP]" wrote:
> If you log onto the server with a different system admin user, or a
> local/domain windows administrator, you can use master..sp_password.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:53e001c48a07$3ad358b0$a301280a@.phx.gbl...
>
>|||Already answered in another thread...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejackofall" <thejackofall@.discussions.microsoft.com> wrote in message
news:05AFF2D7-8F2A-4D75-9A0F-83DFB40E17CF@.microsoft.com...[vbcol=seagreen]
> I lost my password for sa. So, I do the following, but it doesn't seem to
> work.
> 1) I login to SQL Server thru the query analyzer using Windows
> Authentication.
> 2) sp_password NULL, 'password', 'sa'
> 3) It indicates with "Password changed"
> 4) I log out from the query analyzer.
> 5) I try login to SQL Server thru query analyzer using SQL Server
> authentication.
> 6) It indicates with a login error, and it says "Reason: Not associated
> with a trusted SQL Server connection.
> Does anyone shine some light?
> Thanks.
> J
> "Aaron [SQL Server MVP]" wrote:
>
Wednesday, March 21, 2012
Reserved Words
Is there a method or procedure name that will let me know if a word is a sql
server reserved word?
i.e.
sp_isReservedWord 'Null' --returns true
sp_isReservedWord 'Count' --returns true
sp_isReservedWord 'OrderNumber' --returns falseJI,
If you trust the list in SQL Server 2005 Books Online, this will work.
It may not be what you had in mind, but I hope it helps.
create function usp_reserved(
@.w nvarchar(128)
) returns bit as begin
set @.w = '|'+UPPER(REPLACE(@.w,'|',''))+'|'
if len(@.w) = 0 return 0
return
cast(charindex(@.w,
'|ADD|EXCEPT|PERCENT|ALL|EXEC|PLAN|ALTER
'+
'|EXECUTE|PRECISION|AND|EXISTS|PRIMARY|A
NY'+
'|EXIT|PRINT|AS|FETCH|PROC|ASC|FILE|PROC
EDURE'+
'|AUTHORIZATION|FILLFACTOR|PUBLIC|BACKUP
|FOR'+
'|RAISERROR|BEGIN|FOREIGN|READ|BETWEEN|F
REETEXT'+
'|READTEXT|BREAK|FREETEXTTABLE|RECONFIGU
RE'+
'|BROWSE|FROM|REFERENCES|BULK|FULL|REPLI
CATION'+
'|BY|FUNCTION|RESTORE|CASCADE|GOTO|RESTR
ICT'+
'|CASE|GRANT|RETURN|CHECK|GROUP|REVOKE'+
'|CHECKPOINT|HAVING|RIGHT|CLOSE|HOLDLOCK
'+
'|ROLLBACK|CLUSTERED|IDENTITY|ROWCOUNT|C
OALESCE'+
'|IDENTITY_INSERT|ROWGUIDCOL|COLLATE|IDE
NTITYCOL'+
'|RULE|COLUMN|IF|SAVE|COMMIT|IN|SCHEMA|C
OMPUTE'+
'|INDEX|SELECT|CONSTRAINT|INNER|SESSION_
USER'+
'|CONTAINS|INSERT|SET|CONTAINSTABLE|INTE
RSECT'+
'|SETUSER|CONTINUE|INTO|SHUTDOWN|CONVERT
|IS'+
'|SOME|CREATE|JOIN|STATISTICS|CROSS|KEY'
+
'|SYSTEM_USER|CURRENT|KILL|TABLE|CURRENT
_DATE'+
'|LEFT|TEXTSIZE|CURRENT_TIME|LIKE|THEN'+
'|CURRENT_TIMESTAMP|LINENO|TO|CURRENT_US
ER'+
'|LOAD|TOP|CURSOR|NATIONAL||TRAN|DATABAS
E'+
'|NOCHECK|TRANSACTION|DBCC|NONCLUSTERED'
+
'|TRIGGER|DEALLOCATE|NOT|TRUNCATE|DECLAR
E'+
'|NULL|TSEQUAL|DEFAULT|NULLIF|UNION|DELE
TE'+
'|OF|UNIQUE|DENY|OFF|UPDATE|DESC|OFFSETS
'+
'|UPDATETEXT|DISK|ON|USE|DISTINCT|OPEN|U
SER'+
'|DISTRIBUTED|OPENDATASOURCE|VALUES|DOUB
LE'+
'|OPENQUERY|VARYING|DROP|OPENROWSET|VIEW
|DUMMY'+
'|OPENXML|WAITFOR|DUMP|OPTION|WHEN|ELSE|
OR'+
'|WHERE|END|ORDER|WHILE|ERRLVL|OUTER|WIT
H'+
'|ESCAPE|OVER|WRITETEXT|') as bit)
end
go
select dbo.usp_reserved('ESCAPE')
select dbo.usp_reserved('AD')
select dbo.usp_reserved('ADD')
select dbo.usp_reserved('ADD|EXCEPT')
go
-- drop function usp_reserved
-- Steve Kass
-- Drew University
JI wrote:
>Is there a method or procedure name that will let me know if a word is a sq
l
>server reserved word?
>i.e.
>sp_isReservedWord 'Null' --returns true
>sp_isReservedWord 'Count' --returns true
>sp_isReservedWord 'OrderNumber' --returns false
>
>|||Thanks Steve...That saved me some cut and pasting from the BOL.
It would be a nice feature for the SQL team to write, simply to have them
maintain the list.
Thanks again,
ji
"Steve Kass" <skass@.drew.edu> wrote in message
news:%236tHIeaaGHA.3612@.TK2MSFTNGP03.phx.gbl...
> JI,
> If you trust the list in SQL Server 2005 Books Online, this will work.
> It may not be what you had in mind, but I hope it helps.
> create function usp_reserved(
> @.w nvarchar(128)
> ) returns bit as begin
> set @.w = '|'+UPPER(REPLACE(@.w,'|',''))+'|'
> if len(@.w) = 0 return 0
> return
> cast(charindex(@.w,
> '|ADD|EXCEPT|PERCENT|ALL|EXEC|PLAN|ALTER
'+
> '|EXECUTE|PRECISION|AND|EXISTS|PRIMARY|A
NY'+
> '|EXIT|PRINT|AS|FETCH|PROC|ASC|FILE|PROC
EDURE'+
> '|AUTHORIZATION|FILLFACTOR|PUBLIC|BACKUP
|FOR'+
> '|RAISERROR|BEGIN|FOREIGN|READ|BETWEEN|F
REETEXT'+
> '|READTEXT|BREAK|FREETEXTTABLE|RECONFIGU
RE'+
> '|BROWSE|FROM|REFERENCES|BULK|FULL|REPLI
CATION'+
> '|BY|FUNCTION|RESTORE|CASCADE|GOTO|RESTR
ICT'+
> '|CASE|GRANT|RETURN|CHECK|GROUP|REVOKE'+
> '|CHECKPOINT|HAVING|RIGHT|CLOSE|HOLDLOCK
'+
> '|ROLLBACK|CLUSTERED|IDENTITY|ROWCOUNT|C
OALESCE'+
> '|IDENTITY_INSERT|ROWGUIDCOL|COLLATE|IDE
NTITYCOL'+
> '|RULE|COLUMN|IF|SAVE|COMMIT|IN|SCHEMA|C
OMPUTE'+
> '|INDEX|SELECT|CONSTRAINT|INNER|SESSION_
USER'+
> '|CONTAINS|INSERT|SET|CONTAINSTABLE|INTE
RSECT'+
> '|SETUSER|CONTINUE|INTO|SHUTDOWN|CONVERT
|IS'+
> '|SOME|CREATE|JOIN|STATISTICS|CROSS|KEY'
+
> '|SYSTEM_USER|CURRENT|KILL|TABLE|CURRENT
_DATE'+
> '|LEFT|TEXTSIZE|CURRENT_TIME|LIKE|THEN'+
> '|CURRENT_TIMESTAMP|LINENO|TO|CURRENT_US
ER'+
> '|LOAD|TOP|CURSOR|NATIONAL||TRAN|DATABAS
E'+
> '|NOCHECK|TRANSACTION|DBCC|NONCLUSTERED'
+
> '|TRIGGER|DEALLOCATE|NOT|TRUNCATE|DECLAR
E'+
> '|NULL|TSEQUAL|DEFAULT|NULLIF|UNION|DELE
TE'+
> '|OF|UNIQUE|DENY|OFF|UPDATE|DESC|OFFSETS
'+
> '|UPDATETEXT|DISK|ON|USE|DISTINCT|OPEN|U
SER'+
> '|DISTRIBUTED|OPENDATASOURCE|VALUES|DOUB
LE'+
> '|OPENQUERY|VARYING|DROP|OPENROWSET|VIEW
|DUMMY'+
> '|OPENXML|WAITFOR|DUMP|OPTION|WHEN|ELSE|
OR'+
> '|WHERE|END|ORDER|WHILE|ERRLVL|OUTER|WIT
H'+
> '|ESCAPE|OVER|WRITETEXT|') as bit)
> end
> go
> select dbo.usp_reserved('ESCAPE')
> select dbo.usp_reserved('AD')
> select dbo.usp_reserved('ADD')
> select dbo.usp_reserved('ADD|EXCEPT')
> go
> -- drop function usp_reserved
> -- Steve Kass
> -- Drew University
> JI wrote:
>
server reserved word?
i.e.
sp_isReservedWord 'Null' --returns true
sp_isReservedWord 'Count' --returns true
sp_isReservedWord 'OrderNumber' --returns falseJI,
If you trust the list in SQL Server 2005 Books Online, this will work.
It may not be what you had in mind, but I hope it helps.
create function usp_reserved(
@.w nvarchar(128)
) returns bit as begin
set @.w = '|'+UPPER(REPLACE(@.w,'|',''))+'|'
if len(@.w) = 0 return 0
return
cast(charindex(@.w,
'|ADD|EXCEPT|PERCENT|ALL|EXEC|PLAN|ALTER
'+
'|EXECUTE|PRECISION|AND|EXISTS|PRIMARY|A
NY'+
'|EXIT|PRINT|AS|FETCH|PROC|ASC|FILE|PROC
EDURE'+
'|AUTHORIZATION|FILLFACTOR|PUBLIC|BACKUP
|FOR'+
'|RAISERROR|BEGIN|FOREIGN|READ|BETWEEN|F
REETEXT'+
'|READTEXT|BREAK|FREETEXTTABLE|RECONFIGU
RE'+
'|BROWSE|FROM|REFERENCES|BULK|FULL|REPLI
CATION'+
'|BY|FUNCTION|RESTORE|CASCADE|GOTO|RESTR
ICT'+
'|CASE|GRANT|RETURN|CHECK|GROUP|REVOKE'+
'|CHECKPOINT|HAVING|RIGHT|CLOSE|HOLDLOCK
'+
'|ROLLBACK|CLUSTERED|IDENTITY|ROWCOUNT|C
OALESCE'+
'|IDENTITY_INSERT|ROWGUIDCOL|COLLATE|IDE
NTITYCOL'+
'|RULE|COLUMN|IF|SAVE|COMMIT|IN|SCHEMA|C
OMPUTE'+
'|INDEX|SELECT|CONSTRAINT|INNER|SESSION_
USER'+
'|CONTAINS|INSERT|SET|CONTAINSTABLE|INTE
RSECT'+
'|SETUSER|CONTINUE|INTO|SHUTDOWN|CONVERT
|IS'+
'|SOME|CREATE|JOIN|STATISTICS|CROSS|KEY'
+
'|SYSTEM_USER|CURRENT|KILL|TABLE|CURRENT
_DATE'+
'|LEFT|TEXTSIZE|CURRENT_TIME|LIKE|THEN'+
'|CURRENT_TIMESTAMP|LINENO|TO|CURRENT_US
ER'+
'|LOAD|TOP|CURSOR|NATIONAL||TRAN|DATABAS
E'+
'|NOCHECK|TRANSACTION|DBCC|NONCLUSTERED'
+
'|TRIGGER|DEALLOCATE|NOT|TRUNCATE|DECLAR
E'+
'|NULL|TSEQUAL|DEFAULT|NULLIF|UNION|DELE
TE'+
'|OF|UNIQUE|DENY|OFF|UPDATE|DESC|OFFSETS
'+
'|UPDATETEXT|DISK|ON|USE|DISTINCT|OPEN|U
SER'+
'|DISTRIBUTED|OPENDATASOURCE|VALUES|DOUB
LE'+
'|OPENQUERY|VARYING|DROP|OPENROWSET|VIEW
|DUMMY'+
'|OPENXML|WAITFOR|DUMP|OPTION|WHEN|ELSE|
OR'+
'|WHERE|END|ORDER|WHILE|ERRLVL|OUTER|WIT
H'+
'|ESCAPE|OVER|WRITETEXT|') as bit)
end
go
select dbo.usp_reserved('ESCAPE')
select dbo.usp_reserved('AD')
select dbo.usp_reserved('ADD')
select dbo.usp_reserved('ADD|EXCEPT')
go
-- drop function usp_reserved
-- Steve Kass
-- Drew University
JI wrote:
>Is there a method or procedure name that will let me know if a word is a sq
l
>server reserved word?
>i.e.
>sp_isReservedWord 'Null' --returns true
>sp_isReservedWord 'Count' --returns true
>sp_isReservedWord 'OrderNumber' --returns false
>
>|||Thanks Steve...That saved me some cut and pasting from the BOL.
It would be a nice feature for the SQL team to write, simply to have them
maintain the list.
Thanks again,
ji
"Steve Kass" <skass@.drew.edu> wrote in message
news:%236tHIeaaGHA.3612@.TK2MSFTNGP03.phx.gbl...
> JI,
> If you trust the list in SQL Server 2005 Books Online, this will work.
> It may not be what you had in mind, but I hope it helps.
> create function usp_reserved(
> @.w nvarchar(128)
> ) returns bit as begin
> set @.w = '|'+UPPER(REPLACE(@.w,'|',''))+'|'
> if len(@.w) = 0 return 0
> return
> cast(charindex(@.w,
> '|ADD|EXCEPT|PERCENT|ALL|EXEC|PLAN|ALTER
'+
> '|EXECUTE|PRECISION|AND|EXISTS|PRIMARY|A
NY'+
> '|EXIT|PRINT|AS|FETCH|PROC|ASC|FILE|PROC
EDURE'+
> '|AUTHORIZATION|FILLFACTOR|PUBLIC|BACKUP
|FOR'+
> '|RAISERROR|BEGIN|FOREIGN|READ|BETWEEN|F
REETEXT'+
> '|READTEXT|BREAK|FREETEXTTABLE|RECONFIGU
RE'+
> '|BROWSE|FROM|REFERENCES|BULK|FULL|REPLI
CATION'+
> '|BY|FUNCTION|RESTORE|CASCADE|GOTO|RESTR
ICT'+
> '|CASE|GRANT|RETURN|CHECK|GROUP|REVOKE'+
> '|CHECKPOINT|HAVING|RIGHT|CLOSE|HOLDLOCK
'+
> '|ROLLBACK|CLUSTERED|IDENTITY|ROWCOUNT|C
OALESCE'+
> '|IDENTITY_INSERT|ROWGUIDCOL|COLLATE|IDE
NTITYCOL'+
> '|RULE|COLUMN|IF|SAVE|COMMIT|IN|SCHEMA|C
OMPUTE'+
> '|INDEX|SELECT|CONSTRAINT|INNER|SESSION_
USER'+
> '|CONTAINS|INSERT|SET|CONTAINSTABLE|INTE
RSECT'+
> '|SETUSER|CONTINUE|INTO|SHUTDOWN|CONVERT
|IS'+
> '|SOME|CREATE|JOIN|STATISTICS|CROSS|KEY'
+
> '|SYSTEM_USER|CURRENT|KILL|TABLE|CURRENT
_DATE'+
> '|LEFT|TEXTSIZE|CURRENT_TIME|LIKE|THEN'+
> '|CURRENT_TIMESTAMP|LINENO|TO|CURRENT_US
ER'+
> '|LOAD|TOP|CURSOR|NATIONAL||TRAN|DATABAS
E'+
> '|NOCHECK|TRANSACTION|DBCC|NONCLUSTERED'
+
> '|TRIGGER|DEALLOCATE|NOT|TRUNCATE|DECLAR
E'+
> '|NULL|TSEQUAL|DEFAULT|NULLIF|UNION|DELE
TE'+
> '|OF|UNIQUE|DENY|OFF|UPDATE|DESC|OFFSETS
'+
> '|UPDATETEXT|DISK|ON|USE|DISTINCT|OPEN|U
SER'+
> '|DISTRIBUTED|OPENDATASOURCE|VALUES|DOUB
LE'+
> '|OPENQUERY|VARYING|DROP|OPENROWSET|VIEW
|DUMMY'+
> '|OPENXML|WAITFOR|DUMP|OPTION|WHEN|ELSE|
OR'+
> '|WHERE|END|ORDER|WHILE|ERRLVL|OUTER|WIT
H'+
> '|ESCAPE|OVER|WRITETEXT|') as bit)
> end
> go
> select dbo.usp_reserved('ESCAPE')
> select dbo.usp_reserved('AD')
> select dbo.usp_reserved('ADD')
> select dbo.usp_reserved('ADD|EXCEPT')
> go
> -- drop function usp_reserved
> -- Steve Kass
> -- Drew University
> JI wrote:
>
Tuesday, March 20, 2012
required to only move data/log files to new SAN, best method?
Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
--
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
--
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:
> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
--
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
--
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:
> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>
required to only move data/log files to new SAN, best method?
Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peter
KB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:
> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peter
KB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:
> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>
required to only move data/log files to new SAN, best method?
Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:
> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data file
s
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW.
So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do
I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSD
B
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making th
e
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over t
he
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:
> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data file
s
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW.
So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do
I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSD
B
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making th
e
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over t
he
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>
Tuesday, February 21, 2012
ReportViewer.LocalReport.Render - PDF filesize over 5 MB
I am testing the .Render method of a local report and can get to export to excel and pdf no problem. My xls file size is under 50 K for a small chart graphic report but every pdf generated tops 5 MB. Is this an issue that an be resolved or a bug in the ReportViewer?
Any solution to this problem? I've got a 10 page report that is less than 1MB in Excel and more than 40MB in PDF!|||This sucks!
I found this post saying that this is a known problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379152&SiteID=1&mode=1
I have a PDF that renders as 25MB. If you open it in acrobat and save a copy of it the copy is saved as 270KB. 95 times smaller!!!
Subscribe to:
Posts (Atom)