SQL – Backup Methods

Introduction

Microsoft SQL Server provides high-performance and easy to use backup and restore capabilities. Backup component of SQL Server provides strong facility by which you can protect your critical data stored in a database. Taking proper backup of a database time-to-time prepares you to respond quickly and easily against any data loss or disaster. So let's find out some cool and easy ways of taking bakup in SQL Server.

Database backup - an introduction

In SQL Server, "Backup is a copy of database, which includes copy of all data stored in tables as well as other database objects (like stored procedure, views, functions etc)". Extension of database backup files in SQL Server is .bak. We creates backup's of databases to protect our data loss due to any system failure. Taking backup is an important part of a DBMS.

Let's say you have a database for your company where all data of your company like employee, customer and transaction details get stored everyday. Suppose someday something goes wrong with your system and your system got crashed. Now what? All your company data erased and you lost all details, which can put a full stop on your company's future.
To prevent these kind of risk and losses of data, we have backup database facility in SQL Server. With SQL backup facility we can take backup of databases daily or at some interval of time (1 hour, 5hours etc). All it depends on your data storage frequency and importance. Backing up a database creates the same copy of the database in the form of .bak file.
So if you have proper database backup, then you can recover easily and early from the disaster like above. With proper backup of databases you can restore your latest backup and again start working, which will not cost a lot to you.

So backup plays an important and crucial role in database management systems. Regular backup is an important task for a DBMS. So let's find out in how many ways we can use SQL Server to take backup.


Taking backup using SSMS(SQL Server Management Studio)

SSMS (SQL Server Management Studio) provides facility to take backup of a database. Taking backup using SSMS is not difficult, it's a very basic and easy method of backing up a database. Following are the steps and also visuals displayed; those will tell you how to take a database backup using SSMS.

NOTE: To use SSMS you must have SQL Server 2005 installed

First step is to open SSMS (SQL Server Management Studio). To open SSMS there are two different ways, you can make use of anyone which you like. Two ways are as follows

1. Start -> All Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio
2. Start -> Run -> Type "sqlwb" -> OK

You can use any way to open SSMS, normally second one is easy and I also use second way.

Then you'll get SSMS will open enter your SQL Server username and password to login. After login you'll get window as below, expand the database node to get list of all databases.


Then right click on database -> Select "Task" -> Click "Back Up..." as below I have done in the following image.



After clicking backup option, database backup window will open, in backup window, under Destination section, choose "Disk" option to save your backup file inside your system. Then click on "Add" button to add the path where you want save your backup file on your disk. After selecting the path click "OK" to complete backup. It will take some time to complete backup, it all depends on your database size, if your database size is huge, then obviously it will take time. After completion a backup file will be created inside the path what you were chosen.

BACKUP (Transact-SQL)

Above method (taking backup using SSMS) includes 5-6 steps to backup a database. SQL Server also have Backup (T-SQL) command using that we can take backup of a database in just one step, that is by executing a simple query backup command in Query Analyzer (which is also SSMS in SQL Server 2005). Here I am just explaining simple backup command syntax, for more detailed explanation about options of backup command visit this link: http://msdn.microsoft.com/en-us/library/ms186865.aspx. A simple backup command to take backup of a database in specified path on the disk is as follows:

BACKUP DATABASE AdvWorks TO DISK = 'C:\backup\AdvWorks.bak' WITH INIT

The above command will take backup of database "AdvWorks" in the path "C:\backup\" with name "AdvWorks.bak" name. WITH INIT option here specifies that any existing database backup with the name "AdvWorks.bak" presents inside "C:\backup\" path will be overwritten.
Backup of all databases (System & User Databases)

Till now we have seen two different methods of taking backup of a single database, what about rest databases. If you have 5 or 10 databases (which is a case normally with web servers where many websites are hosted and each one have their own database inside the same SQL Server) in your SQL Server then its not a logical approach to take backup of all databases one by one.


So what to use if we want to take backup of all databases present in side our SQL Server? Answer is a hidden inside a T-SQL query using which you can take backup of all databases in one go inside the specified path on the disk. For this query we'll use a system stored procedure "SP_MSforeachdb" as follows.

SP_MSforeachdb 'BACKUP DATABASE ? TO DISK = ''C:\backup\?.bak'' WITH INIT'

In the above query we are using a system stored procedure which will run the BACKUP command given inside single quotes ('') for each database one by one. ? (Question mark) in the above command specifies the database name which is handled by the stored procedure. It will put the name of databases automatically one by one.


Let's say you have two databases db1 and db2 in your SQL Server with including the system databases master, msdb, model etc. The stored procedure "SP_MSforeachdb" runs the command specified in the parameter for each databases in the SQL Server including system databases. That means the above query internally runs the Backup command for each database one by one some thing like below, where question mark will be replaced by corresponding database name.

BACKUP DATABASE db1 TO DISK = 'C:\backup\db1.bak' WITH INIT

Then

BACKUP DATABASE db2 TO DISK = 'C:\backup\db2.bak' WITH INIT

Then

BACKUP DATABASE master TO DISK = 'C:\backup\master.bak' WITH INIT

And so on for all user and system databases .....

It's a single query and will take your entire databases backup inside specified path on the disk, you need not to sit and take backup of each databases one by one. Just execute this command and rest the command will do for you. It will take time to execute on the basis of your databases size.

Backup of all user databases

We know how to take backup of entire databases present inside a SQL Server using stored procedure SP_MSforeachdb. But the problem in this stored procedure that it takes backup of all user databases as well as system databases. Backup of system databases normally not required, rare cases where we nee to take backup of system databases (how to take database backup of only system databases, we'll cover this further in this article).
So can we do anything to take backup of only user databases? Answer is yes, you can. Following query will help you to achieve this. You can create an stored procedure for this or you can execute it as a single query in SSMS.

-- command for taking backup of userdefined databases
DECLARE @cursor AS CURSOR
DECLARE @dbname AS VARCHAR(20),
@query AS VARCHAR(100)
SET @cursor = CURSOR SCROLL FOR
SELECT NAME FROM MASTER..Sysdatabases
WHERE NAME NOT IN ('master', 'model','msdb', 'tempdb')
OPEN @cursor
FETCH NEXT FROM @cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''C:\backup\'+ @dbname+'.bak '' WITH INIT'
EXEC(@query)
FETCH NEXT FROM @cursor INTO @dbname
END
CLOSE @cursor
DEALLOCATE @cursor

The above query will select the database names from Master..sysdatabases table excluding the system databases. Then we'll run a cursor that will take backup of all user databases one by one. It's a good query when you need to take backup of all your databases. It is usually required when you take backup in a month or in a week. Just execute the above query and it will take the latest backup. Create a stored procedure and just execute that and your done!
Backup of all system databases

Backup of system databases not required normally, but if you have to take backup of system databases. Then you can achieve the same by modifying little bit in the above query. So query which will take backup of only your system databases.

-- command for taking backup of system databases
DECLARE @cursor AS CURSOR
DECLARE @dbname AS VARCHAR(20),
@query AS VARCHAR(100)
SET @cursor = CURSOR SCROLL FOR
SELECT NAME FROM MASTER..Sysdatabases WHERE NAME IN('master', 'model','msdb')
OPEN @cursor
FETCH NEXT FROM @cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''C:\backup\'+@dbname+'.bak '' WITH INIT'
EXEC(@query)
FETCH NEXT FROM @cursor INTO @dbname
END
CLOSE @cursor
DEALLOCATE @cursor

As we know that taking backup of system databases is not required. You can take backup of all your user databases on daily basis but for system databases if you want to take backup then execute the above command weekly or on monthly basis and your system databases will also secure, daily backup for system databases is not needed because we do not modify them on daily basis. So the above command will be handy when you want to take backup of only system databases.


Ref: http://www.sqlservercentral.com/articles/Backup/64454/

Temp Tables vs Table Variables

The Need For Temporary Storage

When working with Stored Procedures or scripts, developers / DBAs are frequently required to work on complex SQL operations that are required to save result sets into temporary locations which are then to be pull up and used later. You might need to physically persist the state of your data somewhere. This is where functionalities provided by Temp Tables and Table Variables come into play. Both exist to provide the same functionality: a temporary storage place for result sets yet they are different in many other ways.
Let’s start with Temp tables first.

First, the basics:
How do you use temp tables in SQL Server? Simple. Let’s take a look at the example and then proceed on understanding them even further.
Temp Table:
eg

CREATE TABLE #TempEmail
(
RowId INT IDENTITY (1,1),
EmployeeId INT,
EmployeeName VARCHAR (100)
)
Notice that we use the CREATE keyword instead of DECLARE unlike a normal T-SQL variable. Further, a Temp Table object always starts with either a ‘#’ or a ‘##’. Where ‘##’ refers to a global temp table object and a single ‘#’ refers to a local temp table object. The rest of the syntax is just like a normal permanent table.
Local Temporary Tables And Global Temporary Tables
The use of either ‘#’ indicates to SQL Server that this table object is visible to it only during this particular session of SQL Server i.e., only the connection that has created this table can access it. You are therefore creating it as a private temporary table.
You can create a global temporary table from any connection or database and this table is accessible from any connection to enable with the performing of normal operations on it. Therefore, with this property in mind, you should be careful in creating global temporary table names as this might lead to a conflict when other connections create a similar global temporary table with the same name. A global temporary table will persist only till the creating connection stops.

Question: When do I use what?

Use a global temporary table object if you want the table to persist only during the life time of the connection and want it to be accessible from any database and another other session. A local temporary table will be accessible only by the level that created it (eg: Stored Procedure, Triggers and when they are nested (A stored procedure call from within another stored procedure).
To be frank, I haven’t seen or heard of a real-time scenario where a global temporary table variable could really be put to use because the moment you think of a situation where it could be used, the first thought that might cross your mind will be that it will make more sense to store that information in a permanent table instead.

Temporary Tables and tempdb

A temporary table has a representation within the tempdb. This means that it is physically stored on disk. However, there is a little misconception regarding this aspect and there are exceptions when this does not happen. For example when SQL Server finds out that there is enough memory to spare, their pages are instead contained within cache blocks. This is common when you tend to create, update and delete them pretty quickly and hence, they tend to get stored in memory and a physical operation on disk is avoided by SQL Server as it really is not required.

Features of Temp Tables

We’ll list out features that differentiate a Temp Table between either a Permanent Table or a Table variable. These pointers will be helpful to keep in mind when you consider Table Variables in the next post.

Scope: Within a connection, a temporary table object is visible to the creating level and inner levels (nested). For example, if you create a stored procedure and declare a temporary table object within it, you can call another stored procedure from that stored procedure (a nested stored procedure) and perform operations like inserting, updating and deleting that temporary table object. Once the main creating level terminates, the temp table is automatically destroyed. But don’t be too complacent – you’ll have to wait for the system to perform a clean up and therefore, it is highly recommended that you manually drop your temporary table.

Locking: The prospect of table locking is reduced when it comes to local temporary tables since this table is being used by only one user. One aspect where you might want to keep this in mind is that if you cancel a transaction which contains the creation of a temp table object and then cancel that query, an exclusive and update lock can appear on the tempdb. This lock will persist till the complete transaction has closed with a COMMIT or a ROLLBACK

Logging: There is less logging involved with temporary tables compared to permanent tables.

Transaction: When using a temporary table, a temporary table is an integral part of an outer transaction and therefore, ROLLBACKs must be supported by Logging

Indexing: We can create indexes on temporary tables explicitly on them. Hence, there is scope for performance enhancement when you talk about temp tables.

Constraints: All constraints are available for exploiting on a temp table EXCEPT when it comes to referring a FOREIGN KEY constraint.

Statistics: SQL Server can create Statistics for temp tables just like we do for permanent tables and therefore, the query optimizer has the option of choosing different plans. Hence, with this in mind, be aware of the scope of Stored Procedure Recompiles.

Recompiles: There is scope for a large number of Stored Procedure Recompiles especially when you have DDL statements mixed anywhere within your Stored Procedure.

Temp Table Size: Can hold any volume of data. This will be a strong part of the deciding factor when you want to choose between a temporary table and a table variable.

I hope you’ve got a gist of what SQL Server Temp Tables can do and some of the main pointers regarding them. If you have any points that you feel should be addressed or should be pointed out, please feel free to give a buzz and we shall discuss (and that’s the point of this blog!). I was expecting to write a quickie post since it’s way past midnight but I think I’ve already crossed a couple of pages. In my next post, we’ll do a similar study on Table Variables and establish some solid ground on both SQL Server features before we get to the crux of what I really wanted to convey – When to use what and why!
Coming to the topic of Table Variables, the one thing I’ve noticed so frequently is that when you talk about Table Variables to most developers, they automatically have this preconceived notion that they’re the best option compared to Temp Tables for better performance. The fact of the matter is that this is only true in certain cases and absolutely not in most other cases where variable factors can come into play.

Table Variables
Before we start, let’s review the basics. As we all know, when working on the database front, there are many situations where we might need to persist result sets in a temporary location which would then be required to be retrieved later. SQL Server achieves this through the use of Temp Tables and Table Variables.Let me illustrate the use of a Temp Variable eg:
DECLARE @Employee TABLE
(
RowId INT IDENTITY (1,1),
EmployeeId INT,
EmployeeName VARCHAR (100)
)
Some observations:
1. Unlike a normal Table or a Temp Table, we use the DECLARE keyword instead of CREATE
2. We also use the ‘@’ symbol preceeding our Table Variable name like the use of any other variable.

Features of Table Variables
Now that you’ve got a hang around working with a Table Variable, let me mention the main pointers that we need to keep in mind while working with.

Transactions: Table Variables are not bound to any transaction as they are just like any other variable (in most cases .i.e).
You could try these simple set of queries to prove it:

BEGIN TRAN
DECLARE @Employee TABLE
(
RowId INT IDENTITY (1,1),
EmployeeId INT,
EmployeeName VARCHAR (100)
) INSERT INTO @Employee
SELECT 101, ‘John Carmack’ UNION
SELECT 102, ‘Jay Wilbur’ UNION
SELECT 103, ‘Adrian Carmack’ UNION
SELECT 104, ‘George P. Alexander Jr. ‘ SELECT * FROM @Employee — see the Table Variable values? ROLLBACK — we roll back here. Normal Table or Temp Table would loose everything SELECT * FROM @Employee — we still see an Employee table with John, Jay and Adrian!
If you try the same thing with a Temp Table, doing a SELECT * at the end of everything would give you an error since the Table was never created in the first place (due to a ROLLBACK). Now this is interesting because as there is less logging and Database locking involved, performance can come into consideration.
Minimum Constraints: A Table Variable permits us to use only the PRIMARY KEY, UNIQUE KEY and NULL constraint only. What this implies behind the scenes is that we can have unique indexes. The only possibility of creating a non-unique index is if we add attributes and make that blend unique and have a PRIMARY KEY or a UNIQUE KEY on the combination we just made.
And yeah, this point also implies that we don’t have FOREIGN KEY constraints incase you asked..
No SELECT INTO: We cannot use a SELECT INTO with Table Variables in SQL Server 2000 though the feature is available with Table Variables in SQL Server 2005. Likewise, we can also have INSERT INTO working with Table Variables against a SELECT but not against an EXEC StoredProcedure.
Eg:
INSERT INTO @Employee SELECT EmployeeId, EmployeeName FROM TempTable will work while an
INSERT INTO @Employee StoredProcedure will not work.
Similarly, SELECT ‘4005′,’John Carmack’ INTO @Employee won’t work either.
No ALTER TABLE Variable: We cannot ALTER a Table once it has been declared. This may look a little rigid but remember that recompilations can come out like wild fire when there are DDL (Data Definition Language .i.e Schema) changes and therefore, this helps to avoid recompilations.
Table Variable Name!=Table Variable Column Name: err�I don’t know a better and shorter way to say that a Table Variable’s name cannot be the same as a Table Variable’s column name.
Scope: Just like any other variable, a Table Variable’s scope exists only within the context of the current level. Therefore, unlike Temp Tables, it is not accessible to sub levels (of Stored Procedures)

Table Variables and The TempDb: Okay, now I’ll touch upon one of the most common myths that exist among developers: that Table Variables have nothing to do with TempDb and therefore, they have no physical representation in the TempDb and therefore, they reside in ONLY memory� and therefore they’re the best option for efficient processing.
Not entirely true. Table Variables do indeed have a physical representation within the TempDb and this can proved with a simple query in your database against the TempDb:

SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘%#%’ — no result in TABLE_NAME column
GO DECLARE @Employee TABLE
(
RowId INT IDENTITY (1,1),
EmployeeId INT,
EmployeeName VARCHAR (100)
) SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘%#%’ — notice that a new #something exists in TABLE_NAME columnGOSo yes, Table Variables do have a physical representation within the TempDb. So how does this affect our understanding of Temp Tables residing in TempDb and Table Variables also residing in TempDb?
The fact of the matter is that this depends entirely on the scenario and in certain cases, Temp Variables are faster and in certain cases, Temp Tables are faster. Table Variable’s content will reside in memory when it is available but when that limit gets crossed, it will resort to physical I/O.
No Statistics: When it comes to Table Variables, the SQL Server optimizer does not create distribution statistics. Therefore, you run the risk of referring not-so-good query plans when the SQL Server optimizer selects after checking up with histograms. And if you consider this aspect with Tables Variables that contain huge amounts of data, we fall into serious I/O thrashing. Hence, as stated in the closing section of the last point, we have to have a thorough understanding of our scenario to choose a temporary object for the context.
So I hope you got a gist of more than a simple “what Table Variables are”. Essentially, you might have noticed that their usage has a lot of strings attached also though, at the same time, there are seemingly certain performance gains while in certain situations, the tables (no pun intended) can be turned and invariable we can end up with inefficient queries that may lead to a performance hit.
Feel free to raise any point or drop in something that I might have missed or anything from your experience that might help us glean even further into this beautiful functionality that SQL Server developers have at their fingertips while building the back end of their applications.
We’ll finish of this series with one more post that will compare both Table Variables and Temp Tables so that we can understand the best possible scenario to use each one.

Ref : http://sqlpractices.wordpress.com