SQL 2008 Express Editions and Service Packs

Microsoft® SQL Server® 2008 Express with Advanced Services 

Microsoft® SQL Server® 2008 Express Edition Service Pack 1 

Microsoft SQL Server2008 R2 RTM - Express with Advanced Services 

Microsoft SQL Server 2008 SP2 Express Edition Service Pack 2 

SQL Server 2005 Backward Compatibility-1

SQL Server 2005 Backward Compatibility-2


(Click on the option for download page in Microsoft Site)

Read More

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

Error in Opening database - Tables ( SQL 2000)

Re-register the following DLLs by using Regsvr32.exe, and then restart the computer:

C:\Program files\Common Files\System\ADO\Msado15.dll
C:\Program files\Common Files\System\Ole Db\Oledb32.dll
C:\Program files\Common Files\System\Ole Db\Sqloledb.dll
C:\WINNT\System32\Atl.dll

Use Regsvr32.exe to register the preceding DLLs.

For example, at the command prompt type (or) copy & paste and Enter OK

C:\>regsvr32 C:\Program Files\Common Files\System\ADO\Msado15.dll
C:\>regsvr32 C:\Program Files\Common Files\System\Ole Db\Oledb32.dll
C:\>regsvr32 C:\Program files\Common Files\System\Ole Db\Sqloledb.dll
C:\>regsvr32 C:\WINNT\System32\Atl.dll

Read More

How To Change the Default Backup Path in SQL Server

When installing SQL Server you also set up the default backup folder, but how can this folder be changed later?
In SQL Server 2005 and before there is no built-in mechanism.
In SQL Server 2005 or before, this information is saved in the registry. So you need to change the registry. It will be saved in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
In the above registry, the BackupDirectory string value is the place where the default backup directory is stored.

After changing this, you do not need to restart the SQL Server.
In SQL Server 2008 and onwards, there is an option to change this.
In SQL Server Management Studio, right click the server and select Facets,

Under the Server Facet, you can change BackupDirectory.

Read More

How to Manually Uninstall SQL Server 2005

Sometimes, you may find that, though you can't see any SQL Server 2005 instance in the programs list and when you try to install SQL Server 2005, you get a messages saying that there is already a SQL Server instance or some components such as SQL Server Management Studio installed. The reason for this is the existence of corrupted SQL Server instance and you need to remove the SQL Server instance manually.

To do this run the following command from the command prompt:


"%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove"

Uninstall the SQL Server components one at a time until all the SQL Server components are uninstalled.

Note: Add or Remove Programs also runs the ARPWrapper.exe program by using the /Remove option. However, the reference to the ARPWrapper.exe program may have been deleted. Make sure that all the services (if they exists) relevant to this SQL Server instance is stopped.

Read More

Windows Live SkyDrive is one such online storage

Windows Live SkyDrive is one such online storage space which offers 25GB of free online storage. You can use SkyDrive storage to back up your files online for free, or to share large files with others.

Security:

You can assign permissions and set folders to public or private, to help you share individual files with only people that you select.

Free use of Windows Office applications:

SkyDrive also gives you access to free Office Web Apps like Word, Excel, and PowerPoint so you can create, share and work online with others.

With SkyDrive you can also create Word, Excel, and PowerPoint documents, even without having Microsoft Office installed. You can share them with Hotmail.

Your contacts will get an email that lets them access the documents on SkyDrive – and even help you edit them.

Ease of use:

Uploading your files to SkyDrive is easy – simply drag your files from your computer and drop them into SkyDrive.

With SkyDrive, you don’t have to be in front of your PC to see your photos and documents. Access all your password-protected files from any computer or phone with Internet access.

How to use Windows Skydrive?

Use the following link to go to the windows skydrive website and register for windows live ID. In fact Windows live ID is a passport for accessing all Windows live applications.

Go to Windows Skydrive

Read More