maintenance plan wizard: management

Showing posts with label management. Show all posts

Friday, March 30, 2012

Manage with SQL Server Management Studio Express

I have read that SQL Server Compact Edition can be managed within SQL Server Management Studio Express.

Can anyone show me how to do it?

Or I can manage the Compact Edition in other way instead of in VS 2005.

Thanks a lot,

SQL Server Management Studio Express SP2 will allow you to manage SQL Compact Edition (despite the statement on the download page). It can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=6053C6F8-82C8-479C-B25B-9ACA13141C9E&displaylang=en

sql

Wednesday, March 28, 2012

Manage instances in another machine

I'm trying to manage another SQL Server 2005 instance in another machine. I'm doing this by connecting to another computer thru Computer Management. When I go the SQL Server 2005 Services, the right pane is showing There are no items to show in this view. How can I view the services?

Another problem that I have is that I have to turn off the Windows Firewall in the other machine. What exceptions are needed? I have tried by adding the specific TCP used by the instance.As far as I know the Computer management console can only adminsiter the local instances fpr Sql Server 2005.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens is right, you have to enable SQL Server remote access from the console of the server. Here's a couple relevant pages explaining how to enable remote access to SQL Server:

http://support.microsoft.com/default.aspx?scid=kb%3bEN-US%3b914277

http://www.aspcode.net/articles/l_en-US/t_default/Databases/SQL-Server/SQL-Server-2005-Expressremote-connection_article_123.aspx

Once you have remote access enabled, you can manage your server using SQL Server Management Studio from other machines.

Hope this helps,
Steve

|||

Hi Steven,

I'm following this MS article: http://msdn2.microsoft.com/en-US/library/ms190622.aspx

I can connect to the SQL Server Configuration Manager of the remote computer thru Computer Management. I can enable and disable protocols under both SQL Server 2005 Network Configuration and SQL Native Client Configuration. However, SQL Server 2005 Services will only show "There are no items to show in this view". It does not make sense to me since I can control the services thru Services but not thru SQL Server 2005 Services under SQL Server Configuration Manager.

I have disabled Windows Firewall and SQL Browser service is running.

Peter

|||If you login is a part of local administrator group on that server then using Computer Management console can do the job as it relies on the user privileges, as explained above for the SQL Configuration manager you can only manage local instances.sql

Making the Log file small

I'm using ss2005 and Management Studio...
I have this huge log file and I want it to be small. I read that if I do a
transaction backup that it will make it small. That did give it 90% free
space. So then I did a shrink (all three types) but that did not shrink it
at all. I did a database shrink and that did nothing.
Then I read that if I detach it, delete the log file (I just moved it), and
reattach it that a new small log file will be built. But, the reattach
would not work saying that the log file is missing. So I had to move it
back and now I still have this huge log file.
How can I make it small?
Thanks,
Tbackups shrink the logical space, not physical...
try backup log with the no_log clause, then shrink the file
Tina wrote:
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do a
> transaction backup that it will make it small. That did give it 90% free
> space. So then I did a shrink (all three types) but that did not shrink it
> at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), and
> reattach it that a new small log file will be built. But, the reattach
> would not work saying that the log file is missing. So I had to move it
> back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T|||After you backup your log file, you can shrink the file with DBCC
SHRINKFILE. For example:
DBCC SHRINKFILE('MyDatabase_Log', 100)
See the SQL Server Books Online for more info on DBCC SHRINKFILE.
The most common cause of out-of-control log files is failure to implement a
backup/recovery plan. If you choose to run the database in the
FULL/BULK_LOGGED recovery model, you will need to do regular transaction log
backups to keep the log size reasonable and provide the means to minimize
data loss after a failure. You can also run the database in the SIMPLE
recovery model so that you don't need to bother with log backups. However,
your only recovery recourse in the SIMPLE model is to restore from the last
full backup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
> a transaction backup that it will make it small. That did give it 90%
> free space. So then I did a shrink (all three types) but that did not
> shrink it at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it),
> and reattach it that a new small log file will be built. But, the
> reattach would not work saying that the log file is missing. So I had to
> move it back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T
>|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do a transaction backup
> that it will make it small. That did give it 90% free space. So then I did a shrink (all three
> types) but that did not shrink it at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), and reattach it that a new
> small log file will be built. But, the reattach would not work saying that the log file is
> missing. So I had to move it back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T
>

Monday, March 26, 2012

Making Inegration Services visible/available in SSMS 2005

How can I make it visible in the New Project window?

I have successfully used the import function so I think this means
that Inegration Services is there somewhere.

I would appreciate some advice,

Best wishes, John MorganJohn Morgan wrote:

Quote:

Originally Posted by

I have installed SQL Server Management Studio 2005 and have been
finding the equivalent of the Data Transfer Services in SQLServer
2000. I think I have worked out that it is in SQL Server Integration
Services but when I open File-New-Project I do not see the SQL Server
Integration Services there, though Help assumes that it is available
for selection when starting a new project..
>
How can I make it visible in the New Project window?
>
I have successfully used the import function so I think this means
that Inegration Services is there somewhere.
>
I would appreciate some advice,
>
Best wishes, John Morgan

What version of 2005 are you using?|||John Morgan (jfm@.XXwoodlander.co.uk) writes:

Quote:

Originally Posted by

I believe the place to work with Integrations Services is the
Business Intelligence Development Studio.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks both of you. Using your info and investigating the matter
further to make sure I don't hask silly questions has got me the
answer.

I checked the configuration manager and found that integrated services
was listed as running, I looked again and, yes, I did see SQL
Buisiness iIntelligence Studio was part of the programme group.

Opened it up and the VStudio installed templates for Integration
Services Project were visible.

Thanks
John Morgan

On Sun, 28 Jan 2007 08:24:28 -0600, Jonathan Roberts
<gremln007@.diynics.comwrote:

Quote:

Originally Posted by

>John Morgan wrote:

Quote:

Originally Posted by

>I have installed SQL Server Management Studio 2005 and have been
>finding the equivalent of the Data Transfer Services in SQLServer
>2000. I think I have worked out that it is in SQL Server Integration
>Services but when I open File-New-Project I do not see the SQL Server
>Integration Services there, though Help assumes that it is available
>for selection when starting a new project..
>>
> How can I make it visible in the New Project window?
>>
>I have successfully used the import function so I think this means
>that Inegration Services is there somewhere.
>>
>I would appreciate some advice,
>>
>Best wishes, John Morgan

>
>What version of 2005 are you using?

sql

Wednesday, March 21, 2012

Making "Server Management Studio" read only ?

Hi - I would like to be able to setup "Server Management Studio" so
that no changes can be made to the database/schemas (we write scripts
to make changes we want).
What's the best way to do this ?
Just to explain why - I want developers to be able to browse table
definitions etc but I want to ensure that no changes ever take place by
accident when doing so.
Thanks
Richard.Create a login which has only read only permissions. Take a look at
db_datareader fixed database role. The user should no be a member of
sysadmin server role
<shearichard@.gmail.com> wrote in message
news:1168142809.101663.284960@.i15g2000cwa.googlegroups.com...
> Hi - I would like to be able to setup "Server Management Studio" so
> that no changes can be made to the database/schemas (we write scripts
> to make changes we want).
> What's the best way to do this ?
> Just to explain why - I want developers to be able to browse table
> definitions etc but I want to ensure that no changes ever take place by
> accident when doing so.
> Thanks
> Richard.
>|||Uri Dimant wrote:
> Create a login which has only read only permissions. Take a look at
> db_datareader fixed database role. The user should no be a member of
> sysadmin server role
>
OK thanks I will try that. Reading the doco for db_datareader mentions
being able to read data but doesn't mention whether a db_datareader
will be able to read table definitions etc - maybe that's a given ?
Anyway thanks and I will try what you suggest.
regards
Richard.
> <shearichard@.gmail.com> wrote in message
> news:1168142809.101663.284960@.i15g2000cwa.googlegroups.com...
> > Hi - I would like to be able to setup "Server Management Studio" so
> > that no changes can be made to the database/schemas (we write scripts
> > to make changes we want).
> >
> > What's the best way to do this ?
> >
> > Just to explain why - I want developers to be able to browse table
> > definitions etc but I want to ensure that no changes ever take place by
> > accident when doing so.
> >
> > Thanks
> >
> > Richard.
> >|||<shearichard@.gmail.com> wrote in message
news:1168296784.941138.131700@.42g2000cwt.googlegroups.com...
> Uri Dimant wrote:
>> Create a login which has only read only permissions. Take a look at
>> db_datareader fixed database role. The user should no be a member of
>> sysadmin server role
> OK thanks I will try that. Reading the doco for db_datareader mentions
> being able to read data but doesn't mention whether a db_datareader
> will be able to read table definitions etc - maybe that's a given ?
> Anyway thanks and I will try what you suggest.
I believe they can.
> regards
>
> Richard.
>
>> <shearichard@.gmail.com> wrote in message
>> news:1168142809.101663.284960@.i15g2000cwa.googlegroups.com...
>> > Hi - I would like to be able to setup "Server Management Studio" so
>> > that no changes can be made to the database/schemas (we write scripts
>> > to make changes we want).
>> >
>> > What's the best way to do this ?
>> >
>> > Just to explain why - I want developers to be able to browse table
>> > definitions etc but I want to ensure that no changes ever take place by
>> > accident when doing so.
>> >
>> > Thanks
>> >
>> > Richard.
>> >
>

Making "Server Management Studio" read only ?

Monday, March 12, 2012

make a copy of database

Hi:

I have installed sql server 2005 express and SQL Server Management Studio Express

How can I generate a database from another copying the structure and data?

For example I have a database named Customers, I need to make a copy of Customers named Customers2. Customers2 also will be attached to the same Database Engine Server where Customers is attached.

How I Can do it?

Thanks!

P.S.

I tryied to make a copy of mdf an ldf files from Windows Explorer and renamed these files but I could not attach to the same Database Engine Server because I got an error.

The easiest way to do it is with the Backup and Restore Wizard because during restore you can change the name of the new one to customer2, I do it all the time, email the .bak or restore the .bak with a new name for a different department. If you don't have the SQL Server Express Advanced download it from the link below to use the Backup and Restore Wizard. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

Wednesday, March 7, 2012

Maintenance Wizard error...

Hi,

I'm just trying to use the Maintenance Wizard for the first time, but the SQL Server Management Studio shows me an error message about 'Agent XPs' is not running on my server; and I should activate it through 'sp_configure'?

I've tried to understand 'sp_configure' by searching it on MS TechNet, etc. But of course, because I'm not a SQL Server expert user; I don't even know how to use 'sp_configure' at all....

Anyone know how to work this thing around? Appreciate all the help...

PS: I almost forgot to mention, that I'm using MS Windows Small Business Server R2 2003 with SQL Server 2005 Workgroup Edition.

sp_configure is used to view/change global settings for the current server. To enable them, connect to Management Studio, start a new query, and view the current configuration. For more options with sp_configure, check out:

http://msdn2.microsoft.com/en-us/library/ms188787.aspx

To enable Agent XP's, you can try something like this:

use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
go
RECONFIGURE
GO

Thanks,
Sam Lester (MSFT)

|||

Sam,

Thanks for your reply there...

But unfortunately, I am a very novice at SQL Server, is there a step-by-step documentation on how to start using sp_configure?

Thank you again.

|||

The link I supplied explains the entire syntax for sp_configure, one of many system stored procedures used in managing your server. I'd suggest taking a look at the tutorials included in books online (the documentation portion of SQL Server 2005). Here is a good one on getting familiar with Management Studio, including writing T-SQL statements (such as sp_configure):

http://msdn2.microsoft.com/en-us/library/ms167593.aspx

Thanks,
Sam

|||

Hello -

I'm not an expert on SQL Server Management Studio, but I just ran into the same issue and here's how I got around it:

I loaded up SQL Server Management Studio|||

Chris,

Is it really true, you did have the similar problem with Maintenance Wizard like I do? Then perhaps it's like I'm most affraid of, Microsoft actually de-activate it by default settings in SQL Server 2005 Workgroup Edition... The question is why?

I did try to look up in the Object Explorer in SQL Server Management Studio first before post a thread in here, because I didn't find anything that said about Agent XPs. But maybe it's just that missed it, I'll take a look it again when I got back to my office then (too bad right now I'm on vacation, gives me a jeepers to leave the server's database un-protected like that ).

Thanks for the feedback Chris! Hopefully others who has the same problem with Maintenance Wizard will post too, so Microsoft could give a good explanation why there's such issue in the first place.

Maintenance Plans is SSE and Studio Management Express

Hi all,

I am using the Express versions of SQL Server and the Management console and can't seem to find anyway to set up basic maintenance plans.

Is this feature not in the Express management studio? I hope I am just missing it and it is not totally missing. I am trying to get my skills back up to speed so I can tackle some DBA jobs again and am working from home at the moment.

Can someone fill in the blanks for me to tell me if this infact is not available in the express management studio or where it is if it infact is available.

Many Thanks.

Steven

Maint Plans are not part of Express and hence not exposed in SSMS-E|||

Where can I find some scripts that allow scheduling of a backup operation?

I intend to schedule it through the Windows scheduler, as SSE has no scheduling agent.

|||Hi edmund1,

Two example calls are below. They each do the same thing so you only need one of them:

osql -U sa -S .\SQLEXPRESS -Q"BACKUP DATABASE NateTest TO DISK = 'C:\NateTest.bak'" -o c:\osql_log.txt -P

osql -U sa -S .\SQLEXPRESS -Q"BACKUP DATABASE NateTest TO DISK = 'C:\NateTest.bak'" -P > c:\osql_log.txt

You can then schedule the batch script to run every so often. Basically the above called are connecting to a local SQL Server Express instance (.\SQLEXPRESS) using the "sa" account and will execute the BACKUP DATABASE command (which you can alter as appropriate). A log file (osql_log.txt) is produced and this will contain the outcome of the call plus any errors that happened etc. I have used the -P switch without a value to indicate that a NULL password is to be used for the account - if you use a SQL Server login that has a password you need to put it in after the -P switch, e.g. -P ******.

Other options for osql that you might use are:

* Windows authentication. In this case, remove the -U and -P switches and just put in a -E switch

* Passing a file that contains a list of commands to execute. In this case, remove the -Q switch and add in the -i switch with the name of the file to execute, e.g.

-i c:\query.qry

Hope that helps a bit but sorry if it doesn't
|||Good example but I strongly recomend using sqlcmd not osql as osql is going away and sqlcmd is richer.|||

Thanks both.

Yes, this helps alot. I will try to script via sqlcmd.

I am also trying to script a signle batch of all databases that have a common naming (for example, they all start with the letters "DB_"). I'll post my solution for others to see.

Monday, February 20, 2012

Maintenance plan wizard missing

Under Management, all I have is SQL Server Logs and Activity monitor, no option to create a maintenance plan or run the wizard ?Under management you have Maintenance plans,Sql server logs,activity monitor,dataabse mail etc........your question sounds bizzare.....just check if you are logged in with sysadmin privilege.......|||

which edition of sql server is this. I thing Express edition u have . in that case MP not supported in this edtion

Madhu

|||I am running SQL 2005, not Express.|||SQL 2005

Maintenance Plan Wizard is Crashing

Hi,

I am having trouble with the Maintenance Plan Wizard in SQL Server 2005 Management Studio.

I have run the Maintenance Plan Wizard many times since I installed SQL Server 2005. However it looks like there is a problem with assembly 'Microsoft.SqlServer.Smo. I am getting this error:

- Creating maintenance plan "DNNSRC Backup" (Error)
Messages
* Create maintenance plan failed.
ADDITIONAL INFORMATION:
Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Microsoft.SqlServer.MaintenancePlanTasks)

Note I was going to upgrade with service pack 2 but I am reluctant to do that until I fix this problem. I am not sure SP2 would have any effect.

Any help or suggestions would be very much appreciated.

Thanks.
G. M.

make sure that your system is upgraded to SP2 and also you have installed SSIS.

Refer these links for more info

http://support.microsoft.com/default.aspx/kb/933508

http://blogs.msdn.com/sqlrem/

Madhu

|||

Check the following thread...sp2 resolve the same issue...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1404000&SiteID=1

|||Thanks, Your suggestions helped. I installed SP2 and that corrected the problem.

Friday, March 30, 2012

Manage with SQL Server Management Studio Express

Wednesday, March 28, 2012

Manage instances in another machine

Making the Log file small

Monday, March 26, 2012

Making Inegration Services visible/available in SSMS 2005

Wednesday, March 21, 2012

Making "Server Management Studio" read only ?

Making "Server Management Studio" read only ?

Making "Server Management Studio" read only ?

Monday, March 12, 2012

make a copy of database

Wednesday, March 7, 2012

Maintenance Wizard error...

Maintenance Plans is SSE and Studio Management Express

Monday, February 20, 2012

Maintenance plan wizard missing

Maintenance Plan Wizard is Crashing

maintenance plan wizard

Blog Archive

About Me