Sql Activity log backup: databases

Showing posts with label databases. Show all posts

Thursday, March 22, 2012

Adding users to log-shipped DB

Hallo!
After I configure log shipping between databases on primary server A and
secondary server B, is it possible to add new users or groups and give them
access to log-shipped DB on server B only? Since the DB on server B would be
in read-only mode I'm not sure if that would allow me to add users to that
DB.
I would like a group of users to have read access to log shipped DB on
server B so they can run reports,
but they should not have any access to that DB on server A.
Thanks
Tom
You can make NO changes on Server B or it will break the log shipping..After
having thought about this for a few minutes, I don;t think you can do what
you wish... You must add the users to server A, that will get shipped to
server B. If you delete the users or change permissions on Server A, that
will also go to Server B...
It is a great problem to think about, and it seems that you shouldn't be
stuck, but I think you may be... ( Maybe Ron Talmage is watching and will
have a suggestion... He is very good at log shipping.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23xEFcUQxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> Hallo!
> After I configure log shipping between databases on primary server A and
> secondary server B, is it possible to add new users or groups and give
them
> access to log-shipped DB on server B only? Since the DB on server B would
be
> in read-only mode I'm not sure if that would allow me to add users to that
> DB.
> I would like a group of users to have read access to log shipped DB on
> server B so they can run reports,
> but they should not have any access to that DB on server A.
> Thanks
> Tom
>

Adding users to log-shipped DB

Hallo!
After I configure log shipping between databases on primary server A and
secondary server B, is it possible to add new users or groups and give them
access to log-shipped DB on server B only? Since the DB on server B would be
in read-only mode I'm not sure if that would allow me to add users to that
DB.
I would like a group of users to have read access to log shipped DB on
server B so they can run reports,
but they should not have any access to that DB on server A.
Thanks
TomYou can make NO changes on Server B or it will break the log shipping..After
having thought about this for a few minutes, I don;t think you can do what
you wish... You must add the users to server A, that will get shipped to
server B. If you delete the users or change permissions on Server A, that
will also go to Server B...
It is a great problem to think about, and it seems that you shouldn't be
stuck, but I think you may be... ( Maybe Ron Talmage is watching and will
have a suggestion... He is very good at log shipping.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23xEFcUQxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> Hallo!
> After I configure log shipping between databases on primary server A and
> secondary server B, is it possible to add new users or groups and give
them
> access to log-shipped DB on server B only? Since the DB on server B would
be
> in read-only mode I'm not sure if that would allow me to add users to that
> DB.
> I would like a group of users to have read access to log shipped DB on
> server B so they can run reports,
> but they should not have any access to that DB on server A.
> Thanks
> Tom
>

Adding users to log-shipped DB

Hallo!
After I configure log shipping between databases on primary server A and
secondary server B, is it possible to add new users or groups and give them
access to log-shipped DB on server B only? Since the DB on server B would be
in read-only mode I'm not sure if that would allow me to add users to that
DB.
I would like a group of users to have read access to log shipped DB on
server B so they can run reports,
but they should not have any access to that DB on server A.
Thanks
TomYou can make NO changes on Server B or it will break the log shipping..After
having thought about this for a few minutes, I don;t think you can do what
you wish... You must add the users to server A, that will get shipped to
server B. If you delete the users or change permissions on Server A, that
will also go to Server B...
It is a great problem to think about, and it seems that you shouldn't be
stuck, but I think you may be... ( Maybe Ron Talmage is watching and will
have a suggestion... He is very good at log shipping.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23xEFcUQxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> Hallo!
> After I configure log shipping between databases on primary server A and
> secondary server B, is it possible to add new users or groups and give
them
> access to log-shipped DB on server B only? Since the DB on server B would
be
> in read-only mode I'm not sure if that would allow me to add users to that
> DB.
> I would like a group of users to have read access to log shipped DB on
> server B so they can run reports,
> but they should not have any access to that DB on server A.
> Thanks
> Tom
>

Monday, March 19, 2012

Adding SQL Reporting Services to SQL Server 2005 SP1

I'm using Reporting Services Configuration tool to add the Reporting and ReportingTemb databases. Once the have been created, the tool attempts to upgrade the databases and can not create a script. This are the errors I see from the from the Reporting Services Configuration Tool after creating the databases:

! Verifing Database Version

The Database version (C.0.8.40) does not match your Reporting Services installation. You must upgrade your Reporting Services database.

! Creating upgrade script for database version C.0.8.40

ReportServicesConfigUI.WMIProvider.WMIProviderException: The version does not match a supported version.

at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.GenerateDatabaseUpgradeScript(String databaseName, String serverVersion, String& script)

I've reinstalled the entire SQL Server 2005 installation. As part of company installation precodure, SQL 2005 w/o Reporting Services is installed first, followed by SP1. I then perform the SQL Reporting Services installation add-on.

Thanks,

Mark

Ditto. I'm having the same issue. Another wrinkle (or red herring) is that I changed the name of the server before installing SP1 and before installing Reporting Services.|||I get the same too. This is a fresh intall of SQL 2005. What gives!|||

Found the answer :)

Just install SQL 2005 SP1 again (after you installed Reports Services). Fixed it for me.

Good luck

|||

That may do it as well. I re-installed SQL Server because I believe the issue was that I had the retail version of SQL 2005 installed and was trying to install the Developer Edition of SQL 2005 Reporting Services on that. I got a vague warning about the versions not matching, but matching the version of SQL 2005 between the database engine and the reporting services fixed the issue. I still need to install SP1 again....

|||

Yes. Reapplying SP1 after the Reporting Services install fixed the problem. Once SP1 is applied, run the Reporting Services Configuration Tool and the older database format will be converted.

- Thanks for the help.

|||

Hello,

I am receiving the error, "creating upgrade script for database version c.0.8.40" as well. I've tried everything I know of and it still doesn't work. I've uninstalled SQL Reporting Services, reinstalled, then reapplied SP1 but it still doesn't work. I've tried changing the application app pool for reporting services, that didn't work. I've also traced the process with file mon, that didn't help (saw a post on that somewhere else).

Can anyone help me? Is there a utility to check the health of reporting services maybe?

Thanks,
Bob

|||

I can't remember how I solved the issue now (a while ago)

possibly the worst case, uninstall everything and re-install everything

With SP2 officially out, you might want to try upgrade everything (RS, SQL) to SP2 then

|||

Hi Bob,

Try installing SQL server SP2,this should be working.

i did the same and lol its as working fine

Thanks,

vvbkumar

Adding SQL Reporting Services to SQL Server 2005 SP1

! Verifing Database Version

The Database version (C.0.8.40) does not match your Reporting Services installation. You must upgrade your Reporting Services database.

! Creating upgrade script for database version C.0.8.40

ReportServicesConfigUI.WMIProvider.WMIProviderException: The version does not match a supported version.

at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.GenerateDatabaseUpgradeScript(String databaseName, String serverVersion, String& script)

Thanks,

Mark

Found the answer :)

Just install SQL 2005 SP1 again (after you installed Reports Services). Fixed it for me.

Good luck

|||

Yes. Reapplying SP1 after the Reporting Services install fixed the problem. Once SP1 is applied, run the Reporting Services Configuration Tool and the older database format will be converted.

- Thanks for the help.

|||

Hello,

Can anyone help me? Is there a utility to check the health of reporting services maybe?

Thanks,
Bob

|||

I can't remember how I solved the issue now (a while ago)

possibly the worst case, uninstall everything and re-install everything

With SP2 officially out, you might want to try upgrade everything (RS, SQL) to SP2 then

|||

Hi Bob,

Try installing SQL server SP2,this should be working.

i did the same and lol its as working fine

Thanks,

vvbkumar

Adding SQL Reporting Services to SQL Server 2005 SP1

! Verifing Database Version

The Database version (C.0.8.40) does not match your Reporting Services installation. You must upgrade your Reporting Services database.

! Creating upgrade script for database version C.0.8.40

ReportServicesConfigUI.WMIProvider.WMIProviderException: The version does not match a supported version.

at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.GenerateDatabaseUpgradeScript(String databaseName, String serverVersion, String& script)

Thanks,

Mark

Found the answer :)

Just install SQL 2005 SP1 again (after you installed Reports Services). Fixed it for me.

Good luck

|||

Yes. Reapplying SP1 after the Reporting Services install fixed the problem. Once SP1 is applied, run the Reporting Services Configuration Tool and the older database format will be converted.

- Thanks for the help.

|||

Hello,

Can anyone help me? Is there a utility to check the health of reporting services maybe?

Thanks,
Bob

|||

I can't remember how I solved the issue now (a while ago)

possibly the worst case, uninstall everything and re-install everything

With SP2 officially out, you might want to try upgrade everything (RS, SQL) to SP2 then

|||

Hi Bob,

Try installing SQL server SP2,this should be working.

i did the same and lol its as working fine

Thanks,

vvbkumar

Thursday, March 8, 2012

adding new volume and swapping drive letters....

Hello - I have a SQL 2000 server which has a D: drive that contains all of my databases (system and user). I am running out of space on this volume and need to migrate the contents of this volume to a larger one. My initial plan was to introduce a new volume to the server (say a K: drive). Backup all databases (of course), and then stop all SQL services. Copy all data from D: to K:. Once data is copied, swap drive letter names (D: to I: and then K: to D

. Then restart SQL services. SQL should not know any better since everything was on the D: drive when it went down, and everythiing is still on the D: drive when it came back up, correct?

The other option mentioned is to detatch the databases, copy the data and then reattach them in their new locations. I understand this method, but it seems more involved (and riskier) than just renaming the drives. Does anyone have an opinion regarding these two migration methods? Thanks for your help.

Chris
I would use the attach / detach method, would be the best solution for me in this case.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||As you indicated, the drive rename method may be less effort. As long as you are using the Disk Management applet -and not just mapping drives, it should work just fine.

Tuesday, March 6, 2012

Adding New Column & updating bulk data in merge replication

Hi,
We have about 50 databases (SQL Server 2000, SP3) which are merge
replicated. We merge replicate about 100 odd tables in each of these
database. We need to add couple of columns in one of our major transaction
table where most insert/updates are being done. This table presently on
average has 5 lacs records.
During testing, we noticed that it takes about 60-80 minutes to add a
column in this table. Considering the # of database we have where the change
need to implemented, we will not be able to plan the upgrade without
production downtime. For upgrade 50 database it will take about 50 hours.
What are the options available in Replication so this can be done quickly
w/o any production downtime.
Adding to this, in one of the column we have added to the transaction table
, we need to update a new value. On testing we found that for 5 lacs records
it takes anyway between 2-3 hours. This takes roughly another 75 hours for
us to do this update after adding the new column in the table. How can this
be speeded up ?
thanks,
Soura
What's a lacs?
Basically there is probably no good solution for this. I would look at doing
a sync. Then creating my publication and then backing it up and restoring it
to all the subscribers and doing a no sync subscription, or I would look at
regenerating a snapshot and distributing it after you have made the column
change.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:A19172BF-E8AC-4BB7-9FED-87EEC5353D89@.microsoft.com...
> Hi,
> We have about 50 databases (SQL Server 2000, SP3) which are merge
> replicated. We merge replicate about 100 odd tables in each of these
> database. We need to add couple of columns in one of our major transaction
> table where most insert/updates are being done. This table presently on
> average has 5 lacs records.
> During testing, we noticed that it takes about 60-80 minutes to add a
> column in this table. Considering the # of database we have where the
> change
> need to implemented, we will not be able to plan the upgrade without
> production downtime. For upgrade 50 database it will take about 50 hours.
> What are the options available in Replication so this can be done quickly
> w/o any production downtime.
> Adding to this, in one of the column we have added to the transaction
> table
> , we need to update a new value. On testing we found that for 5 lacs
> records
> it takes anyway between 2-3 hours. This takes roughly another 75 hours for
> us to do this update after adding the new column in the table. How can
> this
> be speeded up ?
> thanks,
> Soura
|||5 lacs is 500 K or 500 thousand i.e 500,000
lacs is primarily an indian unit of measurment. 1 lac is 0.1 million
"Hilary Cotter" wrote:

> What's a lacs?
> Basically there is probably no good solution for this. I would look at doing
> a sync. Then creating my publication and then backing it up and restoring it
> to all the subscribers and doing a no sync subscription, or I would look at
> regenerating a snapshot and distributing it after you have made the column
> change.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:A19172BF-E8AC-4BB7-9FED-87EEC5353D89@.microsoft.com...
>
>

Adding Logons

I have transfered databases from one server to another.
All my users are showing up in my databases but there is nothing in the
"Login Name" column. I tried adding in the user in EM in Logins under the
Security folder and that works also. But when I create the user and give it
access to the database, I get an error that says the user already exists.
Now the user is in the Logins and Users section but still no Login Name
showing and you can't logon to the database. But when I drop the user from
the database and add it to the Login User again, it works fine. Do I have
to do this for each user? This is a problem as we have about 15 databases
and about 40 users.
Is there a better way to deal with this?
Thanks,
TomOn Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> I have transfered databases from one server to another.
> All my users are showing up in my databases but there is nothing in the
> "Login Name" column. I tried adding in the user in EM in Logins under the
> Security folder and that works also. But when I create the user and give it
> access to the database, I get an error that says the user already exists.
> Now the user is in the Logins and Users section but still no Login Name
> showing and you can't logon to the database. But when I drop the user from
> the database and add it to the Login User again, it works fine. Do I have
> to do this for each user? This is a problem as we have about 15 databases
> and about 40 users.
> Is there a better way to deal with this?
> Thanks,
> Tom
You must have restored the database to new server. The reason you're
having this problem is the SID for login and SID for the user in the
database are different. You have Orphaned Users. Since you have the
login in the server and user in the database you can keep doing what
you're doing remove the user and add the user again. Or you can use
sp_change_users_login. Check the books online for detailed usage of
the system stored proc or you can read the article for how to fix your
problem. Look into Chadmat's last post that has the tsql for
sp_fixusers procedure.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Good day,
Bulent|||"Bulent" <bulentgucuk@.gmail.com> wrote in message
news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
>> I have transfered databases from one server to another.
>> All my users are showing up in my databases but there is nothing in the
>> "Login Name" column. I tried adding in the user in EM in Logins under
>> the
>> Security folder and that works also. But when I create the user and give
>> it
>> access to the database, I get an error that says the user already exists.
>> Now the user is in the Logins and Users section but still no Login Name
>> showing and you can't logon to the database. But when I drop the user
>> from
>> the database and add it to the Login User again, it works fine. Do I
>> have
>> to do this for each user? This is a problem as we have about 15
>> databases
>> and about 40 users.
>> Is there a better way to deal with this?
>> Thanks,
>> Tom
> You must have restored the database to new server. The reason you're
> having this problem is the SID for login and SID for the user in the
> database are different. You have Orphaned Users. Since you have the
> login in the server and user in the database you can keep doing what
> you're doing remove the user and add the user again. Or you can use
> sp_change_users_login. Check the books online for detailed usage of
> the system stored proc or you can read the article for how to fix your
> problem. Look into Chadmat's last post that has the tsql for
> sp_fixusers procedure.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Yes, I did use a new server.
I just installed Sql Server on the new machine (Jupiter). I backed up the
couple of databases I have on my old Server (Venus) and then restored them
on Jupiter.
Can I just backup Master from Venus and restore it to Jupiter. I know that
Master keeps the Logins in its tables. Would this work or would it cause
other problems?
Thanks,
Tom
> Good day,
> Bulent
>|||"tshad" <t@.home.com> wrote in message
news:%23WQxfu2gHHA.2368@.TK2MSFTNGP04.phx.gbl...
> "Bulent" <bulentgucuk@.gmail.com> wrote in message
> news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
>> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
>> I have transfered databases from one server to another.
>> All my users are showing up in my databases but there is nothing in the
>> "Login Name" column. I tried adding in the user in EM in Logins under
>> the
>> Security folder and that works also. But when I create the user and
>> give it
>> access to the database, I get an error that says the user already
>> exists.
>> Now the user is in the Logins and Users section but still no Login Name
>> showing and you can't logon to the database. But when I drop the user
>> from
>> the database and add it to the Login User again, it works fine. Do I
>> have
>> to do this for each user? This is a problem as we have about 15
>> databases
>> and about 40 users.
>> Is there a better way to deal with this?
>> Thanks,
>> Tom
>> You must have restored the database to new server. The reason you're
>> having this problem is the SID for login and SID for the user in the
>> database are different. You have Orphaned Users. Since you have the
>> login in the server and user in the database you can keep doing what
>> you're doing remove the user and add the user again. Or you can use
>> sp_change_users_login. Check the books online for detailed usage of
>> the system stored proc or you can read the article for how to fix your
>> problem. Look into Chadmat's last post that has the tsql for
>> sp_fixusers procedure.
>> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
This SP did work great, BTW.
But I am still curious about the Master and whether I can or even need to
move it to the new server. As well as what problems I could run into such
as incompatabilities (assuming that the new Sql Server was installed exactly
the same as the old one).
Thanks,
Tom
> Yes, I did use a new server.
> I just installed Sql Server on the new machine (Jupiter). I backed up the
> couple of databases I have on my old Server (Venus) and then restored them
> on Jupiter.
> Can I just backup Master from Venus and restore it to Jupiter. I know
> that Master keeps the Logins in its tables. Would this work or would it
> cause other problems?
> Thanks,
> Tom
>> Good day,
>> Bulent
>

Saturday, February 25, 2012

Adding Logons

Adding logins and permissions

Hi,
I am at a company with 18 employees and I have 11-12 databases in SQL server. I can't seem to give logins and permissions to groups. Is there a simpler way, or do I have to add every single employee to each database and give permissions?
You may create roles (it's a group analogue) within databases and then assign your employees to these groups. There are no serverwide groups except of builtin server roles.

Sunday, February 19, 2012

Adding Domain Accounts to Databases

Hi guys
I have a user that is in the process of being migrated from one domain to
another. Therefore I have been asked to add his new domain account to the SQL
Server 2000.
However when I try to Add the user Domain2\user1 it fails saying 'user1'
already exist. Which is true as Domain1\User1.
Question is: Is there a way to add user1 from Domain2 without removing the
Domain1 user?
Thanks.
Regards
Jonas
Jonas
Lookup sp_change_users_login in the BOL.
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:A913CF93-A288-45E0-8653-D085F2FB68B9@.microsoft.com...
> Hi guys
> I have a user that is in the process of being migrated from one domain to
> another. Therefore I have been asked to add his new domain account to the
> SQL
> Server 2000.
> However when I try to Add the user Domain2\user1 it fails saying 'user1'
> already exist. Which is true as Domain1\User1.
> Question is: Is there a way to add user1 from Domain2 without removing the
> Domain1 user?
> Thanks.
> Regards
> Jonas
|||Jonas Larsen wrote:
> Hi guys
> I have a user that is in the process of being migrated from one domain to
> another. Therefore I have been asked to add his new domain account to the SQL
> Server 2000.
> However when I try to Add the user Domain2\user1 it fails saying 'user1'
> already exist. Which is true as Domain1\User1.
> Question is: Is there a way to add user1 from Domain2 without removing the
> Domain1 user?
> Thanks.
> Regards
> Jonas
Hi Jonas
You could also create a group in the new domain and then give this group
the required access to the database. You can then put the new user
account into this group. That should give you what you want.
Regards
Steen

Adding Domain Accounts to Databases

Hi guys
I have a user that is in the process of being migrated from one domain to
another. Therefore I have been asked to add his new domain account to the SQL
Server 2000.
However when I try to Add the user Domain2\user1 it fails saying 'user1'
already exist. Which is true as Domain1\User1.
Question is: Is there a way to add user1 from Domain2 without removing the
Domain1 user?
Thanks.
Regards
JonasJonas
Lookup sp_change_users_login in the BOL.
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:A913CF93-A288-45E0-8653-D085F2FB68B9@.microsoft.com...
> Hi guys
> I have a user that is in the process of being migrated from one domain to
> another. Therefore I have been asked to add his new domain account to the
> SQL
> Server 2000.
> However when I try to Add the user Domain2\user1 it fails saying 'user1'
> already exist. Which is true as Domain1\User1.
> Question is: Is there a way to add user1 from Domain2 without removing the
> Domain1 user?
> Thanks.
> Regards
> Jonas|||Jonas Larsen wrote:
> Hi guys
> I have a user that is in the process of being migrated from one domain to
> another. Therefore I have been asked to add his new domain account to the SQL
> Server 2000.
> However when I try to Add the user Domain2\user1 it fails saying 'user1'
> already exist. Which is true as Domain1\User1.
> Question is: Is there a way to add user1 from Domain2 without removing the
> Domain1 user?
> Thanks.
> Regards
> Jonas
Hi Jonas
You could also create a group in the new domain and then give this group
the required access to the database. You can then put the new user
account into this group. That should give you what you want.
Regards
Steen

Adding Domain Accounts to Databases

Hi guys
I have a user that is in the process of being migrated from one domain to
another. Therefore I have been asked to add his new domain account to the SQ
L
Server 2000.
However when I try to Add the user Domain2\user1 it fails saying 'user1'
already exist. Which is true as Domain1\User1.
Question is: Is there a way to add user1 from Domain2 without removing the
Domain1 user?
Thanks.
Regards
JonasJonas
Lookup sp_change_users_login in the BOL.
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:A913CF93-A288-45E0-8653-D085F2FB68B9@.microsoft.com...
> Hi guys
> I have a user that is in the process of being migrated from one domain to
> another. Therefore I have been asked to add his new domain account to the
> SQL
> Server 2000.
> However when I try to Add the user Domain2\user1 it fails saying 'user1'
> already exist. Which is true as Domain1\User1.
> Question is: Is there a way to add user1 from Domain2 without removing the
> Domain1 user?
> Thanks.
> Regards
> Jonas|||Jonas Larsen wrote:
> Hi guys
> I have a user that is in the process of being migrated from one domain to
> another. Therefore I have been asked to add his new domain account to the
SQL
> Server 2000.
> However when I try to Add the user Domain2\user1 it fails saying 'user1'
> already exist. Which is true as Domain1\User1.
> Question is: Is there a way to add user1 from Domain2 without removing the
> Domain1 user?
> Thanks.
> Regards
> Jonas
Hi Jonas
You could also create a group in the new domain and then give this group
the required access to the database. You can then put the new user
account into this group. That should give you what you want.
Regards
Steen

Adding database to publication stops responding on first article

When adding a database to publications, the process gets stuck on the first
article of the database. This only happens on databases that were previously
replicated, others run through without the slightest problem. We also checked
the transactions issued with sp_who2 / trace, it looks like the transaction
goes into a loop of selecting and updating. We have checked all the
replication related system tables in an attempt to properly remove any traces
of previous publication, but to no avail. Can anyone help? Thanks in advance
Hi Paul
To start of with, we had to manually clear out all the system tables to
properly disable the server as a distributor (The SEM froze) ( We also tried
using sp's which also became non-responsive). At the moment, enabling and
disabling the server as a distributor can be done without any problems
through SEM. We use the SEM to publish the database(s), and the process hangs
on the third step which is adding the articles. It simply gets stuck on
number 1 of X articles, but at least the GUI remains responsive. We have left
the process to run for significant periods without any change - it remains
stuck on article one. Again, we only experience this on databases that were
previously replicated, previously unreplicated databases can be published and
remove without any problems, so I am of the opinion that the problem is not
so much the SQL installation as a problem with the databases.
Thanks
Emile
"Paul Ibison" wrote:

> Emile,
> are you talking about sp_addarticle - manually or
> clicking the articles checkbox in the add publication
> wizard? Or is it when you run the snapshot agent, or the
> distribution/merge agent?
> What procedure gets called in a loop?
> How did you previously remove replication - if you want
> to remove almost all traces, try sp_removedbreplication.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Emile
can you try sp_removedbreplication on the database in
question. Then use sp_dboption to disable and reenable
publishing and then see if the issue still remains.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Thursday, February 16, 2012

Adding database name at runtime

Hi,
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from dbo.sysfiles
But I am not able to substitute the database name which I am getting
from the cursor at runtime.
I want to place the database name in the following query instead of
'DBNAME'.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from >>DBNAME<<<.dbo.sysfiles
Can we replace the 'DBNAME' with the actual database name from the
cursor and retrieve the values?
Thanks,
Regards,
PramodHi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
?.dbo.sysfiles'
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||try this
declare @.dbname varchar(10)
set @.dbname='Northwind'
exec('select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from['+ @.dbname +'].[dbo].[sysfiles]')
Vt
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||Hi Uri,
Thanks for your feedback. It really worked.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--
--EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
--
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
Uri Dimant wrote:
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> ?.dbo.sysfiles'
>
> <ipramod@.gmail.com> wrote in message
> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > Hi,
> >
> > I have below SQL query which calculates the database size for all
> > databases.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from dbo.sysfiles
> >
> > But I am not able to substitute the database name which I am getting
> > from the cursor at runtime.
> > I want to place the database name in the following query instead of
> > 'DBNAME'.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from >>DBNAME<<<.dbo.sysfiles
> >
> > Can we replace the 'DBNAME' with the actual database name from the
> > cursor and retrieve the values?
> >
> > Thanks,
> > Regards,
> > Pramod
> >|||What version of sql server you using..'
vt
<ipramod@.gmail.com> wrote in message
news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> Hi Uri,
> Thanks for your feedback. It really worked.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
> OPEN AllDatabaseInfo
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
> SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
> Can you help me?
> Thanks,
> Regards,
> Pramod
> Uri Dimant wrote:
>> Hi
>> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
>> ?.dbo.sysfiles'
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> > Hi,
>> >
>> > I have below SQL query which calculates the database size for all
>> > databases.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from dbo.sysfiles
>> >
>> > But I am not able to substitute the database name which I am getting
>> > from the cursor at runtime.
>> > I want to place the database name in the following query instead of
>> > 'DBNAME'.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from >>DBNAME<<<.dbo.sysfiles
>> >
>> > Can we replace the 'DBNAME' with the actual database name from the
>> > cursor and retrieve the values?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>|||SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:
> What version of sql server you using..'
> vt
>
> <ipramod@.gmail.com> wrote in message
> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > Hi Uri,
> >
> > Thanks for your feedback. It really worked.
> > Now, I have another question.
> >
> > I have a variable @.dbsize to which I am assigning the value of database
> > size and I am using the variable value in the code
> >
> > Below is my SQL query which returns the database free space in percent
> > for all the databases.
> >
> > SET nocount on
> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > master..sysdatabases
> >
> > OPEN AllDatabaseInfo
> >
> > IF object_id('tempdb..#test2') IS NOT NULL
> > BEGIN
> > DROP TABLE #test2
> > END
> >
> > CREATE TABLE #test2 (
> > [Database Name] [varchar] (1000),
> > [Database Space Available] [varchar] (1000)
> > )
> >
> > IF object_id('tempdb..#test3') IS NOT NULL
> > BEGIN
> > DROP TABLE #test3
> > END
> >
> > CREATE TABLE #test3 (
> > [dbsize] [varchar] (1000),
> > [logsize] [varchar] (1000)
> > )
> >
> > DELETE FROM #test2
> > DECLARE @.DBName nvarchar(1000)
> > DECLARE @.sql nvarchar(1000)
> > DECLARE @.str sysname
> > SET @.sql = ''
> > SET @.DBName = ''
> > DECLARE @.pages bigint
> > ,@.dbsize bigint
> > ,@.logsize bigint
> > ,@.reservedpages bigint
> > ,@.unallocatedsize bigint
> > ,@.totalsize bigint
> >
> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > --
> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > 0 end))FROM ?.dbo.sysfiles'
> >
> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > <> 0 then size else 0 end))
> > FROM dbo.sysfiles
> >
> > SELECT @.reservedpages = sum(a.total_pages)
> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > = a.container_id
> > left join sys.internal_tables it on p.object_id = it.object_id
> >
> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > (15,2),@.logsize))/128.00
> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > --
> >
> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > 15,2)
> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > @.str
> > EXEC sp_executesql @.sql
> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > END
> >
> > CLOSE AllDatabaseInfo
> > DEALLOCATE AllDatabaseInfo
> >
> > SELECT * FROM #test2
> > SET nocount off
> >
> >
> >
> > Now this code returns the free space value in percent only for one
> > database because I am unable to substitute the database name when I
> > calculate the @.dbsize.
> >
> > Can you help me?
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > Uri Dimant wrote:
> >> Hi
> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> >> ?.dbo.sysfiles'
> >>
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> > Hi,
> >> >
> >> > I have below SQL query which calculates the database size for all
> >> > databases.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from dbo.sysfiles
> >> >
> >> > But I am not able to substitute the database name which I am getting
> >> > from the cursor at runtime.
> >> > I want to place the database name in the following query instead of
> >> > 'DBNAME'.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >
> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> > cursor and retrieve the values?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >|||Sorry buddy.. still using 2000
<ipramod@.gmail.com> wrote in message
news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> SQL Server 2005 RTM Version
> Thanks,
> Regards,
> Pramod
> vt wrote:
>> What version of sql server you using..'
>> vt
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
>> > Hi Uri,
>> >
>> > Thanks for your feedback. It really worked.
>> > Now, I have another question.
>> >
>> > I have a variable @.dbsize to which I am assigning the value of database
>> > size and I am using the variable value in the code
>> >
>> > Below is my SQL query which returns the database free space in percent
>> > for all the databases.
>> >
>> > SET nocount on
>> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
>> > master..sysdatabases
>> >
>> > OPEN AllDatabaseInfo
>> >
>> > IF object_id('tempdb..#test2') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test2
>> > END
>> >
>> > CREATE TABLE #test2 (
>> > [Database Name] [varchar] (1000),
>> > [Database Space Available] [varchar] (1000)
>> > )
>> >
>> > IF object_id('tempdb..#test3') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test3
>> > END
>> >
>> > CREATE TABLE #test3 (
>> > [dbsize] [varchar] (1000),
>> > [logsize] [varchar] (1000)
>> > )
>> >
>> > DELETE FROM #test2
>> > DECLARE @.DBName nvarchar(1000)
>> > DECLARE @.sql nvarchar(1000)
>> > DECLARE @.str sysname
>> > SET @.sql = ''
>> > SET @.DBName = ''
>> > DECLARE @.pages bigint
>> > ,@.dbsize bigint
>> > ,@.logsize bigint
>> > ,@.reservedpages bigint
>> > ,@.unallocatedsize bigint
>> > ,@.totalsize bigint
>> >
>> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
>> > WHILE @.@.FETCH_STATUS = 0
>> > BEGIN
>> > --
>> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =>> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
>> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
>> > 0 end))FROM ?.dbo.sysfiles'
>> >
>> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
>> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
>> > <> 0 then size else 0 end))
>> > FROM dbo.sysfiles
>> >
>> > SELECT @.reservedpages = sum(a.total_pages)
>> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
>> > = a.container_id
>> > left join sys.internal_tables it on p.object_id = it.object_id
>> >
>> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
>> > (15,2),@.logsize))/128.00
>> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
>> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
>> > --
>> >
>> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
>> > 15,2)
>> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
>> > @.str
>> > EXEC sp_executesql @.sql
>> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
>> > END
>> >
>> > CLOSE AllDatabaseInfo
>> > DEALLOCATE AllDatabaseInfo
>> >
>> > SELECT * FROM #test2
>> > SET nocount off
>> >
>> >
>> >
>> > Now this code returns the free space value in percent only for one
>> > database because I am unable to substitute the database name when I
>> > calculate the @.dbsize.
>> >
>> > Can you help me?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>> > Uri Dimant wrote:
>> >> Hi
>> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> >> end))FROM
>> >> ?.dbo.sysfiles'
>> >>
>> >>
>> >>
>> >> <ipramod@.gmail.com> wrote in message
>> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> >> > Hi,
>> >> >
>> >> > I have below SQL query which calculates the database size for all
>> >> > databases.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from dbo.sysfiles
>> >> >
>> >> > But I am not able to substitute the database name which I am getting
>> >> > from the cursor at runtime.
>> >> > I want to place the database name in the following query instead of
>> >> > 'DBNAME'.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from >>DBNAME<<<.dbo.sysfiles
>> >> >
>> >> > Can we replace the 'DBNAME' with the actual database name from the
>> >> > cursor and retrieve the values?
>> >> >
>> >> > Thanks,
>> >> > Regards,
>> >> > Pramod
>> >> >
>> >
>|||Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:
> Sorry buddy.. still using 2000
>
> <ipramod@.gmail.com> wrote in message
> news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > SQL Server 2005 RTM Version
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > vt wrote:
> >> What version of sql server you using..'
> >>
> >> vt
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> >> > Hi Uri,
> >> >
> >> > Thanks for your feedback. It really worked.
> >> > Now, I have another question.
> >> >
> >> > I have a variable @.dbsize to which I am assigning the value of database
> >> > size and I am using the variable value in the code
> >> >
> >> > Below is my SQL query which returns the database free space in percent
> >> > for all the databases.
> >> >
> >> > SET nocount on
> >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> >> > master..sysdatabases
> >> >
> >> > OPEN AllDatabaseInfo
> >> >
> >> > IF object_id('tempdb..#test2') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test2
> >> > END
> >> >
> >> > CREATE TABLE #test2 (
> >> > [Database Name] [varchar] (1000),
> >> > [Database Space Available] [varchar] (1000)
> >> > )
> >> >
> >> > IF object_id('tempdb..#test3') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test3
> >> > END
> >> >
> >> > CREATE TABLE #test3 (
> >> > [dbsize] [varchar] (1000),
> >> > [logsize] [varchar] (1000)
> >> > )
> >> >
> >> > DELETE FROM #test2
> >> > DECLARE @.DBName nvarchar(1000)
> >> > DECLARE @.sql nvarchar(1000)
> >> > DECLARE @.str sysname
> >> > SET @.sql = ''
> >> > SET @.DBName = ''
> >> > DECLARE @.pages bigint
> >> > ,@.dbsize bigint
> >> > ,@.logsize bigint
> >> > ,@.reservedpages bigint
> >> > ,@.unallocatedsize bigint
> >> > ,@.totalsize bigint
> >> >
> >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> >> > WHILE @.@.FETCH_STATUS = 0
> >> > BEGIN
> >> > --
> >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> >> > 0 end))FROM ?.dbo.sysfiles'
> >> >
> >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> >> > <> 0 then size else 0 end))
> >> > FROM dbo.sysfiles
> >> >
> >> > SELECT @.reservedpages = sum(a.total_pages)
> >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> >> > = a.container_id
> >> > left join sys.internal_tables it on p.object_id = it.object_id
> >> >
> >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> >> > (15,2),@.logsize))/128.00
> >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> >> > --
> >> >
> >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> >> > 15,2)
> >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> >> > @.str
> >> > EXEC sp_executesql @.sql
> >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> >> > END
> >> >
> >> > CLOSE AllDatabaseInfo
> >> > DEALLOCATE AllDatabaseInfo
> >> >
> >> > SELECT * FROM #test2
> >> > SET nocount off
> >> >
> >> >
> >> >
> >> > Now this code returns the free space value in percent only for one
> >> > database because I am unable to substitute the database name when I
> >> > calculate the @.dbsize.
> >> >
> >> > Can you help me?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >> > Uri Dimant wrote:
> >> >> Hi
> >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> >> end))FROM
> >> >> ?.dbo.sysfiles'
> >> >>
> >> >>
> >> >>
> >> >> <ipramod@.gmail.com> wrote in message
> >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> >> > Hi,
> >> >> >
> >> >> > I have below SQL query which calculates the database size for all
> >> >> > databases.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from dbo.sysfiles
> >> >> >
> >> >> > But I am not able to substitute the database name which I am getting
> >> >> > from the cursor at runtime.
> >> >> > I want to place the database name in the following query instead of
> >> >> > 'DBNAME'.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >> >
> >> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> >> > cursor and retrieve the values?
> >> >> >
> >> >> > Thanks,
> >> >> > Regards,
> >> >> > Pramod
> >> >> >
> >> >
> >|||Hi Vt,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys :)
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:
> > Sorry buddy.. still using 2000
> >
> >
> >
> > <ipramod@.gmail.com> wrote in message
> > news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > > SQL Server 2005 RTM Version
> > >
> > > Thanks,
> > > Regards,
> > > Pramod
> > >
> > > vt wrote:
> > >> What version of sql server you using..'
> > >>
> > >> vt
> > >>
> > >>
> > >> <ipramod@.gmail.com> wrote in message
> > >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > >> > Hi Uri,
> > >> >
> > >> > Thanks for your feedback. It really worked.
> > >> > Now, I have another question.
> > >> >
> > >> > I have a variable @.dbsize to which I am assigning the value of database
> > >> > size and I am using the variable value in the code
> > >> >
> > >> > Below is my SQL query which returns the database free space in percent
> > >> > for all the databases.
> > >> >
> > >> > SET nocount on
> > >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > >> > master..sysdatabases
> > >> >
> > >> > OPEN AllDatabaseInfo
> > >> >
> > >> > IF object_id('tempdb..#test2') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test2
> > >> > END
> > >> >
> > >> > CREATE TABLE #test2 (
> > >> > [Database Name] [varchar] (1000),
> > >> > [Database Space Available] [varchar] (1000)
> > >> > )
> > >> >
> > >> > IF object_id('tempdb..#test3') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test3
> > >> > END
> > >> >
> > >> > CREATE TABLE #test3 (
> > >> > [dbsize] [varchar] (1000),
> > >> > [logsize] [varchar] (1000)
> > >> > )
> > >> >
> > >> > DELETE FROM #test2
> > >> > DECLARE @.DBName nvarchar(1000)
> > >> > DECLARE @.sql nvarchar(1000)
> > >> > DECLARE @.str sysname
> > >> > SET @.sql = ''
> > >> > SET @.DBName = ''
> > >> > DECLARE @.pages bigint
> > >> > ,@.dbsize bigint
> > >> > ,@.logsize bigint
> > >> > ,@.reservedpages bigint
> > >> > ,@.unallocatedsize bigint
> > >> > ,@.totalsize bigint
> > >> >
> > >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > >> > WHILE @.@.FETCH_STATUS = 0
> > >> > BEGIN
> > >> > --
> > >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> > 0 end))FROM ?.dbo.sysfiles'
> > >> >
> > >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > >> > <> 0 then size else 0 end))
> > >> > FROM dbo.sysfiles
> > >> >
> > >> > SELECT @.reservedpages = sum(a.total_pages)
> > >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > >> > = a.container_id
> > >> > left join sys.internal_tables it on p.object_id = it.object_id
> > >> >
> > >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > >> > (15,2),@.logsize))/128.00
> > >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > >> > --
> > >> >
> > >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > >> > 15,2)
> > >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > >> > @.str
> > >> > EXEC sp_executesql @.sql
> > >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > >> > END
> > >> >
> > >> > CLOSE AllDatabaseInfo
> > >> > DEALLOCATE AllDatabaseInfo
> > >> >
> > >> > SELECT * FROM #test2
> > >> > SET nocount off
> > >> >
> > >> >
> > >> >
> > >> > Now this code returns the free space value in percent only for one
> > >> > database because I am unable to substitute the database name when I
> > >> > calculate the @.dbsize.
> > >> >
> > >> > Can you help me?
> > >> >
> > >> > Thanks,
> > >> > Regards,
> > >> > Pramod
> > >> >
> > >> > Uri Dimant wrote:
> > >> >> Hi
> > >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> > >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> > >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > >> >> end))FROM
> > >> >> ?.dbo.sysfiles'
> > >> >>
> > >> >>
> > >> >>
> > >> >> <ipramod@.gmail.com> wrote in message
> > >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > >> >> > Hi,
> > >> >> >
> > >> >> > I have below SQL query which calculates the database size for all
> > >> >> > databases.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from dbo.sysfiles
> > >> >> >
> > >> >> > But I am not able to substitute the database name which I am getting
> > >> >> > from the cursor at runtime.
> > >> >> > I want to place the database name in the following query instead of
> > >> >> > 'DBNAME'.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from >>DBNAME<<<.dbo.sysfiles
> > >> >> >
> > >> >> > Can we replace the 'DBNAME' with the actual database name from the
> > >> >> > cursor and retrieve the values?
> > >> >> >
> > >> >> > Thanks,
> > >> >> > Regards,
> > >> >> > Pramod
> > >> >> >
> > >> >
> > >

Monday, February 13, 2012

Adding column to sp_helpuser

I am working on creating a configuration database. This will connect to many databases on many different servers. What I would like to do is run sp_MSForEachDB then store the name of the current server and database the package is connected to. Then add both of these into a column before running sp_helpuser. I don't know how confusing this is, but I think I may have the code worked out pretty well. If you have any questions, please post them and I will try to answer as best as I can. What I would like the ending result to look like is...

I am at a loss, and I can't use the sys.<table> tables because it has to be backward compatible. Thanks for any help in advance.
sp_helplogins is the answer.
-Kyle

Adding column to sp_helpuser

I am at a loss, and I can't use the sys.<table> tables because it has to be backward compatible. Thanks for any help in advance.
sp_helplogins is the answer.
-Kyle

Sunday, February 12, 2012

Adding another server instance to another hard drive?

Problem: For safety reasons, I want an exact copy of all SQL databases on
another hard drive. I'm trying to guard against a hard drive crash.
Currently, I detach, file copy to another drive, then reattach each
individual database. Since SQL Server is running on different hard drive
from the data, I know that I wouldn't have to reinstall it.
1) Is there a way to do what I am asking?
2) Is this the correct the newsgroup to post these questions?
3) Is there a book that covers this?
Tried various keywords, but couldn't find anything in Books Online. I'm
running MS SQL Server Developer's ed. under Windows 2000 Pro.
Niels
That is what backups are for. It's much easier to simply do a backup of
each database (including Master & MSDB) and store those in a different
location. Preferably on a different machine or medium altogether, You don't
have to take the databases off line for a backup like you do with detach.
You might want to take a look at this:
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
Andrew J. Kelly
SQL Server MVP
"Niels" <nkistrup@.comcast.net> wrote in message
news:6v-dnY1yOZGJGCXdRVn2sQ@.giganews.com...
> Problem: For safety reasons, I want an exact copy of all SQL databases on
> another hard drive. I'm trying to guard against a hard drive crash.
> Currently, I detach, file copy to another drive, then reattach each
> individual database. Since SQL Server is running on different hard drive
> from the data, I know that I wouldn't have to reinstall it.
> 1) Is there a way to do what I am asking?
> 2) Is this the correct the newsgroup to post these questions?
> 3) Is there a book that covers this?
> Tried various keywords, but couldn't find anything in Books Online. I'm
> running MS SQL Server Developer's ed. under Windows 2000 Pro.
> Niels
>
|||Thanks Andrew,
But I will still like to create another server. Where I can find the steps
to add additional server instances?
Niels
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ev$827XREHA.568@.TK2MSFTNGP12.phx.gbl...
> That is what backups are for. It's much easier to simply do a backup of
> each database (including Master & MSDB) and store those in a different
> location. Preferably on a different machine or medium altogether, You
don't
> have to take the databases off line for a backup like you do with detach.
> You might want to take a look at this:
>
http://www.microsoft.com/technet/pro...n/sqlops0.mspx[vbcol=seagreen]
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Niels" <nkistrup@.comcast.net> wrote in message
> news:6v-dnY1yOZGJGCXdRVn2sQ@.giganews.com...
on[vbcol=seagreen]
drive
>
|||There a couple of options that you might want to consider.
Firstly if your running NT 4, 2000 or 2003, then ask your network guys to
raid the disks, I wouldn't do into it in too much detail but there are
plenty of examples. What a raid does in automatically (non SQL Server) copy
stuff from one hard drive to another.
If you wish to create another instance on your current machine then that
will not really help as if the machine goes then both instances go.
If you want to create another server then you will need another machine to
do it then you just install SQL Server the normal way, that will create a
default instance on that machine.
If you want to keep your database running in case of server crashes then
create a new server and backup your databases on the other server.(using the
backup database command).
If you want a server to kick in automatically when your existing server dies
then look on Microsoft for Replication, and High Availabity Server.
"Niels" <nkistrup@.comcast.net> wrote in message
news:8fKdnfr3UYxtyCTdRVn2sw@.giganews.com...
> Thanks Andrew,
> But I will still like to create another server. Where I can find the
steps[vbcol=seagreen]
> to add additional server instances?
> Niels
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ev$827XREHA.568@.TK2MSFTNGP12.phx.gbl...
> don't
detach.
>
http://www.microsoft.com/technet/pro...n/sqlops0.mspx[vbcol=seagreen]
[vbcol=seagreen]
> on
> drive
I'm
>
|||Peter,
So much for my mastery of English. I don't think I'm explaining the
situation correctly, sorry.
I'm running Win 2000 Pro, with the developer's edition of MS SQL Server, but
I have 4 hard drives ... and it's all on my home computer (I'm the 'network
guy'). Maybe another server instance is not the correct terminology?
Currently my data is on D:, but I would like to use one of the other hard
drives to actively store data.
If the computer crashes, I'm screwed, but I can always move the hard drive
to another machine. Heck the drive where the data is 6 1/2 yrs old, and has
migrated across machines in the past. (The hard drive.)
Niels
"Peter Nolan" <peter.nolan90@.ntlworld.com> wrote in message
news:x4luc.65$PA4.57@.newsfe1-gui.server.ntli.net...
> There a couple of options that you might want to consider.
> Firstly if your running NT 4, 2000 or 2003, then ask your network guys to
> raid the disks, I wouldn't do into it in too much detail but there are
> plenty of examples. What a raid does in automatically (non SQL Server)
copy
> stuff from one hard drive to another.
> If you wish to create another instance on your current machine then that
> will not really help as if the machine goes then both instances go.
> If you want to create another server then you will need another machine to
> do it then you just install SQL Server the normal way, that will create a
> default instance on that machine.
> If you want to keep your database running in case of server crashes then
> create a new server and backup your databases on the other server.(using
the
> backup database command).
> If you want a server to kick in automatically when your existing server
dies[vbcol=seagreen]
> then look on Microsoft for Replication, and High Availabity Server.
>
> "Niels" <nkistrup@.comcast.net> wrote in message
> news:8fKdnfr3UYxtyCTdRVn2sw@.giganews.com...
> steps
of
> detach.
>
http://www.microsoft.com/technet/pro...n/sqlops0.mspx[vbcol=seagreen]
databases
> I'm
>
|||A disaster recovery plan will help you minimize data loss. You need to
plan for various recovery scenarios according to the amount of data loss
that is acceptable to you.
The worst case is that you lose your computer completely due to a disaster
such as for or theft. Your only recourse in this situation is to reinstall
SQL Server on a working machine and restore from backups you've kept in a
safe location.
If your computer crashes and/or you lose your SQL Server drive, you might
get lucky with a successful attach of the database files if your database
files are intact. However, you should not rely on this and plan on
restoring your database from backup because you might not be able to attach
databases which were not detached properly.
The bottom line is that you need to backup your databases periodically and
archive the backup files to a safe location.
Hope this helps.
Dan Guzman
SQL Server MVP
"Niels" <nkistrup@.comcast.net> wrote in message
news:NeudnYnNW-kXTiTdRVn2tQ@.giganews.com...
> Peter,
> So much for my mastery of English. I don't think I'm explaining the
> situation correctly, sorry.
> I'm running Win 2000 Pro, with the developer's edition of MS SQL Server,
but
> I have 4 hard drives ... and it's all on my home computer (I'm the
'network
> guy'). Maybe another server instance is not the correct terminology?
> Currently my data is on D:, but I would like to use one of the other hard
> drives to actively store data.
> If the computer crashes, I'm screwed, but I can always move the hard drive
> to another machine. Heck the drive where the data is 6 1/2 yrs old, and
has
> migrated across machines in the past. (The hard drive.)
> Niels
>
|||Dan,
Lets talk 'preemptive correction'. My data drive is 6 1/2 yrs old & have
the replacement drive ready for swapping. How do you copy the server
instance onto a new drive. From what I can see, it is not as easy as
copying the SQL data folder to another drive, then onto the new one. (Or is
it?)
I am still curious about creating other server instances on other hard
drives. That way, I could spread the data across hard drives. Yes, I need
to transfer my data to a new drive, which will have the same drive. But
just because I cannot figure how out how to create new server instances,
makes me curious about the procedure. I work as a T-SQL developer, but my
DBA skills are minimal, which is why I'm being such a persistent pest about
this.
Hard drive crashes remain my #1 disaster threat. Failure of the computer is
also possible, but like you said, regular backups minimize problems from
that.
Niels
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OPBjaKmREHA.2848@.TK2MSFTNGP10.phx.gbl...
> A disaster recovery plan will help you minimize data loss. You need to
> plan for various recovery scenarios according to the amount of data loss
> that is acceptable to you.
> The worst case is that you lose your computer completely due to a disaster
> such as for or theft. Your only recourse in this situation is to
reinstall
> SQL Server on a working machine and restore from backups you've kept in a
> safe location.
> If your computer crashes and/or you lose your SQL Server drive, you might
> get lucky with a successful attach of the database files if your database
> files are intact. However, you should not rely on this and plan on
> restoring your database from backup because you might not be able to
attach[vbcol=seagreen]
> databases which were not detached properly.
> The bottom line is that you need to backup your databases periodically and
> archive the backup files to a safe location.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Niels" <nkistrup@.comcast.net> wrote in message
> news:NeudnYnNW-kXTiTdRVn2tQ@.giganews.com...
> but
> 'network
hard[vbcol=seagreen]
drive
> has
>
|||I am not sure if you really mean another instance or just moving the data
files. If you want to install a second instance of sql server (binaries)
then you need to rerun the setup and point it to the other drive for the
install. If you just want to move the data files you can find what you need
here:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.databasejournal.com/featu...le.php/2228611
Transferring Logins
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
This still does not mean you should not have valid backups. See BACKUP and
RESTORE in BooksOnLine for more info.

Andrew J. Kelly
SQL Server MVP
"Niels" <nkistrup@.comcast.net> wrote in message
news:GY6dneIma9N3zSfdRVn2tA@.giganews.com...
> Dan,
> Lets talk 'preemptive correction'. My data drive is 6 1/2 yrs old & have
> the replacement drive ready for swapping. How do you copy the server
> instance onto a new drive. From what I can see, it is not as easy as
> copying the SQL data folder to another drive, then onto the new one. (Or
is
> it?)
> I am still curious about creating other server instances on other hard
> drives. That way, I could spread the data across hard drives. Yes, I
need
> to transfer my data to a new drive, which will have the same drive. But
> just because I cannot figure how out how to create new server instances,
> makes me curious about the procedure. I work as a T-SQL developer, but my
> DBA skills are minimal, which is why I'm being such a persistent pest
about
> this.
> Hard drive crashes remain my #1 disaster threat. Failure of the computer
is[vbcol=seagreen]
> also possible, but like you said, regular backups minimize problems from
> that.
> Niels
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OPBjaKmREHA.2848@.TK2MSFTNGP10.phx.gbl...
disaster[vbcol=seagreen]
> reinstall
a[vbcol=seagreen]
might[vbcol=seagreen]
database[vbcol=seagreen]
> attach
and[vbcol=seagreen]
Server,[vbcol=seagreen]
> hard
> drive
and
>

Thursday, March 22, 2012

Adding users to log-shipped DB

Adding users to log-shipped DB

Adding users to log-shipped DB

Monday, March 19, 2012

Adding SQL Reporting Services to SQL Server 2005 SP1

Adding SQL Reporting Services to SQL Server 2005 SP1

Adding SQL Reporting Services to SQL Server 2005 SP1

Thursday, March 8, 2012

adding new volume and swapping drive letters....

Tuesday, March 6, 2012

Adding New Column & updating bulk data in merge replication

Adding Logons

Adding Logons

Saturday, February 25, 2012

Adding Logons

Adding logins and permissions

Sunday, February 19, 2012

Adding Domain Accounts to Databases

Adding Domain Accounts to Databases

Adding Domain Accounts to Databases

Adding database to publication stops responding on first article

Thursday, February 16, 2012

Adding database name at runtime

Monday, February 13, 2012

Adding column to sp_helpuser

Adding column to sp_helpuser

Sunday, February 12, 2012

Adding another server instance to another hard drive?

Sql Activity log backup

Blog Archive

About Me