Advertisements

Stored Proc for auto restoring a database

USE [master]
GO

/****** Object: StoredProcedure [dbo].[usp_AutoRestoreDB] Script Date: 1/23/2015 9:21:22 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_AutoRestoreDB]
AS
SET NOCOUNT ON;

DECLARE
@BackupFiles VARCHAR(500)
,@data_file_path VARCHAR(512)
,@log_file_path VARCHAR(512)
,@RestoreFileList VARCHAR(2000)
,@RestoreStatement VARCHAR(3000)
,@MoveFiles VARCHAR(2000)
,@DBName VARCHAR(150)

DECLARE @filelist TABLE
(
LogicalName NVARCHAR(128) NOT NULL
,PhysicalName NVARCHAR(260) NOT NULL
,[Type] CHAR(1) NOT NULL
,FileGroupName NVARCHAR(120) NULL
,Size NUMERIC(20,0) NOT NULL
,MaxSize NUMERIC(20,0) NOT NULL
,FileID BIGINT NULL
,CreateLSN NUMERIC(25,0) NULL
,DropLSN NUMERIC(25,0) NULL
,UniqueID UNIQUEIDENTIFIER NULL
,ReadOnlyLSN NUMERIC(25,0) NULL
,ReadWriteLSN NUMERIC(25,0) NULL
,BackupSizeInBytes BIGINT NULL
,SourceBlockSize INT NULL
,FileGroupID INT NULL
,LogGroupGUID UNIQUEIDENTIFIER NULL
,DfferentialBaseLSN NUMERIC(25,0) NULL
,DifferentialBaseGUID UNIQUEIDENTIFIER NULL
,IsReadOnly BIT NULL
,IsPresent BIT NULL
,TDEThumbprint VARBINARY(32) NULL
)

SET @data_file_path=’\\TAR-DBDBS\C$\SQLDATA\’
SET @log_file_path=’\\TAR-DBDBS\C$\SQLLOGS\’
SET @DBName=’DB’

–Get last full backup:
SELECT
@BackupFiles=COALESCE(@BackupFiles+’,’,”)+’DISK = N”’+physical_device_name+””
FROM
DBDBS.msdb.dbo.backupset S
JOIN
DBDBS.msdb.dbo.backupmediafamily M
ON M.media_set_id = S.media_set_id
WHERE
backup_set_id = (
SELECT
MAX(backup_set_id)
FROM
DBDBS.msdb.dbo.backupset S
JOIN
DBDBS.msdb.dbo.backupmediafamily M
ON M.media_set_id = S.media_set_id
WHERE
S.database_name = @DBName
AND Type = ‘D’
AND user_name = ‘user’
)

SELECT
@RestoreFileList=’RESTORE FILELISTONLY FROM ‘+@BackupFiles+’ WITH FILE = 1 ‘

IF (@@microsoftversion/0x1000000)&0xff >= 10 –TDE capability
BEGIN
INSERT INTO @filelist
(
LogicalName
,PhysicalName
,Type
,FileGroupName
,Size
,MaxSize
,FileID
,CreateLSN
,DropLSN
,UniqueID
,ReadOnlyLSN
,ReadWriteLSN
,BackupSizeInBytes
,SourceBlockSize
,FileGroupID
,LogGroupGUID
,DfferentialBaseLSN
,DifferentialBaseGUID
,IsReadOnly
,IsPresent
,TDEThumbprint
)
EXEC (@RestoreFileList)
END
ELSE
BEGIN
INSERT INTO @filelist
(
LogicalName
,PhysicalName
,Type
,FileGroupName
,Size
,MaxSize
,FileID
,CreateLSN
,DropLSN
,UniqueID
,ReadOnlyLSN
,ReadWriteLSN
,BackupSizeInBytes
,SourceBlockSize
,FileGroupID
,LogGroupGUID
,DfferentialBaseLSN
,DifferentialBaseGUID
,IsReadOnly
,IsPresent
)
EXEC (@RestoreFileList)
END

–next version, do a count on filename, any >1 put in alternate data/log location.
SELECT
@MoveFiles=COALESCE(@MoveFiles+’,’,”)+’MOVE N”’+LogicalName+”’ to N”’
+CASE
WHEN type = ‘D’ THEN @data_file_path+RIGHT(physicalname,CHARINDEX(‘\’,REVERSE(physicalname),1)-1)
WHEN type = ‘L’ THEN @log_file_path+RIGHT(physicalname,CHARINDEX(‘\’,REVERSE(physicalname),1)-1)
ELSE ‘Full Text – code not complete’
END+””
FROM
@filelist

SELECT
@RestoreStatement=’RESTORE DATABASE [‘+@DBName+’] FROM ‘+@BackupFiles+’ WITH FILE = 1, ‘+@MoveFiles+’, NOUNLOAD, REPLACE, STATS = 20′

PRINT @RestoreStatement

EXEC(@RestoreStatement)

GO

Advertisements

Where in I create a lame .bat file for auto restoring a SQL Test DB

On occasion we have to restore a test DB to do testing of our ERP. It’s not hard but you have to do the clicking. I created an automagic .bat file to do this.

It starts off looking for a “go.txt” file, if it finds it then on to the fun

IF NOT EXIST C:\Auto_Restore\StartRestore\go.txt GOTO LabelExit

REM delete previous backups

ECHO Y | DEL C:\BackupRestore\*.*bak

REM copy the newest .bak file over

set folder1=\\Backups\SQL-Backups\ERP\FULL
set folder2=C:\BackupRestore
for /f “tokens=*” %%a in (‘dir /b /a-d /o-d “%folder1%\*.bak”‘) do copy “%folder1%\%%~a” “%folder2%” & goto next
:next

REM check there is only file

set cnt=0
for %%A in (C:\BackupRestore\*) do set /a cnt+=1

IF NOT %cnt% ==1 goto LabelExit

for %%A in (C:\BackupRestore\*.bak) do Nuke_Test.bat & Restore_Test.bat

:LabelExit
exit /b

—-     Nuke_Test.bat    —-

sqlcmd -S SERVERNAME -U user -P Password -i C:\Auto_Restore\Nuke_Test.sql

—-     Nuke_Test.sql     —–

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’Test’
GO
USE [master]
GO
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [Test] ******/
DROP DATABASE [Test]
GO

—–     Restore_Test.bat     —–

SqlCmd -S TEST -U User -P thepassword -Q “RESTORE DATABASE Test FROM DISK=’%1′”

my compadre DBA has a much more SQLY restore script here —-> https://michaelellerbeck.com/2015/01/23/stored-proc-for-auto-restoring-a-database/

User error message:The system is reporting the following error:Document’s contents was not correct: Interchange syntactically incorrect. Bizlink

So, previously I laid out how to define the metadata for capturing data so you can search on it. https://michaelellerbeck.com/2014/09/12/bizlink-creating-new-doc-types-for-tracking-metadata/

I learned something new. If perchance you are trying to capture some data from say a W05.

I had definitions for W0501, W0502

Then a partner sent some data in W0503

I figured it would just ignore the data since I wasn’t trying to capture it… nope. You need to define all elements that you might potentially get data sent on. Otherwise you will get the error “Document’s contents was not correct: Interchange syntactically incorrect.”

And there you have it, define all the things.

Quick way to add some newlines to EDI code

http://blog.jtbworld.com/2011/01/notepad2-tips-to-find-and-replace.html

ALFA AWUS036H with Kali Linux 1.0.9 & vmware Player

I got an ALFA AWUS036H for some wireless testing. I had trouble getting deauthentication to work. Eventually I ran across this post

http://forum.backbox.org/software-support/aireplay-ng-ignore-negative-one/

I’ve encountered this error in the past many times. I own four different Alfa wireless NICs, and to get around this problem all I have to do is:
service network-manager stop
airmon-ng check kill && airmon-ng start wlan0
ifconfig wlan0 down
airodump-ng -f 1000 mon0 (as an example)

I usually double check wpa_supplicant is not running as it does seem to affect monitor mode.

killall wpa_supplicant

You’re monitor interface (eg mon0) will still be available even after putting your primary wireless interface (eg wlan0) down. This fixes the -1 channel issue for me without patching any drivers.

This made it work for me

Also, to bump the trsx http://fl3x.us/blog/2013/01/12/setting-alfa-awus036h-to-30-dbm-1w-in-backtrack-5-r3/

Also, this command is useful after a capture

wpaclean <What you want the output cap file to be called> <cap file to clean>

A little retro gaming, Civilization II Multiplayer Gold Edition

So I got the hankering to play some Civ 2 on Windows 7 64bit. Well it worked for a bit but then it would crash. Luckily someone patched it for us.

http://pages.videotron.ca/masterx/civ2xp64patcher.zip

Or my local copy:

https://sites.google.com/site/mellerbeck/Home/civ2xp64patcher.zi?attredirects=0&d=1

rename to .zip