Tonight, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that executes a transaction log backup on all user databases and save the results to a directory of my choosing. One of the most important job functions of a DBA is to have a recent backup chain in which a point in time restore can be performed. A backup chain consists of the most recent FULL…
Tag: BACKUP DATABASE
Backup Database Task – Part 1
SQL Server Data Tools (SSDT) is the newest release of the Visual Studio (VS) development environment for the SQL Server projects. I suggest that you use the 2010 VS Shell since the 2012 VS has some GUI color issues that are reminders of terminal screens in the early 1980’s. I hope they listen to developer feedback and fix this issue in a future release. I will be doing a series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks.…
Import & Export Data – Part 3
The BCP command line utility is the Cadillac of ETL programs for text based data files. It is REALLY FAST. It can perform both imports and exports and it can generate format files from existing objects. Today, I am going implement the same business algorithms I did earlier (Part 2) using the BCP program instead of BULK INSERT. We will be working again with the Boy Scouts of America (BSA) hypothetical database. I will be using the xp_cmdshell to execute BCP from a query window inside of SQL Server Management…
Import & Export Data Part 2
The BULK INSERT statement imports formatted data directly into a table or view of your choosing. The main advantage of this statement is that is minimally logged if the correct recovery model is choosen. Peforming a transaction log backup after each bulk insert reclaims the log space that was used. This statment has many parameters that can alter how the statement executes. Today, I am going to demonstrate the parameters that I think are most useful. We will be working again with the Boy Scouts of America (BSA) hypothetical database.…