SQL Server 2008 R2 Maintenance Plan

This maintenance plans to clean up the database, make backups (data and log), and do some performance optimization.

Weekly Database Maintenance Plans

1.Start the Microsoft SQL Server Management Studio and log in as the ‘sa’ user. Open the “Management” -> “Maintenance Plans” -> Select “Maintenance Plan Wizard.”

2.On the “Select Plan Properties” page give it the name “Weekly Maintenance Plan”. Select “Single schedule for the entire plan or no schedule” and click the “Change” button to set schedule. Click Next.

SQL Server - BLOB Import and Export

SQL scripts to insert File to BLOB field and export BLOB to File

1. Using SQL server 2008, you can saved images / files to BLOB binaries and retrieve back them to file system.
2. Import
   Test table structure:
   
   CREATE TABLE [dbo].[TestBlob](
   [tbId] [int] IDENTITY(1,1) NOT NULL,
   [tbName] [varchar](50) NULL,
   [tbDesc] [varchar](100) NULL,
   [tbBin] [varbinary](max) NULL
   ) ON [PRIMARY]
3. Insert file to BLOB test table is fairly easy. Open Microsoft SQL server management studio, run the below script, script is inserting one pdf, doc, image and exe fil.
   
   Insert TestBlob(tbName, tbDesc, tbBin) Select '81.pdf','PDF file', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\81.pdf', Single_Blob) as tb
   
   Insert TestBlob(tbName, tbDesc, tbBin) Select 'mountain.jpg','Image jpeg', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\mountain.jpg', Single_Blob) as tb
   
   Insert TestBlob(tbName, tbDesc, tbBin) Select 'Questionnaire.docx','Doc Question', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\Questionnaire.docx', Single_Blob) as tb
   
   Insert TestBlob(tbName, tbDesc, tbBin) Select 'txpeng542.exe','Texpad Exe', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\txpeng542.exe', Single_Blob) as tb