A lot of times, we have the need to execute a scheduled task to perform different tasks in our databases like backups, restore db, extract or import data. There are a lot of different tools which are really powerful but for the most of the small tasks we might use the SQL console.
A few days ago I had to perform some tasks over a database throw different servers and I decided to do it using a sqlcmd process in a batch file. These are the steps I wanted to execute:
- Copy the last backup created in server A to server B.
- Restore the backup overwriting the database in server B.
- Extract all the images from one table in an FTP server
Copy last backup
At this stage wee need to share the folder where the .bak files are generated in the production database (server A). That folder contains the las N bakups created each week but we just need to get the last one created.
In the following script lines we take a list with all the .bak files and copy the last one over the network. Until the copy process is not finished our scrip does not continue executing the following lines:
@echo off
:Variables
SET DatabaseBackupPath=\\DatabasePath\
echo %DATE% %TIME%: Starting scheduled task >> "log.txt"
echo %DATE% %TIME%: Copy prod database backup >> "log.txt"
FOR /F "delims=|" %%I IN ('DIR "%DatabaseBackupPath%\*.bak" /B /O:D') DO SET NewestFile=%%I
copy "%DatabaseBackupPath%\%NewestFile%" "E:\Production DB copy\" /z
Notice the "echo ... >> "log.txt" ", which is saving all the actions we take in a text file file to get a summary of the tasks performed. In every echo command we are using the %DATE%%TIME% which gets the current date and time from the server and print it in the log.txt file.
Restore backup
Now the backup is already in our test server (server B) and we are free to perform a restoration task using the database backup just copied. The following lines are consecutive to the previous example and they will replace the database with the bakup from production:
echo %DATE% %TIME%: Restore Sys Database >> "log.txt" :sqlcmd -S DbServerName -U UserName -P Password -o "DBlog.txt" -d master -Q ^ :"RESTORE DATABASE DbName ^ :FROM DISK = N'E:\Production DB copy\%NewestFile%' WITH REPLACE"
Notice about the "WITH REPLACE" parameter which allow us to overwrite the database if there is already created.
Extract all the images
Finally to extract all the images (or any other column you need to extract). First, I used SQL Server Data Tools, which it's a really powerful tool provided from Microsoft to handle all the extra stuff you want to do with your databases. I followed the tutorial from Daniel Calbimonte which comes really good explained with images of all the steps:
Once your package is properly created you just need to create a new job in your SQL Server Agent to execute it every time you need to update the images. I faced some problems when I did this step, because the password to connect to the database is saved inside of the package and you need a way to secure it. In the following tutorial you can see an explanation of how to solve it:
http://zarez.net/?p=155
Finally, you'll just need to create a call to sql to execute the job throw our great bash console application:
http://zarez.net/?p=155
Finally, you'll just need to create a call to sql to execute the job throw our great bash console application:
echo %DATE% %TIME%: Execute 'Export images delta' job >> "log.txt" osql -S "SQL11CTMD" -U UserName -P UserPassword -d master -Q"exec msdb.dbo.sp_start_job 'Export images delta'"
--
References
- Daniel Calbimonte / http://www.mssqltips.com
- Zarez / http://zarez.net/?p=155
0 comments:
Post a Comment