Question: DB from SQL 2005 to SQL 2000

When initially trying to migrate SharePoint databases from the same machine that is running the whole FARM, I got this error:

SqlException: Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE DATABASE is terminating abnormally.

Afer researching this, I discovered that it should work after upgrading the SQL 2000 engine to SP4 (as we had SP3). Over the weekend, we upgraded our production database to SP4. Then I tried the SharePoint restore to that production database again
(using stsadm -o restore -directory \\server2\disks\sharepointbackup -restoremethod new and applying the new db server name, server db and log file location, and then the new db names). The new error is:

SqlException: The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. RESTORE DATABASE is terminating abnormally.

If anyone else is having a similary issue and knows how to get around this, please post a comment or email me.

osql query to text file - thanks to Brain Knight

Piping a Query to a Text File
By Brian Knight, 2001/05/21

The case where one has to send the results of a query to a text file comes up quite often. 

General this case take the form of a maintenance script that you run through Agent nightly 

and would like to see the results in the morning.

In the above example, you can create a CMDEXEC job to run the below command. This will 

output the results of the query inside c:\inputquery.txt to a separate file.

\mssql7\binn\osql -Usa -Ppassword -Ic:\inputquery.txt -Oc:\outputdir\output.txt


You can also use -E switch for trusted connection if you don't want to hard code a password 

into your job. From an Query Analyzer window or inside the stored procedure you can run the 

above statement with an extended stored procedure called XP_CMDSHELL:


master..xp_cmdshell 'osql
 -Usa
 -Pmisty
 -dNorthwind
 -Q"select * from products"
 -oc:\output.txt'


The -Q parameter in the above syntax runs the query then exits ISQL. You may also want to 

add the additional NO_OUTPUT parameter at the end of query as shown below to supress any 

output to the client:


master..xp_cmdshell 'osql
 -Usa
 -Pmisty
 -dNorthwind
 -Q"select * from products"
 -oc:\output.txt', no_output


There are other methods of piping a query to a text file by using OLE Automation but they 

are much more complex than this method.


By Brian Knight, 2001/05/21


usage: osql              [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w columnwidth]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-L list servers]      [-c cmdend]            [-D ODBC DSN name]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-n remove numbering]  [-m errorlevel]
  [-r msgs to stderr]    [-V severitylevel]
  [-i inputfile]         [-o outputfile]
  [-p print statistics]  [-b On error batch abort]
  [-O use Old ISQL behavior disables the following]
       batch processing
      Auto console width scaling
      Wide messages
      default errorlevel is -1 vs 1
  [-? show syntax summary]

Petit Jean National State Park, 2006

Here are my boys, my daughter, and 2 of my neices and 2 of my nephews. This pic was taken at Petit Jean Ntl Park during the winter of 2006. Here's a mountaintop view of the beautiful falls there.