How-to rollback a MSSQL db from file via command line

Download project

START ===== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\RestoreDbFromFile.sql

-- Restore database from file

-----------------------------------------------------------------

use master

go

declare @FileToRollBack varchar(100), @DbFilesBackUpDir varchar(100),

@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),

@databaseDataFile varchar(100), @databaseLogFile varchar(100),

@DbNameToRestore varchar(100), @execSql nvarchar(1000)

--'$(_DbFilesBackUpDir)'

-- Set the name of the database to restore

set @DbNameToRestore = '$(_DbNameToRestore)'

-- Set the path to the directory containing the database backup

set @DbFilesBackUpDir =  '$(_DbFilesBackUpDir)' -- such as 'c:\temp\'

-- Create the backup file name based on the restore directory, the database name and today's date

--@FileToRollBack = @DbFilesBackUpDir + @DbNameToRestore + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'

declare @DBFileName varchar(256)    

--set @DBFileName = @DbNameToRestore + '_' + replace(convert(varchar, getdate(), 112), '-', '.') + '.bak'

--BOOM BOOM BOOM CHANGE THIS 

set @FileToRollBack = '$(_FileToRollBack)'

-- Get the data file and its path

select @databaseDataFile = rtrim([Name]),

@databaseDataFilename = rtrim([Filename])

from master.dbo.sysaltfiles as files

inner join

master.dbo.sysfilegroups as groups

on

files.groupID = groups.groupID

where DBID = (

select dbid

from master.dbo.sysdatabases

where [Name] = @DbNameToRestore

)

-- Get the log file and its path

select @databaseLogFile = rtrim([Name]),

@databaseLogFilename = rtrim([Filename])

from master.dbo.sysaltfiles as files

where DBID = (

select dbid

from master.dbo.sysdatabases

where [Name] = @DbNameToRestore

)

and

groupID = 0

print 'Killing active connections to the "' + @DbNameToRestore + '" database'

-- Create the sql to kill the active database connections

set @execSql = ''

select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '

from master.dbo.sysprocesses

where db_name(dbid) = @DbNameToRestore

and

DBID <> 0

and

spid <> @@spid

exec (@execSql)

print 'Restoring "' + @DbNameToRestore + '" database from "' + @FileToRollBack + '" with '

print ' data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"'

print ' log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"'

set @execSql = '

restore database [' + @DbNameToRestore + ']

from disk = ''' + @FileToRollBack + '''

with

file = 1,

move ''' + @databaseDataFile + ''' to ' + '''' + @databaseDataFilename + ''',

move ''' + @databaseLogFile + ''' to ' + '''' + @databaseLogFilename + ''',

norewind,

nounload,

replace'

exec sp_executesql @execSql

exec('use ' + @DbNameToRestore)

go

-- If needed, restore the database user associated with the database

/*

exec sp_revokedbaccess 'myDBUser'

go

exec sp_grantdbaccess 'myDBUser', 'myDBUser'

go

exec sp_addrolemember 'db_owner', 'myDBUser'

go

use master

go

*/ 

END ================== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\RestoreDbFromFile.sql

. 

START ===== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\RollBackDbFromFile.cmd

ECHO STARTTING ROLLBACK

ECHO FOR EACH SQL FILE DO RUN IT THIS WILL TAKE A WHILE 

@ECHO OFF

:: DOC AT THE END 

:: AND READS THE COMMENTS BEFORE EACH LINE !!!

:: BOOM BOOM BOOM CHANGE THIS ENVIRONMENT VARIABLES ACCORDING TO YOUR CONFIGURATION SETUP

:: BOOM BOOM BOOM DO NOT ADD EXTRA SPACE BEFORE THE = SIGN IN THE VARS OR AT THE END OF THE LINE !!!

SET _DbName=master

SET _sqlAdminUserName=ysg

SET _hostNameAndInstance=hostName\POC_QA

SET _passwd=pass

SET _MillisecondsToSleep=10

SET _DbFilesBackUpDir=D:\Data\Backups\

SET _FileToRollBack=%_DbFilesBackUpDir%POC_FB_20100331.bak

SET _DbNameToRestore=POC_DEV

ECHO OPEN THIS FILE AND AND SET  THE ENV VARS ACCORDING TO YOUR CONFIGURATION 

set _

pause

CLS

mkdir log 

::DEBUG ECHO FOR EACH SQL FILE DO RUN IT THIS WILL TAKE A WHILE 

ECHO START RESTORE 

for /f %%i  in ('dir *.SQL /s /b /o') do ECHO RUNNING %%i1>>"..\install.log"&SQLCMD -U %_sqlAdminUserName% -P %_passwd% -S %_hostNameAndInstance% -d %_DbName% -t 300 -w 80 -u -p 1 -b -i %%i  -r1 1>> "log\install.log" 2>> "log\error.log" 

ECHO GO ONE FOLDER UP 

cd ..

ECHO SLEEP FOR 1 SECOND 

ping -n 1 127.0.0.1 >NUL

ECHO DONE WITH BACKUP GOING UP 

ECHO Please , Review the log files and sent them back to Advanced Application Support 

cmd /c start /max log\install.log

CMD /C start /MAX log\error.log

echo DONE !!!

ECHO HIT A KEY TO EXIT 

END ================== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\RollBackDbFromFile.cmd

. 

START ===== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\ZipMe.cmd

@echo off

echo this batch file zips recursively everything from the current directory

echo into one nicely timestamped filename and 

echo this batch files needs the gnu zip utility 

echo google download gnu zip for windows

:: JUST MAKE SURE WE DO NOT HAVE TXTT FILES ANYMORE 

for /f %%i in ('dir *.txtt /s /b') do rename "%%i" *.sql

pause

::GET FROM USER PACKAGE NAME

set /p pkgName=TYPE THE PACKAGE NAME WITH NO SPACES AND HIT ENTER :

:: set the nicetime var

call GetNiceTime.cmd

::delete the previoues zip file 

del /q zipFile.zip

:: add the files into a package

zip -r zipFile *

::debug echo nicetime is %niceTime%

::debug pause

::debug echo rename "%cd%\zipFile.zip" %niceTime%.zip.txt

::debug pause

:: make it a txt file for easier e-mail deleting 

rename "%cd%\zipFile.zip" "%pkgName%_%niceTime%.zip.txt"

google docs upload "%niceTime%.zip.txt" 1>log.log 2>error.log

pause

::debug pause

END ================== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\ZipMe.cmd

. 

START ===== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\log\error.log

Changed database context to 'master'.

Killing active connections to the "POC_DEV" database

Restoring "POC_DEV" database from "D:\Data\Backups\POC_FB_20100331.bak" with 

data file "POC" located at "D:\Data\POC_DEV.mdf"

log file "POC_log" located at "D:\Data\POC_DEV_1.ldf"

Processed 45352 pages for database 'POC_DEV', file 'POC' on file 1.

Processed 1 pages for database 'POC_DEV', file 'POC_log' on file 1.

RESTORE DATABASE successfully processed 45353 pages in 19.995 seconds (17.720 MB

/sec).

END ================== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\log\error.log

. 

START ===== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\log\install.log

4096:1:47:47.00:21.28 

4096:1:24961:24961.00:0.04 

4096:1:1:1.00:1000.00 

END ================== D:\cas\Sql_dev\sql_dev.0.4.1_20100815_145522\RollBack\log\install.log