cls
Echo off
/////////////////////////////////////////////////////////////////////////////
//
//
// Remotely detach and re-attach a database.
// Restore could also be used, but detach is quicker.
// Saves the detached file with a time stamp
// Copies a fresh DB from a location on the DB server
// and reattaches it.
//
//
// Requires that a use be on the remote SQL machine, dbDropper, for instance.
//
//
//
//
//
// V-charm@microsoft.com 2/20/2013 for Microsoft DMS
//
//
/////////////////////////////////////////////////////////////////////////////
rem : Note the remote SQL server must have the SQL server running under the admin or other account.
Rem : that account must have privilages to read from the disk.
Rem: Otherwise you can detach, but not attach
set HomeDir=c:\Tools\SQL
set PathToBackups=backups\WithDSLLayer
set PathToSQLData=Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
set ServerUnderTest=10.125.252.90
set CleanDbSuffix=_AttachMe
set DBToReAttach=MicrosoftDynamicsAX
REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET theDate=%%D-%%B-%%C
FOR /F "tokens=1-4 delims=:." %%a in ("%time%") do set cur_h=%%a&set /a cur_m=100%%b %% 100&set /a cur_s=100%%c %% 100&set /a cur_ms=100%%d %% 100
set theTime=%cur_h%:%cur_m%:%cur_s%.%cur_ms%
set theTimeFN=%cur_h%-%cur_m%-%cur_s%.%cur_ms%
echo the time is: %theTime%
echo The Date and Time are: %theDate%_%theTime%
echo the date and Time for a filename= %theTimeFN%
md %theDate%_%theTimeFN%
net use R: /delete /y
net use R: \\%ServerUnderTest%\C$
Rem Stop the AOS service
call safeServiceStop.bat 10.125.252.90 AOS60$01 C:\temp
Echo Inserting Tracer Record
Rem: Insert intoAXDB
SqlCmd -S 10.125.252.90 -U dbDropper -P Password.1 -d %DBToReAttach% -Q "INSERT INTO [ACCOUNTANT_BR] ([CPFNUM_BR], [CRCNUM_BR], [RECID]) Values ('97','xx','88');"
SqlCmd -S 10.125.252.90 -U dbDropper -P Password.1 -d %DBToReAttach% -Q "SELECT TOP 1 [CPFNUM_BR], [CRCNUM_BR] FROM [ACCOUNTANT_BR] WHERE [RECID] ='88'"
Rem // don't need these right now.
Rem: Insert into BaseLineDB
rem SqlCmd -S 10.125.252.90 -U dbDropper -P Password.1 -d %DBToReAttach% -Q "SELECT TOP 1 [Id],[Name] FROM [dbo].[Layer] WHERE [Name] ='xx'"
rem SqlCmd -S 10.125.252.90 -U dbDropper -P Password.1 -d %DBToReAttach% -Q "INSERT INTO [Layer] ([ID], [Name]) Values ('97','xx');"
Echo. Kick users off SQL by restarting services ----------------------------------------------
call safeServiceStart.bat 10.125.252.90 MSSQLServer C:\temp
sleep 2
Echo.
Echo. Detaching Database Remotely ----------------------------------------------------------
SqlCmd -S 10.125.252.90 -U dbDropper -P Password.1 -d master -Q "sp_detach_db '%DBToReAttach%'"
echo.
echo Sleeping 10 seconds.
sleep 10
Echo.
Rem: Copy Fresh copy of .MDF file to the SQL Data directory.
copy R:\%pathToBackups%\%DBToReAttach%.mdf_attachMe "R:\%PathToSQLData%" /y
Echo.
Rem Rename the file just load tested to a timestamped version
Echo. Renaming "R:\%PathToSQLData%\%DBToReAttach%.mdf" to "%theDate%_%theTime%_%DBToReAttach%.mdf"
rename "R:\%PathToSQLData%\%DBToReAttach%.mdf" "%theDate%_%theTimeFN%_%DBToReAttach%.mdf"
Echo.
Rem: rename the "clean" version to the Production name.
Echo. Renaming "R:\%PathToSQLData%\%DBToReAttach%.mdf%CleanDbSuffix%" to "%DBToReAttach%.mdf"
rename "R:\%PathToSQLData%\%DBToReAttach%.mdf%CleanDbSuffix%" "%DBToReAttach%.mdf"
Echo.
echo.
Rem: Delete the log file. We currently don't need this. Will be recreated on attachment.
Echo. Deleting Log file "R:\%PathToSQLData%\%DBToReAttach%_log.ldf
erase "R:\%PathToSQLData%\%DBToReAttach%_log.ldf%"
Rem ----------- Re-attachement ------------------------------
Echo. RE-Attaching Fresh Database from local %pathToBackUps% -------------------------------------------------
echo.
rem This does not allow us to pass parameter, don' use. SqlCmd -S 10.125.252.90 -U dbDropper -P Password.1 -d master -i "AttachBaseline.sql"
echo doing "CREATE DATABASE %DBToReAttach% ON (FILENAME= N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\%DBToReAttach%.MDF') FOR ATTACH;"
SqlCmd -S 10.125.252.90 -U dbDropper -P Password.1 -d master -Q "CREATE DATABASE %DBToReAttach% ON (FILENAME= N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\%DBToReAttach%.MDF') FOR ATTACH;"
Echo.
sleep 5
echo Restaring Services -------------------------------------------------
rem call safeServiceStart.bat 10.125.252.90 MSSQLServer C:\temp
rem sleep 5
Echo Querying for Test Record. Should NOT be found
Rem: AXDB
SqlCmd -S 10.125.252.90 -U dbDropper -P Password.1 -d %DBToReAttach% -Q "SELECT TOP 1 [CPFNUM_BR], [CRCNUM_BR] FROM [ACCOUNTANT_BR] WHERE [RECID] ='88'"
REm Start the AOS
call safeServiceStart.bat 10.125.252.90 AOS60$01 C:\temp
No comments:
Post a Comment