SQL 2005 Embedded and Moving Sharepoint Services Database

[Watt, Bobby]

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.

[Jonathan Psaila-Depasquale]

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

Post by Watt, Bobby
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.

[Watt, Bobby]

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>

Post by Jonathan Psaila-Depasquale
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
backup log mydb
to disk = 'c:\mybackuppath\mybackupfile.bak'
go
use mydb
go
dbcc shrinkfile (mylargelogfile, 500)
Jonathan

Post by Watt, Bobby
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.

[Jonathan Psaila-Depasquale]

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/library/aa224033(SQL.80).aspx.

Hope this solves the problem.

Jonathan

Post by Watt, Bobby
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>

Post by Jonathan Psaila-Depasquale
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
backup log mydb
to disk = 'c:\mybackuppath\mybackupfile.bak'
go
use mydb
go
dbcc shrinkfile (mylargelogfile, 500)
Jonathan

Post by Watt, Bobby
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.

[Watt, Bobby]

That was the ticket. Thanks.

Post by Jonathan Psaila-Depasquale
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
[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/library/aa224033(SQL.80).aspx.
Hope this solves the problem.
Jonathan

Post by Watt, Bobby
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>

Post by Jonathan Psaila-Depasquale
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
backup log mydb
to disk = 'c:\mybackuppath\mybackupfile.bak'
go
use mydb
go
dbcc shrinkfile (mylargelogfile, 500)
Jonathan

Post by Watt, Bobby
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.

[Leo Mattern]

Jonathan, one year and a half later, this is still the ticket for the hyphen problem. Thanks for sharing the knowledge and Happy New Year 2009.

From http://www.developmentnow.com/g/107_2007_8_0_0_1008133/SQL-2005-Embedded-and-Moving-Sharepoint-Services-Database.ht

Posted via DevelopmentNow.com Group
http://www.developmentnow.com/g/