Solved

Moving WSUS Database SBS2011

Posted on 2014-02-02
6
1,709 Views
Last Modified: 2016-02-20
Hello,

I tried following these directions --> http://www.blackmanticore.com/caa80f452a8fb0337dfc80875e79dddc 

To move my susdb.mdf and SUSDB_log.ldf from my C:\WSUS\SUSDB\UpdateServicesDbFiles folder to P:\WSUS\Database as my C:\ is running low on space and the database is around 13GB. All went well up until the point I need to mount the database back. When I try run the command

sqlcmd -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -Q "sp_attach_db @dbname=N'SUSDB',\
       @filename1=N'P:\WSUS\Database\SUSDB.mdf',\
       @filename2=D'P:\WSUS\Database\SUSDB_log.ldf"

I get this:

C:\Windows\system32>sqlcmd -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -Q
"sp_attach_db @dbname=N'SUSDB',\
Msg 105, Level 15, State 1, Server SERVERNAME\MICROSOFT##SSEE, Line 1
Unclosed quotation mark after the character string 'sp_attach_db @dbname=N'SUSDB
','.
Msg 102, Level 15, State 1, Server SERVERNAME\MICROSOFT##SSEE, Line 1
Incorrect syntax near 'sp_attach_db @dbname=N'SUSDB','.

C:\Windows\system32>       @filename1=N'P:\WSUS\Database\SUSDB.mdf',\
'filename1' is not recognized as an internal or external command,
operable program or batch file.

C:\Windows\system32>       @filename2=D'P:\WSUS\Database\SUSDB_log.ldf"

I tried connecting with management studio express to try attach the database from there but it can no longer connect to MICROSOFT##SSEE. What is the correct systax I should use or how can i get this database re attached?

FYI, my OS is SBS2011
0
Comment
Question by:celeraadmin
  • 4
  • 2
6 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39828344
you might have put the SQLCMD command in multiple lines... which is not getting accepted


try this one,put them all in one line and it should work.

sqlcmd -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -Q "sp_attach_db @dbname=N'SUSDB',@filename1=N'P:\WSUS\Database\SUSDB.mdf',@filename2=D'P:\WSUS\Database\SUSDB_log.ldf"

Open in new window

0
 

Author Comment

by:celeraadmin
ID: 39828360
After running that I get the following:

C:\Windows\system32>sqlcmd -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -Q
"sp_attach_db @dbname=N'SUSDB',@filename1=N'P:\WSUS\Database\SUSDB.mdf',@filenam
e2=D'P:\WSUS\Database\SUSDB_log.ldf"
Msg 105, Level 15, State 1, Server CELERASERVER\MICROSOFT##SSEE, Line 1
Unclosed quotation mark after the character string 'P:\WSUS\Database\SUSDB_log.l
df
'.

C:\Windows\system32>

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39828366
spotted it now, the unclosed quotation mark is at the end of the statement, right after susdb_log.ldf.

Added that now and the code is below, give it a try and let us know the results.

C:\Windows\system32>sqlcmd -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -Q
"sp_attach_db @dbname=N'SUSDB',@filename1=N'P:\WSUS\Database\SUSDB.mdf',@filenam
e2=D'P:\WSUS\Database\SUSDB_log.ldf'"

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39828374
I also see that there is another issue here that you are using the @filename2 = D' where as it should be @filename2 = N'

C:\Windows\system32>sqlcmd -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -Q
"sp_attach_db @dbname=N'SUSDB',@filename1=N'P:\WSUS\Database\SUSDB.mdf',@filenam
e2=N'P:\WSUS\Database\SUSDB_log.ldf'"

Open in new window

0
 

Author Comment

by:celeraadmin
ID: 39828390
That did the trick. You my friend are my hero. THANK YOU. Question... what do the "D" and "N" values represent?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39828397
There is nothing as D in SQL Server... it is syntactically wrong.
N means it is a NVARCHAR data type not a VARCHAR dataType. NVARCHAR datatype can hold charecter outside the normal ASCII range.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help  needed 3 35
Help Required 2 39
convert null in sql server 12 46
Conver SQL Server 2008 R2 from sql_latin1_general_cp1_ci_as to Arabic_CI_AS 9 22
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question