SQL 2005 Embedded and Moving Sharepoint Services Database

[Database Administrator]

I have a SQL 2005 Embedded database that was created by SharePoint Services.
It's ldf file has grown to almost 3 gig, and I need to move it off the C
drive. I have successfully moved some of the other databases using the OSQL
command, but when I try to move this one particular database I keep getting
a syntax error. I've checked and rechecked the file name and I'm typing it
in right. It's the Sharepoint_Config database.

SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b.mdf

The error states Incorrect Syntax near '-'.



Couple of questions.

How can I connect to this database using the SQL 2005 Management Studio? I'm
connecting to CYAN\MICROSOFT##SSEE and using Windows Authentication.

This is the error I get.


Cannot connect to CYAN\MICROSOFT##SSEE.

===================================

An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified) (.Net SqlClient Data Provider)

Any suggestions would be appreciated.

[Database Administrator]

Hi,

Why are you moving the log file off the C drive? If you're moving it
because it has grown too large and you're running out of space then you're
going about things the wrong way. That file will keep growing if you're
running your database in FULL or BULK_LOGGED recovery mode and eventually no
matter where you place it you'll keep running out of space. What you need
to do is to (regularly) run a backup of the log then shrink the file to a
size that make sense for your environment (something that avoids a lot of
growth operations). So if you connect via osql run something as follows:

backup log mydb
to disk = 'c:\mybackuppath\mybackupfile.bak'
go

use mydb
go

dbcc shrinkfile (mylargelogfile, 500)


Jonathan


"Watt, Bobby" wrote in message
news:3DE82A96-888E-4307-95F9-23EF23C473DB@microsoft.com...
>I have a SQL 2005 Embedded database that was created by SharePoint
>Services. It's ldf file has grown to almost 3 gig, and I need to move it
>off the C drive. I have successfully moved some of the other databases
>using the OSQL command, but when I try to move this one particular database
>I keep getting a syntax error. I've checked and rechecked the file name and
>I'm typing it in right. It's the Sharepoint_Config database.
>
> SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b.mdf
>
> The error states Incorrect Syntax near '-'.
>
>
>
> Couple of questions.
>
> How can I connect to this database using the SQL 2005 Management Studio?
> I'm connecting to CYAN\MICROSOFT##SSEE and using Windows Authentication.
>
> This is the error I get.
>
>
> Cannot connect to CYAN\MICROSOFT##SSEE.
>
> ===================================
>
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 26 - Error Locating
> Server/Instance Specified) (.Net SqlClient Data Provider)
>
> Any suggestions would be appreciated.
>

[Database Administrator]

Ok, That makes total sense. But here is the problem. I'm getting that syntax
error again with this database. Below is the command I ran, with the name of
the database. For some reason it doesn't like the dashes in the database
name.

Any ideas?


C:\>cd program files

C:\Program Files>cd microsoft sql server

C:\Program Files\Microsoft SQL Server>cd 90

C:\Program Files\Microsoft SQL Server\90>cd tools

C:\Program Files\Microsoft SQL Server\90\Tools>cd binn

C:\Program Files\Microsoft SQL Server\90\Tools\binn>sqlcmd -S
\\.\pipe\mssql$mic
rosoft##ssee\sql\query -E
1> backup log SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b_log
2> to disk = 'd:\mybackup.bak'
3> Go
Msg 102, Level 15, State 1, Server CYAN\MICROSOFT##SSEE, Line 1
Incorrect syntax near '-'.
1>


"Jonathan Psaila-Depasquale" wrote in message
news:u38$bKo4HHA.5212@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> Why are you moving the log file off the C drive? If you're moving it
> because it has grown too large and you're running out of space then you're
> going about things the wrong way. That file will keep growing if you're
> running your database in FULL or BULK_LOGGED recovery mode and eventually
> no matter where you place it you'll keep running out of space. What you
> need to do is to (regularly) run a backup of the log then shrink the file
> to a size that make sense for your environment (something that avoids a
> lot of growth operations). So if you connect via osql run something as
> follows:
>
> backup log mydb
> to disk = 'c:\mybackuppath\mybackupfile.bak'
> go
>
> use mydb
> go
>
> dbcc shrinkfile (mylargelogfile, 500)
>
>
> Jonathan
>
>
> "Watt, Bobby" wrote in message
> news:3DE82A96-888E-4307-95F9-23EF23C473DB@microsoft.com...
>>I have a SQL 2005 Embedded database that was created by SharePoint
>>Services. It's ldf file has grown to almost 3 gig, and I need to move it
>>off the C drive. I have successfully moved some of the other databases
>>using the OSQL command, but when I try to move this one particular
>>database I keep getting a syntax error. I've checked and rechecked the
>>file name and I'm typing it in right. It's the Sharepoint_Config database.
>>
>> SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b.mdf
>>
>> The error states Incorrect Syntax near '-'.
>>
>>
>>
>> Couple of questions.
>>
>> How can I connect to this database using the SQL 2005 Management Studio?
>> I'm connecting to CYAN\MICROSOFT##SSEE and using Windows Authentication.
>>
>> This is the error I get.
>>
>>
>> Cannot connect to CYAN\MICROSOFT##SSEE.
>>
>> ===================================
>>
>> An error has occurred while establishing a connection to the server.
>> When connecting to SQL Server 2005, this failure may be caused by the
>> fact that under the default settings SQL Server does not allow remote
>> connections. (provider: SQL Network Interfaces, error: 26 - Error
>> Locating Server/Instance Specified) (.Net SqlClient Data Provider)
>>
>> Any suggestions would be appreciated.
>>
>
>

[Database Administrator]

Yes, this is going to happen because the SharePoint setup created a database
with "illegal" characters in its name, in your case the hyphen. What you
need to do is to put the name of your database within square brackets as
follows whenever using it in a T-SQL command:

[SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b_log]

For future reference on what constitutes a "legal" name read through this
article - http://msdn2.microsoft.com/en-us/lib...3(SQL.80).aspx.

Hope this solves the problem.

Jonathan


"Watt, Bobby" wrote in message
news:C2C9E40D-F909-4340-B843-A08522EC9DAD@microsoft.com...
> Ok, That makes total sense. But here is the problem. I'm getting that
> syntax error again with this database. Below is the command I ran, with
> the name of the database. For some reason it doesn't like the dashes in
> the database name.
>
> Any ideas?
>
>
> C:\>cd program files
>
> C:\Program Files>cd microsoft sql server
>
> C:\Program Files\Microsoft SQL Server>cd 90
>
> C:\Program Files\Microsoft SQL Server\90>cd tools
>
> C:\Program Files\Microsoft SQL Server\90\Tools>cd binn
>
> C:\Program Files\Microsoft SQL Server\90\Tools\binn>sqlcmd -S
> \\.\pipe\mssql$mic
> rosoft##ssee\sql\query -E
> 1> backup log SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b_log
> 2> to disk = 'd:\mybackup.bak'
> 3> Go
> Msg 102, Level 15, State 1, Server CYAN\MICROSOFT##SSEE, Line 1
> Incorrect syntax near '-'.
> 1>
>
>
> "Jonathan Psaila-Depasquale" wrote in message
> news:u38$bKo4HHA.5212@TK2MSFTNGP04.phx.gbl...
>> Hi,
>>
>> Why are you moving the log file off the C drive? If you're moving it
>> because it has grown too large and you're running out of space then
>> you're going about things the wrong way. That file will keep growing if
>> you're running your database in FULL or BULK_LOGGED recovery mode and
>> eventually no matter where you place it you'll keep running out of space.
>> What you need to do is to (regularly) run a backup of the log then shrink
>> the file to a size that make sense for your environment (something that
>> avoids a lot of growth operations). So if you connect via osql run
>> something as follows:
>>
>> backup log mydb
>> to disk = 'c:\mybackuppath\mybackupfile.bak'
>> go
>>
>> use mydb
>> go
>>
>> dbcc shrinkfile (mylargelogfile, 500)
>>
>>
>> Jonathan
>>
>>
>> "Watt, Bobby" wrote in message
>> news:3DE82A96-888E-4307-95F9-23EF23C473DB@microsoft.com...
>>>I have a SQL 2005 Embedded database that was created by SharePoint
>>>Services. It's ldf file has grown to almost 3 gig, and I need to move it
>>>off the C drive. I have successfully moved some of the other databases
>>>using the OSQL command, but when I try to move this one particular
>>>database I keep getting a syntax error. I've checked and rechecked the
>>>file name and I'm typing it in right. It's the Sharepoint_Config
>>>database.
>>>
>>> SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b.mdf
>>>
>>> The error states Incorrect Syntax near '-'.
>>>
>>>
>>>
>>> Couple of questions.
>>>
>>> How can I connect to this database using the SQL 2005 Management Studio?
>>> I'm connecting to CYAN\MICROSOFT##SSEE and using Windows Authentication.
>>>
>>> This is the error I get.
>>>
>>>
>>> Cannot connect to CYAN\MICROSOFT##SSEE.
>>>
>>> ===================================
>>>
>>> An error has occurred while establishing a connection to the server.
>>> When connecting to SQL Server 2005, this failure may be caused by the
>>> fact that under the default settings SQL Server does not allow remote
>>> connections. (provider: SQL Network Interfaces, error: 26 - Error
>>> Locating Server/Instance Specified) (.Net SqlClient Data Provider)
>>>
>>> Any suggestions would be appreciated.
>>>
>>
>>
>

[Database Administrator]

That was the ticket. Thanks.


"Jonathan Psaila-Depasquale" wrote in message
news:ugSFB%23x4HHA.5852@TK2MSFTNGP02.phx.gbl...
> Yes, this is going to happen because the SharePoint setup created a
> database with "illegal" characters in its name, in your case the hyphen.
> What you need to do is to put the name of your database within square
> brackets as follows whenever using it in a T-SQL command:
>
> [SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b_log]
>
> For future reference on what constitutes a "legal" name read through this
> article - http://msdn2.microsoft.com/en-us/lib...3(SQL.80).aspx.
>
> Hope this solves the problem.
>
> Jonathan
>
>
> "Watt, Bobby" wrote in message
> news:C2C9E40D-F909-4340-B843-A08522EC9DAD@microsoft.com...
>> Ok, That makes total sense. But here is the problem. I'm getting that
>> syntax error again with this database. Below is the command I ran, with
>> the name of the database. For some reason it doesn't like the dashes in
>> the database name.
>>
>> Any ideas?
>>
>>
>> C:\>cd program files
>>
>> C:\Program Files>cd microsoft sql server
>>
>> C:\Program Files\Microsoft SQL Server>cd 90
>>
>> C:\Program Files\Microsoft SQL Server\90>cd tools
>>
>> C:\Program Files\Microsoft SQL Server\90\Tools>cd binn
>>
>> C:\Program Files\Microsoft SQL Server\90\Tools\binn>sqlcmd -S
>> \\.\pipe\mssql$mic
>> rosoft##ssee\sql\query -E
>> 1> backup log SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b_log
>> 2> to disk = 'd:\mybackup.bak'
>> 3> Go
>> Msg 102, Level 15, State 1, Server CYAN\MICROSOFT##SSEE, Line 1
>> Incorrect syntax near '-'.
>> 1>
>>
>>
>> "Jonathan Psaila-Depasquale" wrote in message
>> news:u38$bKo4HHA.5212@TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> Why are you moving the log file off the C drive? If you're moving it
>>> because it has grown too large and you're running out of space then
>>> you're going about things the wrong way. That file will keep growing if
>>> you're running your database in FULL or BULK_LOGGED recovery mode and
>>> eventually no matter where you place it you'll keep running out of
>>> space. What you need to do is to (regularly) run a backup of the log
>>> then shrink the file to a size that make sense for your environment
>>> (something that avoids a lot of growth operations). So if you connect
>>> via osql run something as follows:
>>>
>>> backup log mydb
>>> to disk = 'c:\mybackuppath\mybackupfile.bak'
>>> go
>>>
>>> use mydb
>>> go
>>>
>>> dbcc shrinkfile (mylargelogfile, 500)
>>>
>>>
>>> Jonathan
>>>
>>>
>>> "Watt, Bobby" wrote in message
>>> news:3DE82A96-888E-4307-95F9-23EF23C473DB@microsoft.com...
>>>>I have a SQL 2005 Embedded database that was created by SharePoint
>>>>Services. It's ldf file has grown to almost 3 gig, and I need to move it
>>>>off the C drive. I have successfully moved some of the other databases
>>>>using the OSQL command, but when I try to move this one particular
>>>>database I keep getting a syntax error. I've checked and rechecked the
>>>>file name and I'm typing it in right. It's the Sharepoint_Config
>>>>database.
>>>>
>>>> SharePoint_Config_0e211848-1a2a-47d8-a407-ec9839afe12b.mdf
>>>>
>>>> The error states Incorrect Syntax near '-'.
>>>>
>>>>
>>>>
>>>> Couple of questions.
>>>>
>>>> How can I connect to this database using the SQL 2005 Management
>>>> Studio? I'm connecting to CYAN\MICROSOFT##SSEE and using Windows
>>>> Authentication.
>>>>
>>>> This is the error I get.
>>>>
>>>>
>>>> Cannot connect to CYAN\MICROSOFT##SSEE.
>>>>
>>>> ===================================
>>>>
>>>> An error has occurred while establishing a connection to the server.
>>>> When connecting to SQL Server 2005, this failure may be caused by the
>>>> fact that under the default settings SQL Server does not allow remote
>>>> connections. (provider: SQL Network Interfaces, error: 26 - Error
>>>> Locating Server/Instance Specified) (.Net SqlClient Data Provider)
>>>>
>>>> Any suggestions would be appreciated.
>>>>
>>>
>>>
>>
>
>