Wednesday, December 01, 2010

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
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"
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

No comments: