Bala Singam

I got this code snippet from my collegue , hope this can be helpful for others too

declare @dbname nvarchar(255)
set @dbname = 'databasename'
backup log @dbname with truncate_only
DBCC SHRINKDATABASE (@dbname, 0)

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

Yesterday I been requested by one of our client to write a database back up script so that they can schedule it as SQL Server job. As usual i google it and found plenty of solutions but I was looking for something simple and workable. Found it here

This is one line T-SQL code to back up any given database however I added 2 extra line code to have the date on the back up file name.

declare @filename as varchar(100)
SET @fileName = 'd:\' + 'Skanda' + '_' + CONVERT(VARCHAR(20),GETDATE(),112)</code> + '.BAK'
BACKUP DATABASE Skanda TO DISK = @fileName

The a backup file with following format will created Skanda_20100214.BAK

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

Update statement one of most common statement in developers daily routine but sometimes you will come across situation where you need update certain fields a table with values from other tables. Recently i came across this situation so i decided to share the sql statement with others throught this blog.

Example

I will update unitprice field of salesorderdetail table with the value of listprice field from product table using inner join where product.productid=salesorderdetail.productid, this is how the statement look like

update salesorderdetail
set unitprice=product.listprice
from salesorderdetail inner join product
on salesorderdetail.productid=product.productid