Ask The DBAs

Warm standby on SQL Server 2008 with log shipping from SQL Server 2005?

2010-08-12T00:33:33-07:00

Question

Can you create a warm standby database on SQL Server 2008 with log shipping from a SQL Server 2005 database?

Answer

Each version of SQL Server has a database version number; for SQL 2000 it is 8, SQL 2005: 9 and SQL 2008: 10. When a database backup that came from a lower SQL Server version is restored, SQL Server will run an upgrade process to bring that older database version up to the database version supported by the new instance. This process is run automatically as part of SQL Server’s recovery process. The recovery process itself is certain operations that need to happen to ensure the database is in a consistent state for user access (roll-forwards and roll-backs etc).

Placing a database in “Standby/Read Only” mode instructs SQL Server to recover the database after each restore, but also create a special standby file that contains information which will allow this recovery to be undone when it is time to do additional transaction logs restores.

However unfortunately, once a database has been upgraded it cannot be undone. This is why if you try and use the “Standby/Read Only” option when restoring a database of an older version SQL Server fails with:

Msg 3180, Level 16, State 1, Line 1
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This error message essentially says you cannot bring an older database version online in “Standby/Read Only” mode.

But you can restore transaction logs and log shipping from an older database version to a newer database version when you keep the database offline. This is because the recovery (and upgrade process) for the database is deferred until the point you do actually bring the database online. This means you can log ship from an older version of SQL Server to a newer version until the point you bring that database online for user access.

Hope this helps. All Answers provided are subject to our standard Answers Disclaimer.

Q) How do I add a root node and return columns as elements with FOR XML?

2010-07-11T15:05:54-07:00

Question

How do I add a root node and return columns as elements with FOR XML?

Answer

FOR XML is a TSQL option which can be added to the SELECT statement to return data in XML format. A simple example of this is:

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML AUTO

When combined with JOINs FOR XML can be used to return structured XML hierarchies, however we won’t go into those details here. Instead we will focus on the question. To add a root node simply add the ROOT(rootnodename) modifier to the FOR XML clause. For example:

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML AUTO,ROOT('Tables')

To change the Element tag name for each row you can use a table alias or change to using RAW or PATH mode. For example:

SELECT * FROM INFORMATION_SCHEMA.Tables as [Table] FOR XML AUTO, ROOT('Tables')
OR
SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML RAW ('Table'), ROOT('Tables')

And finally, if you want each column to be translated into as an element instead of being an attribute you add the ELEMENTS modifier. This is useful, for example, if you are outputting XML to be loaded into a DataSet via the DataSets ReadXML method. For example:

SELECT * FROM INFORMATION_SCHEMA.Tables as [Table] FOR XML AUTO, ELEMENTS, ROOT('Tables')
OR
SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML RAW ('Table'), ELEMENTS, ROOT('Tables')

Hope this helps. All Answers provided are subject to our standard Answers Disclaimer.

Q) Query that returns rows in date range or overlapping date range

2010-06-17T14:10:23-07:00

Question

I need a query returns all rows between two dates or rows that overlap the date range in any way.

Answer

SELECT *
FROM MyTable
WHERE
(
/*When the start date is before the range but the end date falls in or after the range*/
(StartDate < @StartDate AND EndDate >= @StartDate)
/*When the StartDate falls in the range. The end date can be in or out of the range*/
OR (StartDate >= @StartDate AND StartDate < @EndDate)

)

Hope this helps. All Answers provided are subject to our standard Answers Disclaimer.

Q) How in SQL Server can I get a date value without any time value?

2010-06-16T14:26:12-07:00

Question

How in SQL Server can I get a date value without any time value?

Answer

In SQL Server 2008 Microsoft introduced the DATE data type so you can simply do the following:

SELECT CAST(GETDATE() as DATE)

In SQL Server 2005 and below the DATETIME data type always retains a time component. If you are not interested in time then commonly you set this to 00:00:000. This will ensure sorting and aggregations on the “date” component are consistent. A quick and easy way to convert a datetime to a standardized date is to do the following:

SELECT CAST(CONVERT(VARCHAR(50),GETDATE(),112) as DATETIME)

This has the benefit in that it results in a proper date time value (rather than a string) and is also deterministic so can be used in indexed views etc.

All Answers provided are subject to our standard Answers Disclaimer.