January | 2013 | SQL Freelancer Blog

SQL Server Partitioning can be a bit confusing and can lead some companies in a different direction as far as archiving data. Writing the T-SQL code to create partition functions, partition schemes, and then altering the table to use the partition can be overwhelming for some DBA’s. SQL Server 2008 introduced a table partitioning wizard in SQL Server Management Studio that helps make this task easier. In this post, I’ll show you how to use this wizard. Let’s jump in.

Based on my experience, it seems as if SQL Server table partitioning is not commonly used and I’m not sure if it’s because of the complexity and management of partitions or if companies simply don’t have the knowledge of partitioning data. This tip will focus on the SQL Server Partitioning wizard as opposed to the ins and outs of partitioning.

To start the wizard, right click on the table you want to partition in SQL Server Management Studio and select Storage, Create Partition. In this example, I’m using AdventureWorks2012.Production.TransactionHistory.

On the Select a Partitioning Column screen as shown below you will need to select the column that you want to use to partition your table. The most common column used is a date column since most tables are being archived by date. In this example, I’ll choose TransactionDate so I’ll partition my table based on the date when the transaction was entered into this table.

Other options include:

Collocate this table to the selected partition table: Displays related data to join with the column being partitioned.

Storage Align Non Unique Indexes and Unique Indexes with an Indexed Partition Column: Aligns all indexes of the table being partitioned with the same partition scheme. If you do not select this option, you may place indexes independently of the columns they point to.

The next screen, Select a Partition Function, is where you will create the partition function. This will create a function that maps the rows of the table or index into partitions based on the values of the TransactionDate column. In this example, I’ll name the function TransactionHistoryFunction.

The next screen, Select a Partition Scheme, is where you will create the partition scheme. This will create a scheme that maps the partitions of the Production.TransactionHistory table to different filegroups. In this example, I’ll name the scheme TransactionHistoryScheme.

The next screen, Map Partitions, is where you will map your partitions.

Click here to view the rest of this post.

If you take full backups using SQL Server maintenance plans and let SQL Server use the default naming convention, you have probably noticed that usually you’ll have file name in the format of “database name + backup + date + time + .bak”. For example, a backup from the master database may look like this: “master_backup_2012_10_02_220558_8601773.bak”. It can be a challenge to script out automatic restores because the numbers on the end of the backup name constantly change. In this tip I will explain how to script out RESTORE DATABASE statements using DateTime functions.

Let’s say we have a folder full of backups like this:

Let’s say our boss wants us to restore Monday’s production backup (Alabama) every Friday afternoon to our development database (Tide). To accomplish this task, we can use the built-in SQL Server DateTime functions.

The below script will restore from the backup created on the first day of the current week. I’ve added comments to explain the code.

-- Declare variables
DECLARE @backup nvarchar(200) 
DECLARE @datebegin datetime
DECLARE @dateend datetime

-- Initalize variables
-- Set @datebegin equal to the first day of the current week
SELECT @datebegin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
-- Set @dateend equal to the second day of the current week 
SELECT @dateend = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),1) 

-- Set @backup equal to query dependent on datebegin and dateend 
SELECT TOP 1 @backup = name + '.bak' 
FROM msdb..backupset 
WHERE database_name = 'Alabama' 
AND backup_start_date BETWEEN @datebegin AND @dateend 
AND type = 'D' -- D is for full backups
ORDER BY backup_start_date ASC 

USE [master]

-- Put DB in Single_User Mode
ALTER DATABASE [Tide] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

-- Restore DB using query from @backup variable
RESTORE DATABASE [Tide] FROM  DISK = @backup WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 
GO

Below is a table of useful DateTime functions that you can use for the @datebegin and @dateend variables.

Day	SQL
Today	SELECT GETDATE()
Yesterday	SELECT DATEADD(d, -1, GETDATE())
First Day of Current Week	SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
Last Day of the Current Week	SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6)
First Day of the Current Month	SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
Last Day of the Current Month	SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))
First Day of the Current Year	SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
Last Day of the Current Year	SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

Another example may include where you need to take a backup from the first of the month of the production database and restore it weekly to the development database. In this situation you can edit the @datebegin and @dateend variables:

--Set @datebegin equal to the first day of the current month
SELECT @datebegin = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 

--Set @dateend equal to the second day of the current month
SELECT @dateend = SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),1)

Click here to view the rest of this post.

Month: January 2013

SQL Server Partition Wizard

Restore SQL Server Databases using DateTime functions