Update Category name from the name in HQ , with Script

SELECT A.[ID]
      ,A.[Name]
      ,A.[Code],
      'Update Category Set Name = ' + B.Name +'  WHERE Code = ' + A.Code +'' As script

  FROM [AS].[dbo].[Category] AS A, [HQSTOCK].[dbo].[Category] AS B WHERE A.[Code] = B.[Code]
  AND A.Name != B.Name
GO

87	Ready Framed Batik (M Hussin)	01005028	Update Category Set Name = Ready Framed Batik (Alim)  WHERE Code = 01005028
88	Ready Framed Batik (Zabid)	01005029	Update Category Set Name = Ready Framed Batik (Chin)  WHERE Code = 01005029
93	01001029	01001029	Update Category Set Name = Batik Paintings (Topo)  WHERE Code = 01001029
94	01001030	01001030	Update Category Set Name = Batik Paintings (Al-Kusuma)  WHERE Code = 01001030
95	01001031	01001031	Update Category Set Name = Batik Paintings (Haryati)  WHERE Code = 01001031
96	01001032	01001032	Update Category Set Name = Batik Paintings (Andre)  WHERE Code = 01001032
97	01005030	01005030	Update Category Set Name = Ready Framed Batik (Acholik)  WHERE Code = 01005030
98	01001033	01001033	Update Category Set Name = Batik Paintings (Chin)  WHERE Code = 01001033
SELECT C.[ID]
      ,C.[Name]
      ,C.[Code],
      'Update Category Set Name = ''' + C.Name +'''  WHERE Code = ' + C.Code +'' As script

  FROM [HQSTOCK].[dbo].[Category] AS C

Power Pivot Function

We can write functions in Pivot table to put condition on the data taken from DB.

Types of DAX Functions

DAX provides functions that have the same functionality and names as the Excel functions that you might already be familiar with. However, the functions have been modified to use DAX data types and to work with tables and columns. In addition, DAX provides many specialized functions for specific purposes, such as lookups based on relationships, the ability to iterate over a table to perform recursive calculations, and calculations utilizing time intelligence.

Read more about the following types of DAX functions:

LinkedIn IPO now

 

What is LikedIn, I see them long ago but never really find why is it there.  From wiki,

LinkedIn (pronounced /ˌlɪŋkt.ˈɪn/) is a business-oriented social networking site. Founded in December 2002 and launched in May 2003, it is mainly used for professional networking. As of 22 March 2011, LinkedIn reports more than 100 million registered users, spanning more than 200 countries and territories worldwide.The site is available in English, French, German, Italian, Portuguese and Spanish. Quantcast reports Linkedin has 21.4 million monthly unique U.S. visitors and 47.6 million globally.

That might makes sense why is available in the internet world. There is something so surprise happen yesterday ( 19 May 2011). It first initial public offering (IPO) was a huge success.  For just one day, the IPO has nominated millionaires and one billionaire to the family of oh-so-fast-getting-rich gang.

courtesy of Yahoo finace

Buyers crowded the floor of the New York Stock Exchange, and financial news networks flashed LinkedIn’s stock price urgently all day. By the closing bell, the company had a market value of $9 billion, the highest for any Internet company since Google had its initial public offering seven years ago. Millionaires and even one billionaire were made, at least on paper.

The stock, issued at $45, went as high as $122.70 just before noon and closed at $94.25 on a trading volume of 30 million shares. All this for a company that skeptics say amounts to an online Rolodex, a place on the Internet for professionals to post resumes and connect with one another and potential employers.

It was enough to remind some people on Wall Street of the heady late 1990s and the debuts of companies like Netscape Communications — and, more infamously, long-forgotten names like Pets.com and Webvan. Investors wondered whether LinkedIn will be a precursor to another financial frenzy in Silicon Valley.

Concatinate String in SQL 2008

SELECT [BarcodeNumber] + ';' AS BarcodeNumber

        ,([Description]+ ';') AS Description
        ,CAST(c.[Code] AS nvarchar) + ';' As CategoryID
        ,CAST(d.code AS nvarchar)  As DepartmentID

  FROM [HQSTOCK].[dbo].[Item] AS i, [HQSTOCK].[dbo].[Category] AS c , [HQSTOCK].[dbo].[Department] AS d
  WHERE i.CategoryID = c.ID And i.DepartmentID = d.ID
GO

update description with substring.

UPDATE [ECOWARNA].[dbo].[Item]
 --  SET [Description] = [Description]+ ' '+ CONVERT(VARCHAR(4),[BarcodeNumber], 120)
   --SET [Description] = 'Tongkat Ali Kuning Root'
    SET [Description] = [Description]+ ' '+SUBSTRING([BarcodeNumber],10,4)
    
 WHERE [Description] like '%Tongkat Ali Kuning Root%'
GO

Auto Back up and Auto Restore in Remote server

In Sql server in my case 2008, these are the steps you need to do:

        1. Creat the auto back up schedule:

                 In MS SQL server studio,  go to maintenence – > SQL server Maintenance Plan wizard, and fill in the form of the place where you store your back up file.

         2. Write the stored procedure to send those file to any FTP host server.

        3. Exe the stored procedure

        4. Retrive the

Enable sending file through FTP in SQL server

FIrst you need to run this script first, then you can execute your stored procedure. Goodl uck

USE [YOURTESTDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
Go

EXEC sp_configure ‘show advanced options’, 1
GO

RECONFIGURE
GO

EXEC sp_configure’xp_cmdshell’, 1
Go
EXEC sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE

GO

Auto generate SQL Server restore script from backup files in a directory

Greg Robidoux

Problem
One of the ongoing challenges of a DBA is to backup and restore databases.  Backups are done on an automated schedule, but restores can take on many different versions, you may need to restore a production database, restore a development or test database or just create another copy of the database somewhere else.  There are several ways of automating the restore process and creating a script, but this approach shows a way this can be done by just reading the contents of a directory for the backup files that exist.

Solution
The following is one simple approach of reading the contents of a directory and creating the restore commands that need to be issued to restore the database.  This script will work for full, differential and transaction log backups.

Before we get started the script below assumes the following:

  1. The restored database will have the same name as the backed up database
  2. The restored database will be restored in the same location as the backed up database
  3. The files have the following naming format
    • dbName_YYYYMMDDHHMM.xxx
  4. File extensions are as follows
    • Full backup – BAK
    • Differential backup – DIF
    • Transaction log backup – TRN
  5. XP_CMDSHELL is enabled
  6. There are no missing transaction logs that may break the restore chain

So let’s say we are creating our backups on the following schedule:

  • Full backups at midnight
  • Differential backups every 3 hours starting at 3:15am
  • Log backups every 30 minutes starting at 1am

At 9am we would have the following backup files created for September 10, 2008 for the “Customer” database following the rules above.

  • Customer_200809100000.BAK
  • Customer_200809100100.TRN
  • Customer_200809100130.TRN
  • Customer_200809100200.TRN
  • Customer_200809100230.TRN
  • Customer_200809100300.TRN
  • Customer_200809100315.DIF
  • Customer_200809100330.TRN
  • Customer_200809100400.TRN
  • Customer_200809100430.TRN
  • Customer_200809100500.TRN
  • Customer_200809100530.TRN
  • Customer_200809100600.TRN
  • Customer_200809100615.DIF
  • Customer_200809100630.TRN
  • Customer_200809100700.TRN
  • Customer_200809100730.TRN
  • Customer_200809100800.TRN
  • Customer_200809100830.TRN
  • Customer_200809100900.TRN

If we wanted to do a restore of the latest Full, Differential and Transaction Log backups to 9am we would need to restore the following files:

  • Customer_200809100000.BAK
  • Customer_200809100615.DIF
  • Customer_200809100630.TRN
  • Customer_200809100700.TRN
  • Customer_200809100730.TRN
  • Customer_200809100800.TRN
  • Customer_200809100830.TRN
  • Customer_200809100900.TRN

The script below will read through the directory and create the restore script for us.  The only two parameters that would need to change are the @dbName and the @backupPath.

GO
SET NOCOUNT ON– 1 – Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

— 2 – Initialize variables
SET @dbName = ‘Customer’
SET @backupPath = ‘D:\SQLBackups\’

— 3 – get list of files
SET @cmd = ‘DIR /b ‘ + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

— 4 – Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE ‘%.BAK’
AND backupFile LIKE @dbName + ‘%’

SET @cmd = ‘RESTORE DATABASE ‘ + @dbName + ‘ FROM DISK = ”’
+ @backupPath + @lastFullBackup + ”’ WITH NORECOVERY, REPLACE’
PRINT @cmd

— 4 – Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE ‘%.DIF’
AND backupFile LIKE @dbName + ‘%’
AND backupFile > @lastFullBackup

— check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = ‘RESTORE DATABASE ‘ + @dbName + ‘ FROM DISK = ”’
+ @backupPath + @lastDiffBackup + ”’ WITH NORECOVERY’
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END

— 5 – check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE ‘%.TRN’
AND backupFile LIKE @dbName + ‘%’
AND backupFile > @lastFullBackup

OPEN backupFiles

— Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ‘RESTORE LOG ‘ + @dbName + ‘ FROM DISK = ”’
+ @backupPath + @backupFile + ”’ WITH NORECOVERY’
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

— 6 – put database in a useable state
SET @cmd = ‘RESTORE DATABASE ‘ + @dbName + ‘ WITH RECOVERY’
PRINT @cmd

If you run the above code in a query window, assuming the listed files above existed, you will get the following output.  At this point you can copy and paste this code into another query window and run the query to do the actual restore.

As you can see it does a Full restore, the latest Differential restore and all Transaction Logs after that.  The script also does a WITH RECOVERY at the end to put the database in a useable state.

Next Steps

  • This is a pretty straight forward and simple approach.  As mentioned above it restores using the same name and also restores to the same file location.  Try making some modifications to restore it to another database name as well as restoring the files to a different location by incorporating the RESTORE FILELISTONLY command
  • This script will work on any server where the files exists and you can run a SQL Server query.  So you can copy the files from one server to another, run this script and then have your restore script ready to go.
  • Check out these other restore scripts:

Setup Automatic Database Backups in SQL Server 2008

Courtesy of Got Know how

Are you backing up your SQL Server 2008 databases daily?  You should be, especially if you don’t want to lose any of your precious data that you’re storing.  It’s incredibly easy to setup a maintenance plan in SQL Server 2008 to automatically back up one or more databases. To get started backing up your databases using SQL Server Management Studio follow the steps below.

Here’s how to setup automatic daily backups for SQL Server 2008 databases:

  1. Open Microsoft SQL Server Management Studio.
  2. Expand the database server.
  3. Expand the Management folder.
  4. Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.
  5. Click ‘Next’ button, then name your Maintenance Plan and give description.  Select the radio button that says “Single schedule for the entire plan or no schedule”.
  6. Under Schedule,  click on the ‘Change’ button.  This brings up the Job Schedule Properties form. In theFrequency section, change Occurs to ‘Daily’, and then click ‘OK.
  7. Click ‘Next’ button, then check the box next to “Back Up Database (Full)”, then click ‘Next’.
  8. Click ‘Next’ button again, then select one or more of your Databases to be backed up using the Database(s) drop down box. Make any additional settings and then click ‘Next’.
  9. On the Select Report Options form, click the ‘Next’ button once again.
  10. Then click the ‘Finish’ button to complete the wizard.
  11. The Maintenance Plan Wizard will run and should complete successfully.  Click the ‘Close’ button.
  12. You should now see the database backup maintenance plan you just created underneath theMaintenance Plans folder in SQL Server Management Studio.