Using osql to do backup/restore and escaping special characters in 'LIKE'

Using osql util
Some quick quips to share on Sql Server on using osql command line util to performe  backups/restore
backup

osql -E -Sinstancename -q "BACKUP DATABASE [dbname] TO  DISK = N'backupfile.bak' WITH NOFORMAT, INIT,  NAME = N'dbName Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"

restore

osql -E -Sinstancename  -q "RESTORE DATABASE [dbname] FROM  DISK = N'backupfile.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10"

Using 'LIKE'
Using 'LIKE' to determine if a value or a column of values IS ALL DIGITS

SELECT * FROM testtable WHERE columnxyz NOT LIKE '%[^0-9]%'

'^' means 'NOT', so the expression above evaluates to 'Select only rows from testtable where everything in the columnxyz is a digit from 0 to 9'
Use the ESCAPE clause and the escape character in order to use special characters in your expression.
E.g. to find the exact character string 10-15% in column c1 of the mytbl2 table, we use

SELECT c1 FROM mytbl2 WHERE c1 LIKE '%10-15!% off%' ESCAPE '!'

Further reading on the BOL LIKE reference

Using TRUNCATE_ONLY in SQL Server 2008

After upgrading to SQL Server 2008 from 2005, I got an error with one of my scripts. I am using Sql 2008 SqlExpress 10GB.

This is my T-SQL

BACKUP LOG "pathtomylogfile" WITH TRUNCATE_ONLY;

And I get this error:
Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.


So I asked myself if there is an option similar to what 'TRUNCATE_ONLY' was doing in 2005?
I posted a query at Ask Sql Server Central

I got very nice responses, including a link to this article by Brent Ozar


Check out the responses above, hope it helps you if you are upgrading to Sql Server 2008.
You can also read further on this article on how to configure Sql Server 2008

Using SQL Server CROSS APPLY to read XML segment

Here is an interesting code snippet:

declare @XML xml
set @XML ='<students>
<student name="Julius">
<subjects>
<subject name="Maths" score="90"></subject>
<subject name="Art" score="50"></subject>
<subject name="English" score="70"></subject>
</subjects>
</student>
<student name="Becky">
<subjects>
<subject name="Maths" score="90"></subject>
<subject name="Art" score="50"></subject>
<subject name="English" score="70"></subject>
</subjects>'
select ExpressionAlias.student_name,
ExpressionAlias.subject_name,
ExpressionAlias.score
From @XML.nodes('./students/student') Student (rowset)
Cross Apply
Student.rowset.nodes('./subjects/subject') Subject (rowset)
Cross Apply (
Select Student.rowset.value('@name','NVARCHAR(20)'),
Subject.rowset.value('@name','NVARCHAR(20)'),
Subject.rowset.value('@score','INTEGER')
) ExpressionAlias (student_name, subject_name,score)
Order By
ExpressionAlias.student_name ASC,
ExpressionAlias.score DESC;

"Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails."

One useful system stored procedure is sp_helpdb. This stored procedure returns information about all of your databases on your server such as the size, owner, when it was created and the database settings. One issue that you may run into is that the stored procedure does not provide data, but an error occurs instead. The error that you receive is "Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails."

Useful tips to fix this here