Procedure for Schedule Backup in SQL 2008 Express by using batch file

You need to take the following 3 steps to backup your SQL Server databases using Windows Task Scheduler

Step 1: Using SQL Server Management Studio express or Sqlcmd create the following stored procedure in your master database: 

(Query is given in the bottom of this post ) 

Step 2: In a text editor create a batch file that is named sqlbackup.bat and then copy the text from one of the following examples depending on your scenario into that file:

Example1: Full Backups of ALL databases in local named instance of SQLEXPRESS using Windows Authentication

// sqlbackup.bat

sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'" 

 Example2: Differential Backups of ALL databases in local named instance of SQLEXPRESS using SQLLogin and its password

// sqlbackup.bat

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation ='D:\SQLBackups', @BackupType=’D’"

 Note: The SQLLogin should at least have Backup Operator role at SQL Server.

Example3: Log Backups of ALL databases in local named instance of SQLEXPRESS using Windows Authentication

// sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

Example4: Full Backups of database USERDB in local named instance of SQLEXPRESS using Windows Authentication

// sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

Similarly you can take differential and log backup of USERDB by passing in ‘D’ and ‘L’ respectively for the @backupType parameter.

Step 3: Schedule a job using Windows Task Scheduler to execute the batch file created in Step 2. To do this use the following procedure:

1.     On the computer that is running Microsoft SQL Server Express, click Start , point to All Programs , point to Accessories , point to System Tools , and then click Scheduled Tasks. 

2.     Double-click Add Scheduled Task . 

3.     In the Scheduled Task Wizard, click Next . 

4.     Click Browse , click the sqlbackup.bat file that you created in step 2, and then click Open . 

5.     Type SQLBACKUP for the name of the task, and then click Daily . Then, click Next . 

6.     Specify information for a schedule to run the Task. We recommend that you run this at least once every day. Then, click Next .

7.     In the Enter the user name field and in the Enter the password field, type a username and a password. Then, click Next .  Please note that this user should at least be assigned the BackupOperator role at SQL Server level if you are using one of the batch files in example 1, 3 and 4.

8.     Click Finish . 

9.     Execute the scheduled task at least once to ensure that the backup is created successfully.

Note: The folder for SQLCMD executable is generally in the Path variables for the server after SQL Server is installed, but in cases where the Path variable does not list this folder, you can find it under <Install location>\90\Tools\Binn (For example: C:\Program Files\Microsoft SQL Server\90\Tools\Binn).

 Please note the following when using the procedure documented in this article:

• Windows Task Scheduler service must be running at the time when the job is scheduled to run. It is recommended that you set the startup type for this service as Automatic. This ensures that the service will be running even on a restart.
• There should be enough space on the drive where the backups are being written to. It is recommended that you clean the old files in the backup folder on a regular basis to ensure that you do not run out of disk space. The script does not contain the logic to cleanup old files. 

 ----------------------------------------------------------------------------------

Query :

// Copyright © Microsoft Corporation.  All Rights Reserved.

// This code released under the terms of the

// Microsoft Public License (MS-PL,http://opensource.org/licenses/ms-pl.html.)

USE [master] 

GO 

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

-- ============================================= 

-- Author: Microsoft 

-- Create date: 2010-02-06

-- Description: Backup Databases for SQLExpress

-- Parameter1: databaseName 

-- Parameter2: backupType F=full, D=differential, L=log

-- Parameter3: backup file location

-- =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]  

            @databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

       SET NOCOUNT ON; 

           

            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )

           

             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME)

            SELECT Name FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

            OR @DatabaseName IS NULL

            ORDER BY Name

           

            -- Filter out databases which do not need to backed up

            IF @backupType='F'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

            -- Declare variables

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

            -- Loop through the databases one by one

            SELECT @Loop = min(ID) FROM @DBs

      WHILE @Loop IS NOT NULL

      BEGIN

-- Database Names have to be in [dbname] formate since some have - or _ in their name

      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format

      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

-- Create backup filename in path\filename.extension format for full,diff and log backups

      IF @backupType = 'F'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'D'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'L'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

      IF @backupType = 'D'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime

      IF @backupType = 'L'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

       IF @backupType = 'F' 

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

                  END

       IF @backupType = 'D'

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

       IF @backupType = 'L' 

                  BEGIN

               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

-- Execute the generated SQL command

       EXEC(@sqlCommand)

-- Goto the next database

SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

Read More

How to Make EXE Files Using Notepad

    An "exe" file is an executable file or a program file. To create an "exe" file in Notepad, you must understand and know a programming language such as C++. With Notepad, users are able create an the file with software already found on their computer.

Instructions

1. Open Notepad on your computer. Notepad is located in the "Accessories" folder of the computer's "Program Files".

2. Hold down the "Alt" key and use the number pad to type in every 3-digit combinations from 000 to 999. After each 3-digit combination, release the "Alt" key.

3. Make notes on what keys produce what symbols. You can make a list of all of the 3-digit combinations or just a list of the ones you need. In Notepad, these combinations are the only way to get certain symbols.

4. Type the "exe" file for the program you want to create from the programming language into Notepad. Type the file in C++ programming language. Use the "Alt" and the 3-digit combinations to create symbols that do not appear on the keyboard but that you need in the program.

5. Save the file as a text file until you complete the "exe" program. Be aware that it may take a long time to type every line of code in the "exe" program.

6. Click on "File" and then choose "Save." In the extension drop-down menu, choose "exe." Name the file and click the "Save" button. Locate the file you saved and double-click it to run the "exe" file.

Read More

How to Protect your Data ?

     It's a human tendency to lock your home whenever you go out; it makes you feel protected. But what about data what you have saved on your computer? Do you lock it when you transfer it from your computer? Given the threats lurking online - hackers, phishers, identity thieves and the like - you would do well to encrypt your data of you are using the internet to share crucial information with others. This ensures privacy and prevents it from getting hacked. Even if someone succeeds in stealing an encrypted file, he or she will be unable to misuse it unless the key to decode the content of that file is also stolen alongside. Then there are the monetary transactions online and the personal information shared on the Internet. To protect users, the file encryption process is a crucial part of an efficient computer security system. File encryption or data encryption is a process of transforming your confidential information into an unreadable format which cannot be accessed without the correct password or key. Though there are third-party encryption software available like TrueCrypt in the market, you can also use the lesser known, built-in feature of Windows to secure your files. Here to go about this.

How to Encrypt a File? 

1. Right click on a file you want to encrypt. Click the 'Properties' option at the bottom of the context menu.

2. Click the 'Advanced' button located under 'General' tab in the 'Properties' dialog box.

3. Click the 'Encrypt Contents to Secure Data' option and click 'OK'.

4. If the file is in a folder you will be prompted with an 'Encryption Warning' message. If this is the case, click the 'Encrypt the File Only' option and click 'OK'.

Tips

• You can use EFS (Encrypting File System) to encrypt files on Windows XP, Windows Vista, Windows 7 Professional, Windows 7 Ultimate, and Windows 7 Enterprise. The filesystem should be NTFS; it won't work on FAT32.

•  Select the certificate of the user whom you want to add (to the access list of the encrypted file), and then click OK. You can do this by clicking Properties -> Advanced -> EFS settings.

• To decrypt a folder, use the same process, only disable the document property encryption by clearing Encrypt Document Properties check box.

• Rename your file with a completely different filename and a different file extension. For example, change filename.doc into general.jpg or filename.xls into birthday.mp3; If file extensions (.doc, .png, .mp3, etc.) are not visible go to: Tools -> Folder Options -> View, and uncheck hide extensions for known file types. Some computer savvy individuals may be able to crack the file.

• You can encrypt your files using strong AES-256 encryption feature of 7-zip, which is a free file compression utility which you might be using already to compress/zip multiple files into one smaller file to be send across internet.

After encrypting the files, you should back up your certificates because there is no other way to recover encrypted files with a corrupted or missing certificate. It is critical that you back up the certificates and store them in a secure location. Here's how to back up your certificate : 

1. Open Microsoft Internet Explorer. On the Tools menu, click 'Internet Options'.

2. On the 'Content' tab, in the Certificates section, click 'Certificates'. Click the 'Personal' tab.

3. Select one certificate at a time until the 'Certificate Intended Purposes' field shows 'Encrypting File System'.

4. Click 'Export' to start the Certificate Export Wizard, and then click 'Next'. Click 'Enable Strong Protection', then click 'Next'. Type in your password.

5. Specify the path where you want to save the key, and then click 'Next'. You can save the key to another location on the hard disk, or on a removable media.

Read More