SQL Server DB extract and restore from Litespeed

declare @extractor varchar(4000)
, @fullbkp_filename varchar(4000)
, @fullbkp_filepath varchar(4000)
, @ext_path varchar(4000)
,@database varchar(100)
,@fullrestore varchar(4000)
,@datafilepath varchar(500)
,@logfilepath varchar(500)
set @ext_path='R:\Litespeed'
set @fullbkp_filepath='R:\Litespeed\archive_bkps'
set @fullbkp_filename='ods_stage_201211011253.bak'
set @database='ods_stage'
set @datafilepath='R:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data'
set @logfilepath='L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs'

--EXTRACTOR
set @extractor='xp_cmdshell '''+@ext_path+'\extractor.exe -E  '+@fullbkp_filepath+'\'+@fullbkp_filename+' -F '+@fullbkp_filepath+'\'+@fullbkp_filename+''''
exec (@extractor)

--FULL
set @fullrestore='
RESTORE DATABASE '+@database+'
FROM  DISK = N'''+@fullbkp_filepath+'\'+@fullbkp_filename+''+'0''
,DISK = N'''+@fullbkp_filepath+'\'+@fullbkp_filename+''+'1''
,DISK = N'''+@fullbkp_filepath+'\'+@fullbkp_filename+''+'2''
with replace
,  MOVE N'''+@database+''+'_data'' TO N'''+@datafilepath+'\'+@database+'_data.mdf''
,  MOVE N'''+@database+''+'_log'' TO N'''+@logfilepath+'\'+@database+'_log.ldf''
,norecovery'
exec (@fullrestore)

--DIFF (enable reocvery after last diff apply)
RESTORE DATABASE [scratch]
FROM  DISK = N'R:\LiteSpeed\U1048487_201210311424.bak'
with file=2
--toggle to norecovery if need to apply another diff
, recovery


--toggle to norecovery if need to apply a diff
--,norecovery

/*
,  NOUNLOAD,  STATS = 10*/

/*
RESTORE DATABASE [scratch] FROM  DISK = N'R:\LiteSpeed\scratch_2012102108151.bak' WITH  FILE = 2
,  MOVE N'scratch' TO N'R:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\scratch.mdf'
,  MOVE N'scratch_log' TO N'L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs\scratch.ldf',  NOUNLOAD,  STATS = 10

RESTORE DATABASE [scratch] FROM  DISK = N'R:\LiteSpeed\scratch_2012102108152.bak' WITH  FILE = 3
,  MOVE N'scratch' TO N'R:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\scratch.mdf'
,  MOVE N'scratch_log' TO N'L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs\scratch.ldf',  NOUNLOAD,  STATS = 10*/

quick db restore

RESTORE DATABASE em700
FROM  DISK = N'C:\em700201211161241.BKP'

with replace

Quick sql server db bkp

--db name   em700

--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'', ''em700_networkdevice'', ''\\udrsql11\C$\em700'+@year+''+@month+''+@day+''+@hour+''+@minute+'.BKP'''
exec (@sqlscript)

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

--drop bkp device
exec sp_dropdevice 'em700_networkdevice'

Moving User databases

-->Moving user databases

-->Detach the database as follows
use master
   go
   sp_detach_db 'db'
   go

-->Next, copy the data files and the log files from the current location
-->(D:\Mssql7\Data) to the new location (E:\Sqldata).

-->Re-attach the database
use master
  go
  sp_attach_db 'db',
'H:\MSSQL\db1.mdf',
'H:\MSSQL\db2.mdf',
'H:\MSSQL\db3.mdf',
'I:\MSSQL\db4.mdf',
'I:\MSSQL\db5.mdf',
'I:\MSSQL\db6.mdf',
'N:\MSSQL\db_log.ldf'
--'N:\MSSQL\db_log2.ldf'
  go

-->Verify

use db
   go
   sp_helpfile
   go

-->source: http://support.microsoft.com/kb/224071
-->source for tempdb:


use master
go
Alter database tempdb modify file (name = tempdev, filename = 'K:\mssql\tempdev.mdf')
Alter database tempdb modify file (name = tempdev2, filename = 'K:\mssql\tempdev2.mdf')
Alter database tempdb modify file (name = tempdev3, filename = 'K:\mssql\tempdev3.mdf')
Alter database tempdb modify file (name = tempdev4, filename = 'K:\mssql\tempdev4.mdf')
Alter database tempdb modify file (name = tempdev5, filename = 'K:\mssql\tempdev5.mdf')
Alter database tempdb modify file (name = tempdev7, filename = 'K:\mssql\tempdev7.mdf')


go
Alter database tempdb modify file (name = templog, filename = 'J:\mssql\templog.ldf')
go

Update DB recovery model

--VIEW

SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = 'model' ;
GO

--CHANGE

USE master ;
ALTER DATABASE model SET RECOVERY FULL /*SIMPLE*/

What is Predictive Analytics and how does it relate to BI?

The "predictive" approach stems from modeling data and storing in such a way an analyst/statistician can use it to make those decisions "openly" rather than boxing a decision maker into a silo of data which can limit them. You'll see a tool such as SAS, SPSS (and there are other approaches) used to allow a person to perform"what-ifs" that'll evolve the data to help them drive to conclusions.

Traditional BI reporting does a fine job at aggregating and showing how a company has done to date but lacks the ability to make decisions on what one should do. Predictive analytics builds on the practice of data mining and the principles of building decisions on what's in front of a company based on existing data, market conditions and the driving conclusions of model scores.

db backups

USE [Operations]
GO

/****** Object:  StoredProcedure [dbo].[db_backups]    Script Date: 04/05/2011 15:13:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--exec db_backups 'model'
CREATE procedure [dbo].[db_backups]
@db_name varchar(50)
AS
-->declare working variables
DECLARE
@db nvarchar(255),
@cleanup varchar(4000),
@device nvarchar(4000),
@ddevice nvarchar(4000),
@backup varchar(4000),
@bkp_path varchar(1000),
@bkp_device varchar(1000),
@bkp_l_device varchar(2000),
@bkp_sql nvarchar(4000)

-->@bkp_l_device default setting
set @bkp_l_device=null

-->bkp file cleaner
declare cleaner cursor  for select bkp_partition_path  from operations.dbo.ADMIN_db_bkp_meta BM
inner join operations.dbo.ADMIN_db_bkp_partitions BP on BM.bkp_partition_id=BP.bkp_partition_id
where [db_name] =(''+@db_name+'')
order by 1

OPEN cleaner

FETCH NEXT FROM cleaner INTO @bkp_path
WHILE @@FETCH_STATUS = 0
BEGIN
-->purge old backup files.  full purge, no dynamic bkp file names
SET @cleanup='del /Q ' + '"'+@bkp_path+''+ @db_name + '*.bak"'
PRINT @cleanup
exec master..xp_cmdshell @cleanup
FETCH NEXT FROM cleaner INTO @bkp_path
END
CLOSE cleaner
DEALLOCATE cleaner

-->build backup device(s)
declare devices cursor  for select BP.bkp_partition_id  from operations.dbo.ADMIN_db_bkp_meta BM
inner join operations.dbo.ADMIN_db_bkp_partitions BP on BM.bkp_partition_id=BP.bkp_partition_id
where BM.[db_name] =(''+@db_name+'')
order by 1

OPEN devices

FETCH NEXT FROM devices INTO @bkp_device
WHILE @@FETCH_STATUS = 0
BEGIN
-->build bkp devices
set @bkp_path=(select  bkp_partition_path from operations.dbo.ADMIN_db_bkp_partitions BP
where bkp_partition_id=@bkp_device)


SET @device='exec master..sp_addumpdevice ''disk'', ''bkp_'+@db_name+'_device_'+@bkp_device+''', '''+@bkp_path+''+@db_name++@bkp_device+'.bak'''--+@bkp_device+
print @device

SET @bkp_l_device = --N'EXECUTE ' + @proc_name
CASE WHEN @bkp_l_device IS NOT NULL
THEN @bkp_l_device + ',' + 'bkp_'+@db_name+'_device_'+@bkp_device+''
ELSE 'bkp_'+@db_name+'_device_'+@bkp_device+''
END
exec sp_executesql @device
FETCH NEXT FROM devices INTO @bkp_device
END
CLOSE devices
DEALLOCATE devices
print @bkp_l_device

-->backup the database
set @bkp_sql='BACKUP DATABASE ['+@db_name+'] TO
'+@bkp_l_device+' WITH NOFORMAT, INIT,
NAME = N'''+@db_name+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'
print @bkp_sql
exec sp_executesql @bkp_sql

-->drop backup device(s)
declare ddevices cursor  for select BP.bkp_partition_id  from operations.dbo.ADMIN_db_bkp_meta BM
inner join operations.dbo.ADMIN_db_bkp_partitions BP on BM.bkp_partition_id=BP.bkp_partition_id
where BM.[db_name] =(''+@db_name+'')
order by 1

OPEN ddevices

FETCH NEXT FROM ddevices INTO @bkp_device
WHILE @@FETCH_STATUS = 0
BEGIN
-->drop bkp devices

SET @ddevice='exec sp_dropdevice bkp_'+@db_name+'_device_'+@bkp_device+''
print @ddevice

exec sp_executesql @ddevice
FETCH NEXT FROM ddevices INTO @bkp_device
END
CLOSE ddevices
DEALLOCATE ddevices


/*scripts for meta tables

CREATE TABLE operations.[dbo].[ADMIN_db_bkp_meta](
[db_name] [varchar](50) NOT NULL,
[bkp_partition_id] [int] NOT NULL
) ON [PRIMARY]


CREATE TABLE operations.[dbo].[ADMIN_db_bkp_partitions](
[bkp_partition_id] [int] NOT NULL,
[bkp_partition_path] [varchar](max) NOT NULL
) ON [PRIMARY]

insert ADMIN_db_bkp_partitions
(bkp_partition_id,bkp_partition_path)
values(1,
'\\HQFINSQL90\E$\backup\')

insert ADMIN_db_bkp_meta
(db_name,bkp_partition_id)
values ('model','1')
*/
GO