Change default location of DB's in 2005 Express

I've installed SQL Server 2005 Express, MSSMSE, and SQL BOL and they seem to work. How can I change the default location of the DB's I create in this environment (and/or move them after I create them)?

Thanks!

Answered myself:

Right click the top line ("servers") in the MSSMSE TOC window > properties > database...there are the options

change @@SERVERNAME 2005 - Urgent please help !!!

Hi There

I have changed the servername of a sql server 2005 instance. Using sp_dropserver and sp_addserver as per BOL.

However, after changing the servername i still see that :

EWX-JDGSQLSRV1\SQLServer2005SQLAgentUser$EWX-JDGSQLSRV1$MSSQLSERVER

EWX-JDGSQLSRV1\SQLServer2005MSSQLUser$EWX-JDGSQLSRV1$MSSQLSERVER

EWX-JDGSQLSRV1\SQLServer2005MSFTEUser$EWX-JDGSQLSRV1$MSSQLSERVER

built in logins still have the old server name.

My big concern is that these will no longer work?

Also i am changing not only servernames but doamins, i cannot find anythign on chaning domains and all that must be done.

Are the built in sql server logins still ok after i change the servername ? Even though they do not reflect the change?

Wjat else must i do before moving the server to a new domain (i will change the servcie accounts to use new domain accounts in the configuration manager?)

Thanx

Dietz:

Did you include the "local" tag when you called the sp_addserver stored procedure?

Dave

|||

Yes i did, i found the prblem though. Has nothing to do with that when i checked the sql log it could not execute sp_startup_agent, becuase agent XP's were not enabled, i could not enable agent XP's because lock pages in memory priviledges were not granted to the new sqlservice account.

So i granted the priviledges and enabled agent XP's and all was well.

CDC Retention time

According to BOL the default CDC retention time is 3 days and it was mentioned that it configurable.

Can some of point me on how to change the default value.

Thanks in advance.

You can use sp_cdc_add_job or sp_cdc_change_job to set the retention for the cleanup job.

Thanks

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.