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*/