Dec 26

Backup Database Stored Procedure

The following script will handle Full backups, Differential backups, and transaction log backups.

/*   ____  _____ _____ 
    |    \|     |     |
    |  |  |   --| | | |
    |____/|_____|_|_|_|
      Author: RJ Hatch
 Create date: 2013-12-26
 Description: Backup Databases
  Parameters:
    @DatabaseName   - Name of the database
    @BackupType     - F=full, D=differential, L=log
    @BackupLocation - backup directory location
*/ 
CREATE PROCEDURE [dbo].[sp_BackupDatabase]  
    @DatabaseName NVARCHAR(100),
    @BackupType CHAR(1),
    @BackupLocation NVARCHAR(200) 
AS
BEGIN
    SET NOCOUNT ON; 
           
    -- Declare variables
    DECLARE @BackupName NVARCHAR(100),
            @BackupFile NVARCHAR(100),
            @SqlCommand NVARCHAR(1000),
            @Date NVARCHAR(8),
            @Time NVARCHAR(6)

    -- Provide the backup a name for storing in the media
    SELECT  @Date = REPLACE(CONVERT(VARCHAR, GETDATE(), 102), '.', ''), --YYYYMMDD
            @Time = REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), --HHMMSS
            @BackupFile = @backupLocation + REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') +
                CASE
                    WHEN @BackupType='F' THEN '_FULL_' + @Date + '_' + @Time
                    WHEN @BackupType='D' THEN '_DIFF_' + @Date + '_' + @Time
                    ELSE '_LOG_' + @Date + '_' + @Time
                END + 
                CASE
                    WHEN @BackupType='F' THEN '.BAK'
                    WHEN @BackupType='D' THEN '.BAK'
                    ELSE '.TRN'
                END, --[c:\directory\][Database]_[LongType]_[Date][_Time].[Ext]
            @BackupName = REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') + ' ' +
                CASE
                    WHEN @BackupType='F' THEN 'full'
                    WHEN @BackupType='D' THEN 'differential'
                    ELSE 'log'
                END + ' backup for ' + @Date + '_' + @Time, -- [Database] [BackupType] backup for [Date]_[Time]
            @SQLCommand = 'BACKUP ' +
                CASE
                    WHEN @BackupType='F' THEN 'DATABASE'
                    WHEN @BackupType='D' THEN 'DATABASE'
                    ELSE 'LOG'
                END + ' ' +
                @DatabaseName + ' TO DISK = ''' + @BackupFile + ''' WITH ' +
                CASE
                    WHEN @BackupType='D' THEN 'DIFFERENTIAL, '
                    ELSE ''
                END + 'INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'

    -- Execute the generated SQL command
    EXEC(@sqlCommand)
END

Run it as the following

--Full Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'F', 'c:\temp\'

--Differential Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'D', 'c:\temp\'

--Transaction Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'L', 'c:\temp\'
Dec 19

SQL Server Tally Table

The following Tally Table will help in future posts.

The Tally table is simply a table with an ID column, that will help in reducing most loops; a personal preference of mine.

CREATE TABLE dbo.Tally (
    ID INT NOT NULL
)
GO
ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally
    PRIMARY KEY CLUSTERED ( ID )
GO

--2048 * 8 = 16384 Results
INSERT INTO dbo.Tally
    SELECT  a.number * 2048 + b.number + 1 ID
      FROM  [master].dbo.spt_values a,
            [master].dbo.spt_values b
    WHERE   a.[type] = 'p'
      AND   b.[type] = 'p'
      AND   b.number < 2048
      AND   a.number < 8
    ORDER BY a.number, b.number
GO

If you want more results, you will need to mess with the “a.number < 8" line in the where clause. Each increase will result in an extra 2048 entries. I find that 16k+ suits my needs just fine.

Dec 05

Spam from Software Outsourcing Company BairesDev

Below is an email I got from the day job. I haven’t done business with this person or company, and my LinkedIn profile (nor any public profile) has my work email address. So, it is more than likely that this person saw my name, utilized a common email form, and sent off the email.

Since I haven’t agreed to the confidentiality agreement at the bottom of his email, I do not respect, nor need to follow it.

I sincerely hope that they clean up their marketing campaign, as it is NOT appreciated by this developer.

Ignacio, please enjoy the free publicity that I am giving your company. After all, bad press is still press, right?


Dear Rj,

First of all I’d like to introduce myself. My name is Ignacio De Marco and I am the Chief Business Officer at BairesDev. I have found your profile on LinkedIn and, given your role, thought that it might be a good idea to contact you and offer some services that could be of interest to you or your company.

As a brief introduction, our United States & Argentina-based development centers offer a variety of services:

  • Software Outsourcing
  • Software Development
  • Test Automation
  • Remote Support & Cloud Computing

All of these services can be delivered on or off-site, depending on your needs. With more than 200 bilingual employees, we provide services for companies worldwide, from major global companies like Google (GOOG), Netgear (NTGR) or Demand Media (DMD) in the US and Kapsch in Europe (KTCG) to small companies focused on specific markets. More details about our services can be found on http://www.bairesdev.com.

If you are interested in outsourcing any services, I would be very happy to organize an initial call to discuss potential opportunities with you.

Do you think any of the services above can be useful to you or your colleagues?

Kind regards,

Ignacio De Marco

Chief Business Officer

US: +1 408 600 1331

AR: +54 11 5353 9840

Cel: +54 911 3809 1901

Email: idemarco@bairesdev.com

Skype: ignaciodemarco

LinkedIn: http://www.linkedin.com/in/idemarco

BairesDev's Spamming Website Center

This Email (including any attachments) is strictly confidential and intended solely for the addressee. If you are not the intended addressee, you must not disclose, copy, or take any action on account of this message. If you have received this message in error, please notify the sender immediately and delete all copies.

Este correo electrónico (incluyendo cualquier archivo adjunto) es estrictamente confidencial y está destinado exclusivamente a su destinatario original. Si usted no es el destinatario, no debe revelar, copiar, o tomar cualquier otra acción sobre este mensaje. Si usted ha recibido este mensaje por error, por favor notifique al remitente inmediatamente y elimine todas las copias.

iso yeah right

BairesDev is a quality endorsed business to the international quality standard ISO9001:2008. This standard ensures that we deliver what we promise, when we promise it, every time.