How to take backup all databases in SQL Server

Aravind Kumar    Code Snippets, SQL Server     All database backup, Database Backup, SQL

The following code will helps you to take all the databases at a time

Source code

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\SQLBackup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
 
 
 
�

Passing lists to SQL Server 2005 with XML Parameters

Aravind Kumar    SQL Server     passing list as a parameter to sql procedure, passing xml in spoc, sql procedure, stored procedure, xml sql

Getting started with SQL Server 2005′s XML Syntax

XML variables in SQL Server 2005 make it easy to “shred” XML strings into relational data. The main new methods you’ll need to use are value() and nodes() which allow us to select values from XML documents.

Sql Code

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') AS ParamValues(ID)

Which gives us the following three rows:

3
6
15

Alright, just show me how to pass a list in a procedure parameter already!

Here’s a proc which takes a single XML parameter. We first declare a table variable (@Products) and load the XML values into it. Once that’s done, we can join against the @Products table as if it were any other table in the database.

Sql Code

CREATE PROCEDURE SelectByIdList(@productIds xml) AS

DECLARE @Products TABLE (ID int)

INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') AS ParamValues(ID)

SELECT * FROM
    Products
INNER JOIN
    @Products p
ON    Products.ProductID = p.ID

Now we can call it as follows:

Source code

EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'

In order to use this, you’ll need to an XML string with your ID’s.:

csharp Code

public static string BuildXmlString(string xmlRootName, string[] values)
{
StringBuilder xmlString = new StringBuilder();

xmlString.AppendFormat("<{0}>", xmlRootName);
for (int i = 0; i < values.Length; i++)
{
xmlString.AppendFormat("<id>{0}</id>", values[i]);
}
xmlString.AppendFormat("</{0}>", xmlRootName);

return xmlString.ToString();
}

Profiler for Microsoft SQL Server 2005/2008 Express Edition

Aravind Kumar    SQL Server     Query Trace, SQL Server, SQl Server Express

Download “AnjLab Sql Profiler”

http://code.google.com/p/sqlexpressprofiler/downloads/list and install it.

Use the following SQL statement to know your database ID

SELECT dbid FROM master.dbo.sysdatabases WHERE name = ‘Yourdatabasename’

New trace and use filters to trace particular database.