SQL Server Community Blogs

SQL Server Statistics

robcarrol — Fri, 16 May 2008 07:00:00 GMT

b Cross Posted from blogs.technet.com/rob...(read more)

SQL Server and Windows Memory

robcarrol — Thu, 15 May 2008 14:02:00 GMT

SQL Server 2000 Enterprise Edition introduced support for the use of Windows 2000 Address Windowing Extensions (AWE) to address 8GB of memory on Windows 2000 Advanced Server and 32GB of memory on Windows 2000 Datacentre. With AWE, SQL Server can reserve memory not in use by other applications and the OS. Each instance must statically allocate the memory it needs. AWE memory can only be used for the data cache and not for executables, drivers, DLL's, etc.

Cross Posted from blogs.technet.com/rob

Windows Memory (PAE/AWE/3GB)

robcarrol — Thu, 15 May 2008 12:20:00 GMT

SQL Server 2000 Enterrise Edition introduced support for the use of Windows

Cross Posted from blogs.technet.com/rob

SQL Server multiple instances and resource utilization - best practices

ssqa.net — Thu, 15 May 2008 09:48:00 GMT

A newbie or FAQ within Forums and Newsgroups that having multiple instances and how best to set the resources on the server such as memory, CPU etc. Should you configure each of the instances to use specific portions of the available Operating System resources then best to leave the default settings, for instance dynamic memory settings on SQL Server. As per the configuration of SQL Server you can easily mix versions of SQL Server (both 2000 and 2005) on the same machine running Windows server, such...(read more)

Simulating undocumented Procedures

Madhivanan — Tue, 13 May 2008 09:59:00 GMT

Sometimes you may want to run a query against each database

Suppose you want to find all dabase names where particular table exists. You can use undocumented procedure

EXEC sp_msforeachdb 'SELECT table_catalog FROM ?.INFORMATION_SCHEMA.TABLES where table_name=''your_table'''

Because they are undocmented, you cant always rely on them.

Alternatively you can use the following methods

1 WHILE LOOP

declare @dbname varchar(100), @database_id int,@table_name varchar(100)
select @dbname='', @database_id=1,@table_name='your_table'

while exists(Select * from sys.databases where database_id>@database_id)
Begin
        select @dbname=name,@database_id=database_id from sys.databases where database_id=@database_id
        EXEC('SELECT * FROM '+@dbname+'.INFORMATION_SCHEMA.TABLES where table_name='''+@table_name+'''')
        select @database_id=min(database_id) from sys.databases where database_id>@database_id
End

2 Concatenated SQL

declare @sql varchar(max), @table_name varchar(100)
select @sql='', @table_name='your_table'
select @sql=@sql+ 'SELECT table_catalog FROM '+name+'.INFORMATION_SCHEMA.TABLES where table_name='''+@table_name+'''' from sys.databases
exec(@sql)

Common Table Expressions (CTE's) - How it works; How Recursion Works; Using with Adjacency List

tonyrogerson — Sun, 11 May 2008 18:47:00 GMT

Recursion in Common Table Expressions (CTE’s), how does it work? How can I use it with the adjacency list model (parent / child columns)? In this blog entry I show how recursion works, how to use it with the adjacency list model and talk about other aspects and uses for CTE’s; I also demonstrate some of the areas where CTE’s aren’t that good and how to help remedy that performance penalty suffered. This weekend I’ve been at the Scottish Developers Conference in Glasgow – a great day, I gave my making...(read more)

Which queries are missing indexes?

leo.pasta — Thu, 08 May 2008 20:48:28 GMT

One of the things I really enjoy when doing performance tuning on 2005 (I still work on a mix of several SQL 2000 and some SQL 2005) is the sys.dm_db_missing_index* DMVs. As the query processor evaluates queries, it detects if that specific query could benefit from an index and how much it expect that index would reduce the cost (in terms of IO), exposing these information as views that we can query. I won't delve in the structure of it, but you can use my procedure sp_dba_missingindex as an...(read more)

SQL Server - Have you observed DBCC SHRINKFILE operation performance, on huge databases?

ssqa.net — Thu, 08 May 2008 08:35:00 GMT

In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly! Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases ETL processes, coming to the point by design the DBCC SHRINKFILE operation is a single-threaded operation that means you cannot define or configure the server to use multiple CPUs or a dedicated CPU. So troubleshooting the performance problems...(read more)

Plenty of FREE SQL 2008 stuff from JumpStart Event

ThePremiers — Tue, 06 May 2008 20:46:00 GMT

JumpStart 2008 is an event held in Seattle in February.

All the content delivered there is now available for everyone (after a quick registration).

http://sqlserver2008jumpstart.microsofttraining.com/content/DownloadMaterial.asp?CcpSubsiteID=69&section=Overview

Here are some examples of what's waiting for you there:

OS01 - Executive Update - Winning Business Today and Tomorrow
Play session View PowerPoint

OS02 - SQL Server 2008: The Data Platform for Software + Services
Play session   View PowerPoint

OS03 - Mission-Critical Applications
Play session   View PowerPoint

OS04 - Manageability
Play session   View PowerPoint

OS05 - Pervasive Business Intelligence
Play session   View PowerPoint

OS06 - Next Generation Data Applications
Play session   View PowerPoint

OS07 - SQL Server Team Unplugged Discussion Panel
Play session   View PowerPoint

OS08 - SQL Server Powered By Dell & AMD
Play session   View PowerPoint

DBIS01 - Database Infrastructure and Scalability - Overview
Play session   View PowerPoint

DBIS02-1 - Availability Enhancements - Part 1
Play session   View PowerPoint

DBIS02-2 - Availability Enhancements - Part 2
Play session   View PowerPoint

DBIS03-1 - Security Enhancements - Part 1
Play session   View PowerPoint

DBIS03-2 - Security Enhancements - Part 2
Play session   View PowerPoint

DBIS04 - Policy-based Management and Multi-Server Administration - Part 1
Play session   View PowerPoint

DBIS05 - Resource Management and Troubleshooting Enhancements
Play session   View PowerPoint

DBIS06-1 - Features That Need Managing - Part 1A
Play session   View PowerPoint

DBIS06-2 - Features That Need Managing - Part 1B
Play session   View PowerPoint

DBIS07 - Features That Need Managing - Part 2
Play session   View PowerPoint

DBIS08-1 - Scalability Enhancements - Part 1
Play session   View PowerPoint

DBIS08-2 - Scalability Enhancements - Part 2
Play session   View PowerPoint

DBIS09 - Query Optimizer Enhancements
Play session   View PowerPoint

Enjoy,

Itay Braun Premier Field Engineer - SQL Server and BI Microsoft Services - UK

E-Mail: itayb@microsoft.com Mobile: +44-796-928-9996 Blog: http://sqlblogcasts.com/blogs/thepremiers/

Veni Vidi Fixit

A relational database... and now what?

aferrandiz — Tue, 06 May 2008 16:10:00 GMT

I have always told this in my classes to my students: managing a database is just like driving a car... it means everyone drives a car but few have a proper knowledge of driving rules, they just drive because they learnt how to do it and the rest is pure instinct... and this sometimes can lead to accidents.

In the database world it is exactly the same, both developers and administrators can manage a database, they just need to install the software, suggest a model using their intuition and load the data... that's it, it may work very well, but just a proper knowledge of the inner workings of a database engine can maintain data integrity for good.

The main factor that makes different one company or person from another is not only the data but how they manage and use this data.

Just a thought

AFL

Using the OUTPUT clause on INSERT when loading data using OPENROWSET and BULK operator

tonyrogerson — Mon, 05 May 2008 08:52:00 GMT

Say you have an external file you want to load into your SQL Server database; you need to load – verify – cleanse and insert and at the same time keep a cross reference between the original source file records and the inserted database rows. The OUTPUT clause is an ideal candidate to help you with this cause; most people use the IDENTITY property for surrogate keys; the OUTPUT clause allows you to get at the value returned by the IDENTITY for each row you’ve just inserted; unlike SCOPE_IDENTITY(...(read more)

Object Catalog Views in SQL Server 2005

Madhivanan — Sat, 03 May 2008 13:12:00 GMT

In addition to INFORMATION_SCHEMA VIEWS, in SQL Server 2005, we can use Object Catalog Views to know more informations about the objects

Refer this http://msdn.microsoft.com/en-us/library/ms189783.aspx

SQL Internals Viewer - New version with sparse column support

Danny — Fri, 02 May 2008 18:53:00 GMT

I've just released a new version of SQL Internals Viewer that has support for 2008 sparse columns, a feature introduced in SQL Server 2008 CTP6.

There are also a few bug fixes and minor changes.

It's available to download from http://www.sqlinternalsviewer.com/

Thanks to Kalen for the help with the sparse vector complex header info.

SQL Server 2008 February CTP Bug Bash Contest Results

ssqa.net — Thu, 01 May 2008 13:17:00 GMT

Contest-Results...(read more)

Information Overload ??

GrumpyOldDBA — Thu, 01 May 2008 09:44:00 GMT

As I've remarked before the profusion of Blogs ( from the various Microsoft teams and individuals ) whilst exposing so much more information then we ever had before also requires you to almost spend all your time just checking every blog you can find every day, and that's assuming you actually found the blog in the first place. In SQL Server World we tend to direct our attentions to SQL Server blogs, I'll list most of the ones I have bookmarked, however as I've also pointed out before...(read more)

Will you still want me, when I'm 64?

Christian — Tue, 29 Apr 2008 08:44:00 GMT

Thankyou to all those who attended my session on 64-bit SQL Server at the UK SQL Server User Group on 17th April 2008. I've just got round to uploading the slidedeck for those of you that have been asking for it. You can download it here: http://coeo.com/conferences.html. For anyone interested that wasn't there here is a summary of the content:

32-bit memory management

Virtual Address Space
memtoleave
/3GB
/PAE and AWE

64-bit memory management

Virtual Address Space

History of current 64-bit platforms
What value for Max Server Memory setting on 64-bit?
AWE/Lock Pages in Memory with 64-bit
Pagefile size for 64-bit

I'm going to expand some of these points into separate blog postings for easier reference very soon. Until then please feel free to comment here with anything I might have missed or alternative views.

Regards,

Christian Bolton MCA: Database
Database Architect
http://coeo.com - The SQL Server Experts

SQL Server 2005 and Disk drive allocation unit size to 64K - any benefit or performance?

ssqa.net — Mon, 28 Apr 2008 15:28:00 GMT

You may be aware that or seen within your Database platform about slower disk performance than expect having multiple disks in Windows Server 2003 environment when you use a hardware-based redundant array of independent disks (RAID) or a software-based RAID. This could be due to one of the reasons such as starting location of partition is not aligned properly with a stripe unit boundary in the disk partition that is created on that RAID. Further information we can refer from one of the KBA929491...(read more)

UK MS Premier Customer? - Premier Field Engineering are hosting an Operations Day for SQL 2008

tonyrogerson — Sun, 27 Apr 2008 19:06:00 GMT

5th June, Thames Valley Park, Reading sees an Operations Day for SQL Server 2008 for Premier customers only. More information: http://sqlblogcasts.com/blogs/thepremiers/archive/2008/04/27/operations-day-for-sql-server-2008-open-for-premier-customers-only.aspx Don't worry - we've plenty of stuff in the pipe line if you aren't lucky enough (or are for that matter). SQLBits is scheduled for the 12th and 13th Sept in Hatfield but more information on that in about a months time....(read more)

Operations Day for SQL Server 2008 (Open for Premier customers only)

ThePremiers — Sun, 27 Apr 2008 15:56:00 GMT

Dear Premier, I am pleased to announce that Premier Field Engineering (PFE) are hosting an Operations Day for SQL Server 2008. The day will provide customers with a wealth of technical information covering a wide range of issues relevant to SQL Server 2008 database administrators. When? 9am on Thursday 5^th June 2008 Where?Microsoft Campus (Chicago 1) – Thames Valley Park – Reading – Berkshire. RG6 1WG. Please find a customer ready copy of the agenda attached to this e-mail. Places will be allotted on a first come first served basis. The registration process is as follows:

Please speak with your Technical Account Manager (TAM) if you wish to attend

If you have any questions or concerns then please contact the event organiser, Gareth Ford garethf@microsoft.com. Thank you.

Gareth Ford

People Talk, I deliver

SQL Server 2008 UK Usergroup London Launch event

simonsabin — Thu, 24 Apr 2008 08:46:00 GMT

SQL Server 2008 UK Usergroup London Launch event As part of the SQL Server Launch wave we are holding a usergroup meeting to celebrate the launch (not RTM). We're going to run this as an open session so you'll be able to ask us to cover the areas you want us to cover. We have loads of giveaways, including Vista, training vouchers, Technet subscriptions and SQL Server licenses. To register for the event go to the UK SQL Usergroup site http://www.sqlserverfaq.com/?eid=114 Registration is at...(read more)

SSAS Stored Procedures - connecting to the transactional data source

DrJohn — Wed, 23 Apr 2008 13:17:00 GMT

There are often circumstances where an SSAS stored procedure needs to connect to the transactional database to perform a query. This is certainly the case in a near-real-time OLAP solution where the cube sits directly on top of the application database and dynamic dimension security is implemented by a stored proc. Here the SSAS stored procedure has to query the transactional database in order to get the user's authorisation. Clearly the SSAS database has a data source and it uses this connection to process the cube. So how do we get the connection string?

Well the short code snippet below provides the solution. The code simply connects back to the current database using AMO and obtains the connection string from the data source object. It then removes the redundant "Provider=SQLNCLI.1;" before returning a valid SQL connection string that can be used by SqlConnection().

Of course, you must provide your stored proc assembly with the "Unrestricted" permission set and set its impersonation mode to "Service Account" for the connection string to work.

using AMO = Microsoft.AnalysisServices;

using Microsoft.AnalysisServices.AdomdServer;

...

/// <summary>

/// Obtains the SqlConnection string used by the OLAP database

/// </summary>

/// <returns></returns>

public static string GetSqlConnectionString()

{

// connect to the current instance of Analysis Services and return the transactional data source

AMO.Server svr = new AMO.Server();

svr.Connect("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName);

// get the connection to the transactional database from the OLAP connection manager

string connStr = svr.Databases.GetByName(Context.CurrentDatabaseName).DataSources[0].ConnectionString.Replace("Provider=SQLNCLI.1;", "");

svr.Disconnect();

return (connStr);

}

Know The Transaction Log – Part 4 - Restoring Data

sureshbarathan — Tue, 22 Apr 2008 07:05:00 GMT

After looking about the backups in SQL Server it is time to know about the Restore and Recovery in SQL Server.

SQL Server supports three levels of Restoring data. They are

1. Complete Database Restore : This is the basic restore strategy. A complete database restore for a simple recovery model database simply involve a full backup followed by the latest differential backup if any available. For a full or bulk-logged recovery model database this complete Data Restore involves restoring a Full Backup followed by a latest differential backup and then a sequence of Transaction log backup in which they are backed up and finally tail-log backup if any available.

2. File Restore : This restore operation is very useful when any one of the files in the file group is damage. The main advantage of this restore is restore time will be less, obviously compared to complete database restore. For simple recovery model database file restore will work with read-only secondary files.

3. Page Restore : This restore is only applicable for Full or Bulk-Logged Recovery model database and not available for Simple recovery model. Using this level of restore , a particular page or pages can be restored.

How Restore Works ?

Restoring is the process of copying data from a backup and applying Transaction logs to the data to point of time when the backup is taken. This process is done in three phases, Data Copy phase, Redo Phase and Undo Phase.

Data Copy Phase: The process of copying the data, index and log pages from the backup media to the database files. No log backups nor log information in the data backups are applied in this phase.

Redo Phase : This phase applies logged changes to the data by processing log information from the log backups.

Undo Phase : This phase applies undoing any uncommitted transactions from the data that are restored from Backup and brings the database online.

In this stage we have to understand the relationship between the WITH RECOVERY and WITH NORECOVERY options in RESTORE Command.

The default option is WITH RECOVERY. This will continue the Undo phase after completing the REDO phase.

A normal restore operation stops at the redo stage if WITH NORECOVERY is included in RESTORE statement. This allows Roll Forward to continue with the next statement of the Restore Sequence, in which the other backups typically a differential or a transaction backup will do the undo phase.

For a Full Recovery model database or for a Bulk-logged recovery model database, a restore operation is done by a sequence of RESTORE statements. This sequence is called Restore Sequence.

For a simple scenario a restore sequence might be

· starting with restoring a recent full backup,

· applying the most recent differential backup,

· restoring the sequence of log backups in the order they are backed up after the most recent differential backup,

· finally the restoring tail log backup if any taken after the failure occurred.

For more complex scenarios, complex sequence planning will be required. For these planning a recovery path is very important. A Recovery Path is a complete sequence of data and log backups that can bring database to a point of time. For more details about Recovery Path search in Books On Line.

Complete Database Restore :

A simple restore strategy. Let us see how you have to do a Complete Database Restore using an example. Suppose for a full or bulk-logged recovery model database, a series of backups are taken in the following schedules. A Full backup on Monday 10 PM, Differential backups are scheduled on 10 PM of Wednesday,Friday and Sunday. Transaction Log Backups are scheduled twice a day 6 AM and 6 PM every day. In this sequence, the database is got failed on Saturday 4 PM. How to Restore this database with available backups ?

On a failure situation of database, first thing we have to do is take the tail log backup with NOTRUNCATE option if possible. So take the Tail-log backup first.

Every Restore is to be started with Full Backup. So start with restoring the full backup taken on Monday 10 PM with NORECOVERY option. We have the latest differential backup taken on Friday 10 PM. So apply that backup, and we can omit applying log backups taken after Monday 10 PM and before Friday 10 PM. After restoring this latest differential backup, we have to restore the log backup taken on Saturday 6 AM. That is latest log backup taken on schedule before failure. Now the database is ready up to the Saturday 6 AM. Now, Restore the tail backup that is taken after the failure , that Saturday 4 PM with RECOVERY option. Now the database is fully restored.

File or File Group Restore :

To Restore a Single File in a File group or Complete File group of a Database you have to use Restore Command with FILE option or FILEGROUP option. All you need is unbroken chain of log backups from the time of file or file group backup was made. Before applying the file or file group backup you have to take the transaction log backup. After restoring the file or file group , you have to restore all the transaction log backups to synchronise that file or file group with the rest of the database.

Let us see an example. Suppose a SecondaryFG is a file group of a database backed up on Friday 12 noon and the database is still in use. Backing up of Transaction log of this database is scheduled on 10 AM, 11:30 AM, 1:00 PM, 2:30 PM , 4:00PM, 5:30PM and so on. Note that the database is still in use, and the changes are made in SecondaryFG and other file groups too. At 5:15 PM, a media failure occurs that corrupts the SecondaryFG. Now we have to restore this. First take the tail log backup that contains all the log records after 4:00 PM Log Backup, with NOTRUNCATE NORECOVERY, to make the database in Restoring state so that no other modification will be done after the failure. Now apply the backup that was taken at 12 noon. So the SecondaryFG now have all the changes that are made up to 12 noon. Now start applying the Transaction log backups in the sequence of they backed up that is apply 1:00PM Backup first, 2:30 PM backup second, 4:00 PM backup third. Now the SecondaryFG is synchronised with all the database files up to 4:00 PM. Finally apply the tail log backup that was taken after the failure to make SecondaryFG fully compatible with all the files of the database.

Page Restore:

Page restore is only possible for databases using Full Recovery model or Bulk-logged Recovery model. All Editions of SQL Sever other than Enterprise Edition support offline Page Restore whereas SQL Server 2005 Enterprise Edition supports Online PAGE Restore when database is online.

A page may be marked as suspect page, when a query or DBCC CHECK TABLE or DBCC CHECKDB failed to access it. Every page in a database that is marked as suspect will have an entry in msdb..suspect_pages table. Event_type column of this table may have either one of the following numbers, 1 for the pages marked with error number 824 other than Bad Page ID and Checksum Error; 2 for Bad PageID ,3 for Checksum Error. 4,5 and 7 for the repaired pages. This table is limited to size and if it is full, the errors could not be logged in this table. So it should be a DBA’s routine to delete the all the records in the msdb..suspect_pages table having event_type is greater than or equal to 4 in regular intervals.

Get the pageId and fileId from msdb..suspect_pages for the pages to be restored. Start RESTORE with full or File or Group Backup that contains the pages to be restored and use PAGE option. Then apply the most recent differential backup if any available and apply all subsequent log backups. Now backup the log and restore it again to match the last_target_LSN in sys.masterfile.

Limitations of Page Restore

· Only Database pages can be restored not the log pages.

· File boot page i.e Page 0 can not be restored and page 1:9 can not be restored that is database boot page.

· GAM , SGAM and PFS Pages can not be restored.

For more details see Books Online.

With this I conclude my Know The Transaction LOG Series. Some things are purposely omitted in this series of post that are POINT-IN-TIME Restore using RESTORE with STOPAT option to avoid over doses.

Note: This post was originally published in my SQLThoughts blog.

KNOW THE TRANSACTION LOG – PART- 3

sureshbarathan — Tue, 22 Apr 2008 07:02:00 GMT

This is third article in the KNOW THE TRANSACTION LOG series. In Part 1 I explained about the Transaction Log File and its behaviour. In Part 2 I explained about the Recovery Models available in SQL Server 2005 which affects the behaviour of Transaction log file of the database. In this Part 3, I am going to explain about the various Backup options available in SQL Server 2005, because, Backups are the backbone of the Restore Recovery of course for a DBA too. :)

Backups in SQL Server 2005.

Two major categories of Backups are available in SQL Server. They are Data backup and Log Backup.

Data backup includes image of one or more data files and log record data. It has three types.

· Full Database Backup includes all data files in the Database which is complete set of data. This also have enough log records that allow to restore the data during restore recovery. This is called base backup. Every restore situation need at least one base, full backup. For small databases, performing a Full Backup takes small amount of time and the backup occupies small amount of disk spaces. As database becomes larger, the full backup takes more time to finish, so as the restore takes more time during recovery. As for as larger databases concern, take Full backup along with supported differential backups, transaction log backups to reduce backup and restore time and associated system overhead.

When restoring a database from a Full backup , SQL Server re-creates the database in one step. As Full database backups include transaction log records within it, after restoring is over, all uncommitted transactions during the time of full database backup taken, are rolled back. So the restored database matched the original database when it was backed up minus the uncommitted transactions.

· Differential Backup : Differential backup of a database backs up only modified data since a last base database backup. It is small in size comparative to Full Database backup, obviously, runs fast, saves backup time. The base for first Differential backup after the full backup, is last full backup and for subsequent differential backups the base is the previous differential backup until the next full data backup is performed. This base is called as differential base. For a Simple Recovery model database, there should be only one differential base and for Full Recovery Model, there may be multibase Differential bases are allowed, but it is difficult to administer. For a Read-Write and online databases, sys.database_files system catalog view returns various information including three column information about differential base. That columns are differential_base_lsn , differential_base_guid, differential_base_time. For a read-only databases sys.master_files catalog view should be use to get the information about the differential base.

Have a full database backup and subsequent frequently taken differential backup for a large mission critical databases to avoid data loss. As the differential backup process takes smaller time to finish, the restore from it also takes minimum time.

When restoring from Differential Backups , a full backup restore should be done first and then a most recent Differential backup is to be restored even though a multiple differential backups has been taken between Full Backup and most recent Differential backup. No Log Backups that were taken between full backup and Differential backup need to be restore. If any tail log backup that has been taken before the full backup is restored, then that should be restored after restoring the differential backup.

· Partial Backup includes primary file groups and read-write file groups. Excludes read-only file groups by default. It can back up specified read-only file groups while taking backup. This is new to SQL Server 2005. It is different from differential backup. It is designed to provide flexibility for databases having simple recovery model. A Partial Backup of a read-only databases only have the Primary file groups files. To create Partial Backup we have to use READ_WRITE_FILEGROUPS [<filegrouplist] option in T-SQL Statement. Partial Backups can not be done through SSMS. Maintenance Plans also do not support Partial Backups.

A Partial Backup can be base for the Differential Partial Backup. Differential Partial backups back up all the data extents that are modified after a base partial backup of same set of file groups are performed. This can be performed with the help of the following command.

BACKUP DATABASE database_name READ_WRITE_FILEGROUPS [ , <file_filegroup_list> ] TO <backup_device> WITH DIFFERENTIAL

· File Or File Group Backup includes the file or file groups specified. An Individual file of a database alone can be backed up with this type of backup. This backup is very useful for the failure situation like if only one file is damaged in the database, we can restore that particular file only instead of having full database restore. This can minimize the restore time very much. There are two types of File backups. File Backup and Differential File Backup. A File Backup of a database can be the base for the Differential File Backup. Performing Differential File backup will give you an error if you changed the read/write file to read-only file after taking last full file backup. So whenever you change a read/write file to read-only file or a read-only file to read write file then take a full file backup.

An advantage of having file backup is recovery from damaged files or a file located in damaged media is very faster. The only damaged files can be restored. The disadvantage of this is maintaining complete file backup set can be more time consuming and complexity of administrative task is increased.

A complete set of file or file group backup is equivalent to Full database backup. When performing file group backups for a full or bulk logged model database do perform transition log backups additionally.

· Transaction LOG Backup includes only log records. For a full or bulk-logged recovery model regular transaction log backup is required. If not taken the transaction log file grows continuously till the disk is full. LOG Backup can be performed with the following command.

BACKUP LOG <database name> To <device name>.

There is a special type of LOG Backup that is Tail-Log backup. This log backup is taken immediately after the database failure if the log disk is accessible. This can be done if you include WITH NORECOVERY option in BACKUP LOG Command. When you issue this option the database becomes Restoring State and becomes offline to guaranty no modification can be done after finishing the tail-log backup. After taking Tail-Log Backup you have to restore the database.

· COPY-ONLY Backup : This is a special situation backup. It does not affect the regular SQL Server Backups and Restore sequences. After taking the COPY-Only Backup the transaction logs are not truncated. As the name specified it does only copying either Full Database or Full Log. This can be performed when issuing WITH COPY_ONLY option in BACKUP Command.

Backup History

The information about the backup history are stored in the msdb database which are very useful to manage backups. The following system tables in the msdb system database store history information about backups.

1) Backupfile stores a row for each data and log file in the database including a column is_present that indicates whether that file was backed up as a part of the backup set.

2) Backupfilegroup stores a row for each filegroup in a database at a time of a backup but this table does not indicate whether the filegroup was backed up or not. This table is new to SQL Server 2005.

3) Backupset stores a row for each backup set when a new backup set is created for each backup event.

4) Backupmediaset stores one row for each media set to which backupsets are written.

5) Backupmediafamily stores one row for each media family or its part of mirrored media set and one row for each mirror in the set.

For more information about Backup History see Books Online.

In the next and final part, Part-4, I will explain about Restoring Database.

Note:

This post was originally published in my SQLThoughts blogger blog.

How to configure SQL Server Reporting Services in a DMZ environment?

ssqa.net — Tue, 22 Apr 2008 06:27:00 GMT

Many questions will come out if you need set your Reporting Server on Internet within DMZ environment? The important aspects involves SQL Server database, Reports Services & Report Manager, for the sake of discussion say if Report Services and Report Manager are on a server in DMZ (behind firewall) that is not a member of the Domain then you need to setup port & authentication. Also you need to consider that Report server and DB server need not be in the same domain, if your database server...(read more)

The FACT is that it is not so DIM after all

DrJohn — Tue, 22 Apr 2008 05:13:00 GMT

I have always looked down upon the Fact and Dim prefixes applied to the tables within the AdventureWorksDW database with derision. "Why," I asked myself, "would anyone feel the need to use such prefixes? Clearly this is BI for dummies." Given that most BI projects use a nice clean, dedicated data warehouse database where all the tables are imported into the cube, perhaps my derision was justified.

However, in my current project I have been building a near-real-time OLAP solution directly on top of an application database. Of course, to isolate the cube from potential schema changes, I created an abstraction layer in the form of a set of views. However, as the project has progressed and the large team of developers has created more and more database objects, it has become increasingly difficult to find the views created for the cube among the debris.

I recently had to re-build the entire cube from scratch due to a bug caused by reverting to a previous version in source control. Of course, I took the opportunity to consolidate all my T-SQL code into a few files and adopt a consistent naming convention for all my views. So what naming convention did I adopt? Well Fact and Dim of course! Why? Well because anyone following on from my work who is half-familiar with the BI sample databases will immediately recognise the relevance.

I subsequently found out the big benefit of adopting this convention – it makes creating a DSV much, much easier! A quick search for all objects containing 'Fact' and 'Dim' soon had my new DSV populated with all the relevant objects.

So the Fact is that this practice is not so Dim after all!