SQL Server Database Backup Script

Looking for a quick database backup script with a datetimestamp?  Replace the [DBName] with your database name.  Note the [FolderPath] needs to be updated as well.

SQL Server Database Backup Script with datetimestamp:



--db name   [DBName]

--use master db for backups
use master

--variables
declare @year varchar(4)set @year=datepart(yy,getdate())
declare @month varchar(2)set @month=case when datepart(mm,getdate())<10 then '0'+cast(datepart(mm,getdate()) as varchar(1)) else cast(datepart(mm,getdate())as varchar) end
declare @day varchar(2)set @day=case when datepart(dd,getdate())<10 then '0'+cast(datepart(dd,getdate()) as varchar(1)) else cast(datepart(dd,getdate())as varchar) end
declare @hour varchar(2) set @hour=case when datepart(hh,getdate())<10 then '0'+cast(datepart(hh,getdate()) as varchar(1)) else cast(datepart(hh,getdate())as varchar) end
declare @minute varchar(2)set @minute=case when datepart(mi,getdate())<10 then '0'+cast(datepart(mi,getdate()) as varchar(1)) else cast(datepart(mi,getdate())as varchar) end

--create bkp device
declare @sqlscript varchar(2000)
set @sqlscript='exec sp_addumpdevice ''disk'', ''[DBName]_networkdevice'', ''[FolderPath]\[DBNAME]'+@year+''+@month+''+@day+''+@hour+''+@minute+'.BKP'''
exec (@sqlscript)

--bkp db
BACKUP DATABASE [DBName] TO [DBName_networkdevice] WITH  INIT ,  NOUNLOAD ,  RETAINDAYS = 5,  NAME = N'[DBName backup',  NOSKIP ,  STATS = 10,  DESCRIPTION = N'[DBName] backup',  NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='[DBName]'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='[DBName]')
RESTORE VERIFYONLY FROM  [[DBName]_networkdevice]  WITH FILE = @i

--drop bkp device
exec sp_dropdevice '[DBName]_networkdevice'

Execute SSIS package from command line for 32bit

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /DTS "\MSDB\[FOLDER]\[PACKAGE NAME]" /SERVER [SERVER NAME] /DECRYPT [PASSWORD] /X86 /CHECKPOINTING OFF /REPORTING E

Row Number: Quick TSQL Row_Number

Create a quick row number in a sql statement:

select ROW_NUMBER() OVER(ORDER BY [COLUMN] ASC/DESC) AS Row
,* from [TABLE])

ETL Framework Stages

ETL Framework Stages:

STAGE:    storage area between the source data and the data warehouse or ODS or BI marts.  typically temporary in nature.  Used for data cleansing, landing of data in a like format and placing the data out of its source format.

PERSISTENT STAGE:  storage area of data that allows transactional and incrementally changing data to be stored.  Typically the data is kept close to its originating structure and is not related to other sources as one would see in a dimensional approach or 3NF ODS.  It is not used as a system or record, but rather a processing area for historical storage  Meta data columns help process changes to keep history and the likes of type 2 (or others) disciplines can be applied.

ODS:  An operational data store (ODS) is designed to integrate data from multiple sources. The data used then as the system of record and will be used to update/insert data back out to source systems.

DW:  The data warehouse (DW) is a database used for reporting and analysis. It acts as a repository of data that can be fed from STAGE directly, PERSISTENT STAGE (for historical purposes), or the ODS . Data warehouses typically contain current and historical data.

BI MART (DATA MART):  A focused slice of the data warehouse built to focus on a specific subject area.  It can be separated from the DW to help with storage, security, or further business logic not desired in the DW.

SP_WHO to see SQL statement

Quick query to see user and full SQLstatement.  Don't forget one can use the Profile from SSMS for a full view of all db activity.


SELECT  D.text SQLStatement, A.Session_ID SPID, ISNULL(B.status,A.status) Status,
A.login_name Login, A.host_name HostName, C.BlkBy,  DB_NAME(B.Database_ID) DBName,
B.command, ISNULL(B.cpu_time, A.cpu_time) CPUTime, ISNULL((B.reads + B.writes),
(A.reads + A.writes)) DiskIO,  A.last_request_start_time LastBatch, A.program_name FROM
   sys.dm_exec_sessions A    LEFT JOIN    sys.dm_exec_requests B  
 ON A.session_id = B.session_id   LEFT JOIN    
 (        SELECT                 A.request_session_id SPID,        
       B.blocking_session_id BlkBy        
  FROM sys.dm_tran_locks as A          
 INNER JOIN sys.dm_os_waiting_tasks as B        
  ON A.lock_owner_address = B.resource_address        ) C
  ON A.Session_ID = C.SPID   OUTER APPLY sys.dm_exec_sql_text(sql_handle) D

Rank Over

,RANK() OVER
    (PARTITION BY col] ORDER BY [colA] desc, [colB] desc) AS Rank

from MSN  (http://msdn.microsoft.com/en-us/library/ms176102.aspx):

USE AdventureWorks2012;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO