Sunday, January 19, 2014

Von Franz Video

Posted by at No comments:

Script to Detach an Re-attach a SQL Server Database

http://scalabilityplus.blogspot.com/

 

 

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

Posted by at No comments:

Batch file to 





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
Posted by at No comments:
Subscribe to: Posts (Atom)