maintenance plan wizard: single

Showing posts with label single. Show all posts

Friday, March 30, 2012

Manage server in single user mode

Hello-
I am trying to use SQL server in single user mode to move the system
databases to a different drive as described in KB article
http://support.microsoft.com/kb/224071/
I have sql server started up in single user mode with the other startup
options configured as required. However I can not seem to find a way that it
will let me log in in order to run the procedures. The SQL server agent is
stopped as described in order to not use up the available connection.
Whenever I try to connect with SQL Server management studio it says "Login
failed for user xxxx. Reason: Server is in single user mode. Only one
administrator can connect at this time (Error 18461)."
I am the only person who even knows about this server so I know no one else
is conecting to it. I thought it would work to try and do this procedure
through the command line but i cant figure out how to connect to the server
using the command line (im doing this all local on the server).
Can anyone give me any suggestions on how to go about this or what I could
be doing wrong?
Thanks!It's probably from Object Explorer - you probably have SSMS
configured to open Object Explorer at startup. Close object
explorer and try to connect. You can hit cancel when the
Login dialog comes up and and SSMS opens with no connection.
Then close object explorer. Then connect using New Query.
-Sue
On Mon, 23 Jul 2007 12:26:02 -0700, Ehren
<Ehren@.discussions.microsoft.com> wrote:
>Hello-
>I am trying to use SQL server in single user mode to move the system
>databases to a different drive as described in KB article
>http://support.microsoft.com/kb/224071/
>I have sql server started up in single user mode with the other startup
>options configured as required. However I can not seem to find a way that it
>will let me log in in order to run the procedures. The SQL server agent is
>stopped as described in order to not use up the available connection.
>Whenever I try to connect with SQL Server management studio it says "Login
>failed for user xxxx. Reason: Server is in single user mode. Only one
>administrator can connect at this time (Error 18461)."
>I am the only person who even knows about this server so I know no one else
>is conecting to it. I thought it would work to try and do this procedure
>through the command line but i cant figure out how to connect to the server
>using the command line (im doing this all local on the server).
>Can anyone give me any suggestions on how to go about this or what I could
>be doing wrong?
>Thanks!|||On Jul 24, 2:25 am, Sue Hoegemeier <Su...@.nomail.please> wrote:
> It's probably from Object Explorer - you probably have SSMS
> configured to open Object Explorer at startup. Close object
> explorer and try toconnect. Youcanhit cancel when the
> Login dialog comes up and and SSMS opens with no connection.
> Then close object explorer. Thenconnectusing New Query.
> -Sue
> On Mon, 23 Jul 2007 12:26:02 -0700, Ehren
>
> <Eh...@.discussions.microsoft.com> wrote:
> >Hello-
> >I am trying to use SQLserverinsingleusermodeto move the system
> >databases to a different drive as described in KB article
> >http://support.microsoft.com/kb/224071/
> >I have sqlserverstarted up insingleusermodewith the other startup
> >options configured as required. However Icannot seem to find a way that it
> >will let me log in in order to run the procedures. The SQLserveragent is
> >stopped as described in order to not use up the available connection.
> >Whenever I try toconnectwith SQLServermanagement studio it says "Login
> >failed foruserxxxx. Reason:Serveris insingleusermode. Onlyone
> >administratorcanconnectat thistime(Error 18461)."
> >I am theonlyperson who even knows about thisserverso I know nooneelse
> >is conecting to it. I thought it would work to try and do this procedure
> >through the command line but i cant figure out how toconnectto theserver
> >using the command line (im doing this all local on theserver).
> >Cananyone give me any suggestions on how to go about this or what I could
> >be doing wrong?
> >Thanks!- Hide quoted text -
> - Show quoted text -
The process is
NET START MSSQLSERVER /f /T3608 (as per MS article)
Make sure no other SQL tools are running (config manager etc)
Start SQL Server management studio
When it prompts you to connect to the server press cancel (if you
press connect it is THIS connection which prevents you from running
the alter database query)
At this point you are not connected to anything (it will say "No
Server Connection" in big letters)
Press the New Query button
Paste your amended alter database code into thew query pane and
execute
Continue as per article
Cheers
Barry

Friday, March 23, 2012

Making a Single File

i m having 2 Database files for the same DB and 2 Logfiles ,i want it to make a single file.... is it possible with DTS or any other thing....Originally posted by hemzg
i m having 2 Database files for the same DB and 2 Logfiles ,i want it to make a single file.... is it possible with DTS or any other thing....

Check out DBCCC SHRINKFILE with the EMPTYFILE option in BOL.
regards,
harsh.

Monday, March 19, 2012

Make SQL Server distinguish between uppercase and lowercase characters in a stored procedu

I would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?

Use the COLLATE command and change
WHERE A = B
to
WHERE A COLLATE SQL_Latin1_General_Cp850_CS_AS = B COLLATE SQL_Latin1_General_Cp850_CS_AS

This assumes that SQL_Latin1_General_Cp850_CI_AS is your normal collation. The change in collation is effectivle only for the scope of the one clause.

|||

I adopted a different approach.

I converted using the convert function to varbinary(50). My original column was varchar(50)

For example if 'column1' value was being compared with@.column1 parameter, then the following solution helped me in achieving my goal:

CONVERT(varbinary(50), column1) = CONVERT(varbinary(50), @.column1)

My only question is if I have a 50 character varchar then when I convert it to varbinary, should I convert to varbinary(50)?

The advantage of this is that I don't need to disturb the existing collation setting on SQL Server 2000 instance.

|||

I ran
DECLARE @.VAR VARCHAR(50)
SET @.VAR = '12345'
PRINT DATALENGTH(@.VAR)
PRINT DATALENGTH(CONVERT(VARBINARY(50), @.VAR))

and got
5
5

which shows that the lengths can be the same.

>The advantage of this is that I don't need to disturb the existing collation setting on SQL Server 2000 instance.
The COLLATE command used as I describe does not change the collation sequence of the database, merely the sequence the comparison is done in.

Friday, March 9, 2012

Maitenance plans and noskip

I am using SQL Server 2005. I have a Backup Database task which I use
to back up to a single backup device. I want to set a retention time
of 3 days and have old backups overwritten. This seems to be the WITH
INIT, NOSKIP problem. However using the wizard there doesn't seem to
be the possibility of doing this. I find it hard to believe though
that anyone would write a wizard which didn't offer this feature.
Is there any way to backup with NOSKIP or failing that alter the SQL
genereated by the wizard.
For info, here's the first few SQL statements from the wizard!
BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
= 10
GO
etcThere's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
backups. Either you overwrite everything, or you append. Period.
Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
INIT/NOINIT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>I am using SQL Server 2005. I have a Backup Database task which I use
> to back up to a single backup device. I want to set a retention time
> of 3 days and have old backups overwritten. This seems to be the WITH
> INIT, NOSKIP problem. However using the wizard there doesn't seem to
> be the possibility of doing this. I find it hard to believe though
> that anyone would write a wizard which didn't offer this feature.
> Is there any way to backup with NOSKIP or failing that alter the SQL
> genereated by the wizard.
> For info, here's the first few SQL statements from the wizard!
> BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> = 10
> GO
> etc
>|||I would happily overwrite everything but there doesn't seem to be that
option either in the Wizard. You only have the option that it either
expires or it doesn't as whatever you do it always puts in SKIP into
the SQL. The only way you seem to be able to backup without having old
backups is to backup to individual files rather than using a backup
device as I am.
Rob
On 2 Aug, 12:58, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
> backups. Either you overwrite everything, or you append. Period.
> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
> INIT/NOINIT.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>
> >I am using SQL Server 2005. I have a Backup Database task which I use
> > to back up to a single backup device. I want to set a retention time
> > of 3 days and have old backups overwritten. This seems to be the WITH
> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
> > be the possibility of doing this. I find it hard to believe though
> > that anyone would write a wizard which didn't offer this feature.
> > Is there any way to backup with NOSKIP or failing that alter the SQL
> > genereated by the wizard.
> > For info, here's the first few SQL statements from the wizard!
> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> > = 10
> > GO
> > etc- Hide quoted text -
> - Show quoted text -|||So what you want to do is to backup to the same backup device name and always overwrite?
In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
exists:" and I select "Overwrite".
Here are the backup commands executed, as catched by Profiler:
BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME =N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME =N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
you want, ir are you saying that there's some problem with the TSQL above?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>I would happily overwrite everything but there doesn't seem to be that
> option either in the Wizard. You only have the option that it either
> expires or it doesn't as whatever you do it always puts in SKIP into
> the SQL. The only way you seem to be able to backup without having old
> backups is to backup to individual files rather than using a backup
> device as I am.
> Rob
> On 2 Aug, 12:58, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
>> backups. Either you overwrite everything, or you append. Period.
>> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
>> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
>> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites
>> everything
>> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
>> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
>> INIT/NOINIT.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <use...@.tynemarch.co.uk> wrote in message
>> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>>
>> >I am using SQL Server 2005. I have a Backup Database task which I use
>> > to back up to a single backup device. I want to set a retention time
>> > of 3 days and have old backups overwritten. This seems to be the WITH
>> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
>> > be the possibility of doing this. I find it hard to believe though
>> > that anyone would write a wizard which didn't offer this feature.
>> > Is there any way to backup with NOSKIP or failing that alter the SQL
>> > genereated by the wizard.
>> > For info, here's the first few SQL statements from the wizard!
>> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
>> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =>> > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
>> > = 10
>> > GO
>> > etc- Hide quoted text -
>> - Show quoted text -
>|||I could not get the wizard to generate any SQL with SKIP in it.
However, the answer to my problems was to use a one-off backup file
rather than have a pre-allocated backup device. I could then move to
the file to somewhere else and start afresh with a new file the next
day. Thanks for the help anyway.
On 2 Aug, 14:19, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> So what you want to do is to backup to the same backup device name and always overwrite?
> In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
> exists:" and I select "Overwrite".
> Here are the backup commands executed, as catched by Profiler:
> BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME => N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME => N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
> you want, ir are you saying that there's some problem with the TSQL above?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>
> >I would happily overwrite everything but there doesn't seem to be that
> > option either in the Wizard. You only have the option that it either
> > expires or it doesn't as whatever you do it always puts in SKIP into
> > the SQL. The only way you seem to be able to backup without having old
> > backups is to backup to individual files rather than using a backup
> > device as I am.
> > Rob
> > On 2 Aug, 12:58, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
> >> backups. Either you overwrite everything, or you append. Period.
> >> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
> >> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
> >> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites
> >> everything
> >> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
> >> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
> >> INIT/NOINIT.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> <use...@.tynemarch.co.uk> wrote in message
> >>news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
> >> >I am using SQL Server 2005. I have a Backup Database task which I use
> >> > to back up to a single backup device. I want to set a retention time
> >> > of 3 days and have old backups overwritten. This seems to be the WITH
> >> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
> >> > be the possibility of doing this. I find it hard to believe though
> >> > that anyone would write a wizard which didn't offer this feature.
> >> > Is there any way to backup with NOSKIP or failing that alter the SQL
> >> > genereated by the wizard.
> >> > For info, here's the first few SQL statements from the wizard!
> >> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> >> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => >> > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> >> > = 10
> >> > GO
> >> > etc- Hide quoted text -
> >> - Show quoted text -- Hide quoted text -
> - Show quoted text -

Friday, March 30, 2012

Manage server in single user mode

Manage server in single user mode

Manage server in single user mode

Friday, March 23, 2012

Making a Single File

Monday, March 19, 2012

Make SQL Server distinguish between uppercase and lowercase characters in a stored procedu

Friday, March 9, 2012

Maitenance plans and noskip

Maitenance plans and noskip

Maitenance plans and noskip

maintenance plan wizard

Blog Archive

About Me