Convert DateTime

------Convert from datetime to date -SQL Server 2008

DECLARE @date Date

Declare @datetime datetime

SELECT @datetime = GETDATE(), @date = @datetime

SELECT @date AS 'date', @datetime AS 'datetime'

------/Convert from datetime to time-SQL Server 2008

DECLARE @time time, @datetime datetime

SELECT @datetime = GETDATE(), @time = @datetime

SELECT @time AS 'time', @datetime AS 'datetime'

------Convert from datetime to datetime2-SQL Server 2008

DECLARE @datetime2 datetime2, @datetime datetime

SELECT @datetime = GETDATE(), @datetime2 =

@datetime SELECT @datetime2 AS 'datetime2', @datetime

AS 'datetime'

------Convert from time to datetime-SQL Server 2008

DECLARE @time time(4), @datetime datetime

SELECT @time = '10:11:12.1234', @datetime =

@time SELECT @time AS 'time', @datetime AS 'datetime'

------Convert from date to datetime-SQL Server 2008

DECLARE @date date, @datetime datetime

SELECT @date = '01-11-10', @datetime = @date

SELECT @date AS 'date', @datetime AS 'datetime'

------Convert from datetime2 to datetime-SQL Server 2008

DECLARE @datetime2 datetime2, @datetime datetime

SELECT @datetime2 = GETDATE(), @datetime =

@datetime2 SELECT @datetime AS 'datetime',

@datetime2 AS 'datetime2'

Sample Queries

------ Given ------------------

emp_no emp_name supervisor_no

————————————————-

1 vinay 3

2 kamesh 1

3 muhu NULL

NULL NULL NULL

This is my table datas.

I want the result to be in this format;

for vinay the suprevisor is muhu which is from the same table…

emp_name supervisor_name

——————————————

vinay muhu

kamesh vinay

muhu NULL

*/

Select * From Emptable

Select a.EMP_Name,b.Supervisor_Name From

(Select Emp_no as SUP_No,Emp_Name as Supervisor_Name From EmpTable Where Emp_No in (Select Distinct SUP_NO From EmpTable)) as b,

EmpTable as a

where a.SUP_NO=b.SUP_NO and a.Emp_Name not in ('null')

--///////////////////////Another Way///////////////////////////////-----------

SELECT e.emp_name emp_name, s.emp_name supervisor_name

FROM EmpTable e

LEFT OUTER JOIN EmpTable s

ON s.emp_no=e.sup_no

Where e.Emp_Name not in ('null')

Delete Duplicate Values in SQL

/*

Create table TempTable

(

ID INT,

FName nvarchar(50),

LName nvarchar(50)

)

GO

INSERT TempTable Values ('1','XYZ','PQR')

INSERT TempTable Values ('1','XYZ','PQR')

INSERT TempTable Values ('1','XYZ','PQR')

INSERT TempTable Values ('2','ABC','DEF')

INSERT TempTable Values ('2','ABC','DEF')

INSERT TempTable Values ('2','ABC','DEF')

GO

SELECT * FROM TempTable

SELECT ID,

COUNT(ID) AS NumOccurrences

FROM TempTable

GROUP BY ID

HAVING ( COUNT(ID) > 1 )

Delete from TempTable

/*

-------------//////////////Another Way///////////---------------

SELECT * FROM TempTable

SELECT ID,

COUNT(ID) AS NumOccurrences

INTO holdkey

FROM TempTable

GROUP BY ID

HAVING ( COUNT(ID) > 1 )

SELECT DISTINCT TempTable.*

INTO holddups

FROM TempTable, holdkey

WHERE TempTable.ID = holdkey.ID

SELECT ID, count(*)

FROM holddups

GROUP BY ID

DELETE TempTable

FROM TempTable, holdkey

WHERE TempTable.ID = holdkey.ID

INSERT TempTable SELECT * FROM holddups

*/

/*

-------------//////////////Another Way///////////---------------

Select * From TempTable

set rowcount 1

select 'start'

while @@rowcount > 0

delete a from TempTable a where (select count(*) from TempTable b

where a.ID = b.ID)>1

set rowcount 0

select * from TempTable

set nocount off

*/

Get Database Size

GO

ALTER PROCEDURE usp_Sizing

@Granularity VARCHAR(10) = NULL,

@Database_Name sysname = NULL

AS

DECLARE @SQL VARCHAR(5000)

IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = '##Results')

BEGIN

DROP TABLE ##Results

END

CREATE TABLE ##Results ([Database Name] sysname, [File Name] sysname, [Physical Name] NVARCHAR(260),

[File Type] VARCHAR(4), [Total Size in Mb] INT, [Available Space in Mb] INT, [Growth Units] VARCHAR(15), [Max File Size in Mb] INT)

SELECT @SQL =

'USE [?] INSERT INTO ##Results([Database Name], [File Name], [Physical Name],

[File Type], [Total Size in Mb], [Available Space in Mb],

[Growth Units], [Max File Size in Mb])

SELECT DB_NAME(),

[name] AS [File Name],

physical_name AS [Physical Name],

[File Type] =

CASE type

WHEN 0 THEN ''Data'''

+

'WHEN 1 THEN ''Log'''

+

'END,

[Total Size in Mb] =

CASE ceiling([size]/128)

WHEN 0 THEN 1

ELSE ceiling([size]/128)

END,

[Available Space in Mb] =

CASE ceiling([size]/128)

WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)

ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)

END,

[Growth Units] =

CASE [is_percent_growth]

WHEN 1 THEN CAST(growth AS varchar(20)) + ''%'''

+

'ELSE CAST(growth*8/1024 AS varchar(20)) + ''Mb'''

+

'END,

[Max File Size in Mb] =

CASE [max_size]

WHEN -1 THEN NULL

WHEN 268435456 THEN NULL

ELSE [max_size]

END

FROM sys.database_files

ORDER BY [File Type], [file_id]'

--Print the command to be issued against all databases

PRINT @SQL

--Run the command against each database

EXEC sp_MSforeachdb @SQL

--UPDATE ##Results SET [Free Space %] = [Available Space in Mb]/[Total Size in Mb] * 100

--Return the Results

--If @Database_Name is NULL:

IF @Database_Name IS NULL

BEGIN

IF @Granularity = 'Database'

BEGIN

SELECT

T.[Database Name],

T.[Total Size in Mb] AS [DB Size (Mb)],

T.[Available Space in Mb] AS [DB Free (Mb)],

T.[Consumed Space in Mb] AS [DB Used (Mb)],

D.[Total Size in Mb] AS [Data Size (Mb)],

D.[Available Space in Mb] AS [Data Free (Mb)],

D.[Consumed Space in Mb] AS [Data Used (Mb)],

CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1)) / D.[Total Size in Mb]*100) AS [Data Free %],

L.[Total Size in Mb] AS [Log Size (Mb)],

L.[Available Space in Mb] AS [Log Free (Mb)],

L.[Consumed Space in Mb] AS [Log Used (Mb)],

CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1)) / L.[Total Size in Mb]*100) AS [Log Free %]

FROM

(

SELECT [Database Name],

SUM([Total Size in Mb]) AS [Total Size in Mb],

SUM([Available Space in Mb]) AS [Available Space in Mb],

SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]

FROM ##Results

GROUP BY [Database Name]

) AS T

INNER JOIN

(

SELECT [Database Name],

SUM([Total Size in Mb]) AS [Total Size in Mb],

SUM([Available Space in Mb]) AS [Available Space in Mb],

SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]

FROM ##Results

WHERE ##Results.[File Type] = 'Data'

GROUP BY [Database Name]

) AS D ON T.[Database Name] = D.[Databasse Name]

INNER JOIN

(

SELECT [[Database Name],

SUM([Total Size in Mb]) AS [Total Size in Mb],

SUM([Available Space in Mb]) AS [Available Space in Mb],

SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]

FROM ##Results

WHERE ##Results.[File Type] = 'Log'

GROUP BY [Database Name]

) AS L ON T.[Database Name] = L.[Database Name]

ORDER BY D.[Database Name]

END

ELSE

BEGIN

SELECT [Database Name],

[File Name],

[Physical Name],

[File Type],

[Total Size in Mb] AS [DB Size (Mb)],

[Available Space in Mb] AS [DB Free (Mb)],

CEILING(CAST([Available Space in Mb] AS decimal(10,1)) / [Total Size in Mb]*100) AS [Free Space %],

[Growth Units],

[Max File Size in Mb] AS [Grow Max Size (Mb)]

FROM ##Results

END

END

--Return the Results

--If @Database_Name is provided

ELSE

BEGIN

IF @Granularity = 'Database'

BEGIN

SELECT

T.[Database Name],

T.[Total Size in Mb] AS [DB Size (Mb)],

T.[Available Space in Mb] AS [DB Free (Mb)],

T.[Consumed Space in Mb] AS [DB Used (Mb)],

D.[Total Size in Mb] AS [Data Size (Mb)],

D.[Available Space in Mb] AS [Data Free (Mb)],

D.[Consumed Space in Mb] AS [Data Used (Mb)],

CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1)) / D.[Total Size in Mb]*100) AS [Data Free %],

L.[Total Size in Mb] AS [Log Size (Mb)],

L.[Available Space in Mb] AS [Log Free (Mb)],

L.[Consumed Space in Mb] AS [Log Used (Mb)],

CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1)) / L.[Total Size in Mb]*100) AS [Log Free %]

FROM

(

SELECT [Database Name],

SUM([Total Size in Mb]) AS [Total Size in Mb],

SUM([Available Space in Mb]) AS [Available Space in Mb],

SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]

FROM ##Results

WHERE [Database Name] = @Database_Name

GROUP BY [Database Name]

) AS T

INNER JOIN

(

SELECT [Database Name],

SUM([Total Size in Mb]) AS [Total Size in Mb],

SUM([Available Space in Mb]) AS [Available Space in Mb],

SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]

FROM ##Results

WHERE ##Results.[File Type] = 'Data'

AND [Database Name] = @Database_Name

GROUP BY [Database Name]

) AS D ON T.[Database Name] = D.[Database Name]

INNER JOIN

(

SELECT [Database Name],

SUM([Total Size in Mb]) AS [Total Size in Mb],

SUM([Available Space in Mb]) AS [Available Space in Mb],

SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]

FROM ##Results

WHERE ##Results.[File Type] = 'Log'

AND [Database Name] = @Database_Name

GROUP BY [Database Name]

) AS L ON T.[Database Name] = L.[Database Name]

ORDER BY D.[Database Name]

END

ELSE

BEGIN

SELECT [Database Name],

[File Name],

[Physical Name],

[File Type],

[Total Size in Mb] AS [DB Size (Mb)],

[Available Space in Mb] AS [DB Free (Mb)],

CEILING(CAST([Available Space in Mb] AS decimal(10,1)) / [Total Size in Mb]*100) AS [Free Space %],

[Growth Units],

[Max File Size in Mb] AS [Grow Max Size (Mb)]

FROM ##Results

WHERE [Database Name] = @Database_Name

END

END

DROP TABLE ##Results

--EXEC usp_Sizing

Get Alpha, Numeric Values

CREATE FUNCTION dbo.UDF_ParseAlphaChars

(

@string VARCHAR(8000)

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @IncorrectCharLoc SMALLINT

SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)

WHILE @IncorrectCharLoc > 0

BEGIN

SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')

SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)

END

SET @string = @string

RETURN @string

END

GO

--Test

SELECT dbo.UDF_ParseAlphaChars('ABC”_I+{D[]}4|:e;””5,<.F>/?6')

GO

GET VALUES IN CSV FORMAT

SELECT * FROM ORDERS

GO

--TOTAL NUMBER OF ROWS IN ORDERS TABLE

SELECT COUNT(*) TOTALROWS FROM ORDERS

GO

--TOTAL COUNT OF ROWS GROUPED BY P_ID

SELECT COUNT(*)CNT,P_ID FROM ORDERS

GROUP BY P_ID

ORDER BY P_ID

GO

--EXAMPLE OF TOP 2 RECORDS

SELECT TOP 2 * FROM ORDERS

ORDER BY P_ID

GO

--EXAMPLE OF TOP 2 WITH TIES

SELECT TOP 2 WITH TIES * FROM ORDERS

ORDER BY P_ID

GO

GO

--CHECK TABLE COLUMN

SELECT LASTNAME FROM PERSONS

GO

--GET CSV VALUES

SELECT STUFF((SELECT ','+P.LASTNAME FROM PERSONS AS P

ORDER BY P.LASTNAME

FOR XML PATH('')),1,1,'')AS CSV

GO

VLOOKUP() and ISNA() Functions

Use VLOOKUP(), ISNA(), IF() Functions

=IF(ISNA(VLOOKUP(1,Sheet1!C5:E5,3,FALSE)),0,VLOOKUP(1,Sheet1!C5:E5,3,FALSE))

=IF(Sheet1!C5=2,(VLOOKUP(2,Sheet1!C5:E5,3,FALSE)),0)

Duplicate Values

Testing A List For Duplicate Items

The formula below will display the words "Duplicates" or "No Duplicates" indicating whether there are duplicates elements in the list A2:A11.

=IF(MAX(COUNTIF(A2:A11,A2:A11))>1,"Duplicates","No Duplicates")

An alternative formula, one that will work with blank cells in the range, is shown below. Note that the entire formula should be entered in Excel on one line.

=IF(MAX(COUNTIF(INDIRECT("A2:A"&(MAX((A2:A11<>"")*ROW(A2:A11)))),
INDIRECT("A2:A"&(MAX((A2:A11<>"")*ROW(A2:A11))))))>1,"Duplicates","No Duplicates")

Highlighting Duplicate Entries

You can use Excel's Conditional Formatting tool to highlight duplicate entries in a list. All of the examples in this section assume that the data to be tested and highlighted is in the range B2:B11. You should change the cell references to the appropriate values on your worksheet.

This first example will highlight duplicate rows in the range B2:B11. Select the cells that you wish to test and format, B2:B11 in this example. Then, open the Conditional Formatting dialog from the Format menu, change Cell Value Is to Formula Is, enter the formula below, and choose a font or background format to apply to cells that are duplicates.

=COUNTIF($B$2:$B$11,B2)>1

The formula above, when used in Conditional Formatting, will highlight all duplicates. That is, if the value 'abc' occurs twice in the list, both instances of 'abc' will be highlighted. This is shown in the image to the left, in which all occurrences of 'a' and 'g' are higlighted.

You can use the following formula in Conditional Formatting to highlight only the first occurrence of an entry in the list. For example, the first occurrence of 'abc' will be highlighted, but the second and subsequent occurrences of 'abc' will not be highlighted.

=IF(COUNTIF($B$2:$B$11,B2)=1,FALSE,COUNTIF($B$2:B2,B2)=1)

This is shown at the left where only the first occurrences of the duplicate items 'a', 'e', and 'g' are highlighted. The second and subsequent occurrences of these values are not highlighted.

You can also do the reverse of this with Conditional Formatting. Using the formula below in Conditional Formatting will highlight only the second and subsequent occurrences of a value. The first occurrence of the value will not be highlighted.

=IF(COUNTIF($B$2:$B$11,B2)=1,FALSE,NOT(COUNTIF($B$2:B2,B2)=1))

This is shown at the left where only the second occurrences of 'a', 'b', 'c' and 'f' are highlighted. The first occurrences of these items are not highlighted.

Another formula for Conditional Formatting will highlight only the last occurrence of a duplicate element in a list (or the element itself if it occurs only once).

=IF(COUNTIF($B$2:$B$11,B2)=1,TRUE,COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$11,B2))

As you can see only the last occurrences of elements 'a', 'b', 'c', and 'f' are highlighted. Element 'd' is highlighted because it occurs only once. The occurrences of 'a', 'b', 'c' and 'f' that occurs before the last occurrence are not highlighted.

We can round out our discussion of highlighting duplicate rows with two additional formula related to distinct items in a list.

The following can be used in Conditional Formatting to highlight elements that occur only once in the range B2:B11.

=COUNTIF($B$2:$B$11,B2)=1

This image illustrates the formula. Elements 'b', 'c', and 'e' are highlighted because they occur only once in the list. Items 'a', 'd' and 'f' are not highlighted because they occur more than one time in the list.

Finally, the following formula can be used in Conditional Formatting to highlight the distinct values in B2:B11. If an element occurs once, it is highlighted. If it occurs more then once, then only the first occurrence is highlighted.

=COUNTIF($B$2:B2,B2)=1

As you can see, only the first or only occurrences of the elements are highlighted. If an element is duplicated, as is 'b', the duplicate elements are not highlighted.

Counting Distinct Entries In A Range

The following formulas will return the number of distinct items in the range B2:B11. Remember, all of these are array formulas.

The following formula is the longest but most flexible. It will properly count a list that contains a mix of numbers, text strings, and blank cells.

=SUM(IF(FREQUENCY(IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""), IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""))>0,1))

If your data does not have any blank entries, you can use the simpler formula below.

=SUM(1/COUNTIF(B2:B11,B2:B11))

If your data has only numeric values or blank cells (no string text entries), you can use the following formula:

=SUM(N(FREQUENCY(B2:B11,B2:B11)>0))

Excel Compare Two Column Values

Excel compare two column values:

-----------------------------------

Hopefully this is an easy one for you. I have two columns of alphanumeric data that I want to compare. The data in each column is a list of computer names. I want to know what computers names in column A are NOT in column B. Can you assist? The output can be either in column C or in a separate worksheet. Thanks in advance for any help you can provide.

Answer

In cell C1, enter the formula:

=IF(ISERROR(MATCH(A1,B:B,0)),A1,"")

and copy it down col C as far as your data goes. Calculate, and Col C will display all the computer names that appear in Col A but not Col B. (If desired, you can use sort to bring the Col C results to the top, or use advanced filter to extract the unique values of Col C elsewhere.)

**************************************************************

=A$1<>B$1 or =(A$1:A$10)<>B$1

***************************************************************

One way is to use conditional formatting.

Suppose the first column is A and the second is B.

Select A1 and use

Format >> Conditional formatting >> Formula is

Then put for the formula

=COUNTIF(B:B, A1)=0

Then click

Format >> Patterns

And choose a background color of your choice, followed by clicking OK and

OK again.

Finally, with A1 still selected, click the paintbrush and then spread the

paint down the list of values in column A; this paints the conditional

formatting onto those cells.

****************************************************************************

=if(A1<>B1,1,"")

*************************************************************

Get alphanumeric values only

CREATE FUNCTION dbo.UDF_ParseAlphaChars

(

@string VARCHAR(8000)

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @IncorrectCharLoc SMALLINT

SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)

WHILE @IncorrectCharLoc > 0

BEGIN

SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')

SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)

END

SET @string = @string

RETURN @string

END

GO

--Test

SELECT dbo.UDF_ParseAlphaChars('ABC”_I+{D[]}4|:e;””5,<.F>/?6')

GO

Get CSV Format From Table

SELECT * FROM ORDERS

GO

--TOTAL NUMBER OF ROWS IN ORDERS TABLE

SELECT COUNT(*) TOTALROWS FROM ORDERS

GO

--TOTAL COUNT OF ROWS GROUPED BY P_ID

SELECT COUNT(*)CNT,P_ID FROM ORDERS

GROUP BY P_ID

ORDER BY P_ID

GO

--EXAMPLE OF TOP 2 RECORDS

SELECT TOP 2 * FROM ORDERS

ORDER BY P_ID

GO

--EXAMPLE OF TOP 2 WITH TIES

SELECT TOP 2 WITH TIES * FROM ORDERS

ORDER BY P_ID

GO

GO

--CHECK TABLE COLUMN

SELECT LASTNAME FROM PERSONS

GO

--GET CSV VALUES

SELECT STUFF((SELECT ','+P.LASTNAME FROM PERSONS AS P

ORDER BY P.LASTNAME

FOR XML PATH('')),1,1,'')AS CSV

GO

Roll Back Transactions

/*ROLL BACK TRANSACTIONS*/

GO

--CREATE TABLE AND INSERT SINGLE ROW

CREATE TABLE #TEMPTABLE (COL1 VARCHAR(100))

INSERT INTO #TEMPTABLE (COL1)

VALUES ('TEMP DATA - 01')

SELECT * FROM #TEMPTABLE;

--SELECT ONE TABLE

SELECT * FROM PERSONS;

/*

INSERT ADDITIONAL ROW IN TRANS

ROLLBACK TRANSACTION AT THE END

*/

BEGIN TRAN

--INSERT SINGLE ROW

INSERT INTO #TEMPTABLE (COL1)

VALUES ('TEMP DATA - 02')

--INSERT SINGLE ROW

INSERT INTO PERSONS (P_ID,LASTNAME,FIRSTNAME,ADDRESS,CITY)

VALUES (6,'KUMAR','RAM','NORTH GATE','MADURAI')

--CHECK THE VALUES IN TABLE AFTER TRANSACTION

SELECT * FROM #TEMPTABLE;

SELECT * FROM PERSONS;

ROLLBACK

--CHECK THE VALUES IN TABLE AFTER ROLLBACK THE TRANSACTION

SELECT * FROM #TEMPTABLE

SELECT * FROM PERSONS;

GO

--CLEAN UP

DROP TABLE #TEMPTABLE

GO

Insert Bulk Record

INSERT INTO DBNAME.DBO.PRODUCTS (PROD_ID,PRODUCTNAME,UNIT,UNITPRICE,ACTIVE)

SELECT '9','PRODUCT6','1000','12.25','1'

UNION ALL

SELECT '10','PRODUCT7','1000','12.13','1'

UNION ALL

SELECT '11','PRODUCT8','1000','12.14','1'

UNION ALL

SELECT '12','PRODUCT9','1000','12.11','1'

UNION ALL

SELECT '13','PRODUCT10','1000','12.10','1'

-------------------------------------------------------------

BULK

INSERT DBNAME.DBO.PRODUCTS

FROM 'E:\INSERT.TXT'

WITH

(

FIELDTERMINATOR=',',

ROWTERMINATOR='\n'

)

BULK

INSERT DBNAME.DBO.PRODUCTS

FROM 'E:\INSERT.CSV'

WITH

(

FIELDTERMINATOR=',',

ROWTERMINATOR='\n'

)

GO

DB Backup

SELECT P_ID,LASTNAME,FIRSTNAME,CITY

INTO NEWDB.DBO.PERSONS_BACKUP

FROM PERSONS

BACKUP DATABASE DBNAME TO DISK='E:\DBNAME.BAK'

DROP DATABASE DBNAME

RESTORE DATABASE DBNAME FROM DISK='E:\DBNAME.BAK'

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'

RESTORE DATABASE [AdventureWorks]

FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',

DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',

DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'

MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks.bak'

WITH FORMAT

BACKUP DATABASE DBNAME

TO DISK='E:\DBNAME.BAK',

DISK='F:\DBNAME.BAK'