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

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 —-> http://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. http://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

Install google driver server 2008r2 Error loading Python DLL: C:\Users\admin\AppData\Local\ Temp\_MEI31122\python27.dll

From here

http://sysbadmin.wordpress.com/2013/01/19/how-to-install-google-drive-on-server-2008-r2/

Running Microsoft Server 2008 R2 as your home lab but also use it as your desktop? Trying to install Google Drive and getting the following error?

The installer encountered error 1603: Fatal error during installation.

Or

The application has failed to start because its side-by-side configuration is incorrect

It’s because it as a dependency it doesn’t tell you about, you need Microsoft Visual C++ 2008 SP1 Redistributable Package (x64) to install it and thenMicrosoft Visual C++ 2008 SP1 Redistributable Package (x86) to actually run it! As soon as you’ve installed it you can run the usual googledrivesync.exe installaer and that’s it!

Get the downloads (around 4Mb) from Microsoft below:

Microsoft Visual C++ 2008 SP1 Redistributable Package (x64)

Microsoft Visual C++ 2008 SP1 Redistributable Package (x86)

Follow

Get every new post delivered to your Inbox.

Join 241 other followers