maintenance plan wizard: drive

Showing posts with label drive. 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

Monday, March 26, 2012

Making Backup pay attention ?

Hi, i have a vb.net here i have 2 timers. one look every night at 2 o'clock pm and make a backup of my sql server express database to drive c:

In the Program runs at this moment some other action and another timer that checks different things and write this to the sql database .

are the some things that i have to pay attention when i make the backup ?

Or is it equal when i make a backup and at the same time i write data ?

SQL Server is perfectly capable of taking a backup while other operations are happening. Just remember that this could have impact on performance if your hardware is not properly sized.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Inaddition to what Wesley had said, always schedule the backup in off production hours so that the load will be less and it would'nt cause a performance bottleneck|||

though, sql server is capable of taking backup online... what i feel is its always better to take the backup after all the major activities are completed. You schedule the backup after the Program does the database activities. And also check the backup plan u need. If fullbackup is taking lot of time , take differential backup daily and weekly full backup. And also do not keep the backup and original database in the same location (c:\ which u have mentioned).

Madhu

|||

Okay thank you for your reply.

but somethimes i got the following error

************** Ausnahmetext **************
System.ArgumentOutOfRangeException: no negative number necessarily (Nicht negative Zahl erforderlich.)

Parametername: count
bei System.Buffer.BlockCopy(Array src, Int32 srcOffset, Array dst, Int32 dstOffset, Int32 count)
bei System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
bei System.Data.SqlClient.TdsParserStateObject.ReadString(Int32 length)
bei System.Data.SqlClient.TdsParser.ProcessError(Byte token, TdsParserStateObject stateObj)
bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
bei System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
bei System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
bei System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

This happens sometimes when i execute the following command

BACKUP DATABASE MYDBA TO DISK="c:\test.bak' WITH INIT,NOSKIP

and with this i execute it

Try

Dim cmdSQLServer As New SqlCommand

With cmdSQLServer

.CommandTimeout = 0

.CommandText = sSQLCommand

.CommandType = CommandType.Text

.Connection = g_Connection

intRowsAffected = .ExecuteNonQuery

End With

cmdSQLServer.Dispose()

Catch sqldbe As SqlException

End Try

|||

Did you see that you are using double quotes before the path ? The path should be enclosed in single quotes. You could also try the SMO namespace which enabled you to administer your SQL Server in a object-oriented way.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

Monday, March 19, 2012

Make SQL Agent use drive mapping?

Good day,
I'm migrating our current SQL 2k box (running on win 2000) to a new Windows 2003 server. I'm having trouble getting the SQL agent to use the servers drive mapping when running jobs. The odbc driver that I'm using does not support UNC paths. I know, dri
ve mapping are not cool becuase we have to keep the server logged in but it's the only way and it does work on our old box. The weird thing is that I can't get this working on the new server. On the old box the SQL Agent is set to run as the same accoun
t as the logged in user and it can access the drive mappings fine, it's been running this way for quite some time. However, on my new box this does not work...is this a windows 2003 server thing? I'm runing SQL 2k SP3 on both boxes. Also, the new box i
n not in a domain so the account I'm trying to use is a local admin acount.
Any ideas?
Drive mappings are not guaranteed to work when being accessed from a
service. There is no way to force them to work.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I figured it out. I simply added a step in each scheduled job to create and then delete the drive mapping.
For example, I just use the "net use f: \\server\volume" to create the drive ampping an then excute the rest of my job steps and then use another step with this command "net use f: /delete" to delete it. Becuase the SQL Agent service is excuting the comm
and the drive mapping is in it's session and it works every time. It's nice becuase the drive mapping isn't even seen by the desktop and works when the server in not logged in. I remove the drive mapping at the end of each job becuase the drive mapping
remains in tack as long as the SQL agent service stays running and therefore if another job runs and tries to re-create the drive mapping the job will fail.

Make SQL Agent use drive mapping?

Good day,
I'm migrating our current SQL 2k box (running on win 2000) to a new Windows
2003 server. I'm having trouble getting the SQL agent to use the servers dr
ive mapping when running jobs. The odbc driver that I'm using does not supp
ort UNC paths. I know, dri
ve mapping are not cool becuase we have to keep the server logged in but it'
s the only way and it does work on our old box. The weird thing is that I c
an't get this working on the new server. On the old box the SQL Agent is se
t to run as the same accoun
t as the logged in user and it can access the drive mappings fine, it's been
running this way for quite some time. However, on my new box this does not
work...is this a windows 2003 server thing? I'm runing SQL 2k SP3 on both
boxes. Also, the new box i
n not in a domain so the account I'm trying to use is a local admin acount.
Any ideas?Drive mappings are not guaranteed to work when being accessed from a
service. There is no way to force them to work.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I figured it out. I simply added a step in each scheduled job to create and
then delete the drive mapping.
For example, I just use the "net use f: \\server\volume" to create the drive
ampping an then excute the rest of my job steps and then use another step w
ith this command "net use f: /delete" to delete it. Becuase the SQL Agent s
ervice is excuting the comm
and the drive mapping is in it's session and it works every time. It's nice
becuase the drive mapping isn't even seen by the desktop and works when the
server in not logged in. I remove the drive mapping at the end of each job
becuase the drive mapping
remains in tack as long as the SQL agent service stays running and therefore
if another job runs and tries to re-create the drive mapping the job will f
ail.

Monday, March 12, 2012

Make a same copy of SQL Server to a new machine

Dear all,
Since my old server's harddisk drive nearly used up all the free
space, I need to copy it from new server. Can I simply copy all the dat and
log files to new server without detach it provided that there're no user
using it and after that I attach them back to new sql server? I'm afraid in
case there're something go wrong in my new server and I can still use the
old server. And how can I transfer my logins to new server? I read some
articles that it may has orphan users if without transferring them. Or if
anyone knows the proper procedures on coping data into new server. Please
help. Thanks
Best Rdgs
EllisSee if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ezwt5MlvEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> Since my old server's harddisk drive nearly used up all the free
> space, I need to copy it from new server. Can I simply copy all the dat
and
> log files to new server without detach it provided that there're no user
> using it and after that I attach them back to new sql server? I'm afraid
in
> case there're something go wrong in my new server and I can still use the
> old server. And how can I transfer my logins to new server? I read some
> articles that it may has orphan users if without transferring them. Or if
> anyone knows the proper procedures on coping data into new server. Please
> help. Thanks
> Best Rdgs
> Ellis
>

Make a same copy of SQL Server to a new machine

Dear all,
Since my old server's harddisk drive nearly used up all the free
space, I need to copy it from new server. Can I simply copy all the dat and
log files to new server without detach it provided that there're no user
using it and after that I attach them back to new sql server? I'm afraid in
case there're something go wrong in my new server and I can still use the
old server. And how can I transfer my logins to new server? I read some
articles that it may has orphan users if without transferring them. Or if
anyone knows the proper procedures on coping data into new server. Please
help. Thanks
Best Rdgs
Ellis
See if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ezwt5MlvEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> Since my old server's harddisk drive nearly used up all the free
> space, I need to copy it from new server. Can I simply copy all the dat
and
> log files to new server without detach it provided that there're no user
> using it and after that I attach them back to new sql server? I'm afraid
in
> case there're something go wrong in my new server and I can still use the
> old server. And how can I transfer my logins to new server? I read some
> articles that it may has orphan users if without transferring them. Or if
> anyone knows the proper procedures on coping data into new server. Please
> help. Thanks
> Best Rdgs
> Ellis
>

Friday, March 30, 2012

Manage server in single user mode

Manage server in single user mode

Manage server in single user mode

Monday, March 26, 2012

Making Backup pay attention ?

Monday, March 19, 2012

Make SQL Agent use drive mapping?

Make SQL Agent use drive mapping?

Monday, March 12, 2012

Make a same copy of SQL Server to a new machine

Make a same copy of SQL Server to a new machine

Make a same copy of SQL Server to a new machine

maintenance plan wizard

Blog Archive

About Me