Voice of the DBA

Tag: T-SQL

Restoring a Copy Only Backup–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

There was a question posted recently at SQLServerCentral about whether a copy only backup could be restore with a transaction log backup from a database. I was positive this could, but decided I needed to repro and test for someone as there wasn’t a good BOL reference.

The Tests

Here’s what I did. First, I created a table in a database. I often do this and drop in messages to allow me to track the progress of backups and restores. This post follows my progress.

The Backups

Here’s my basic script:
```
CREATE TABLE logger(msg VARCHAR(200), msgdate DATETIME DEFAULT GETDATE())

INSERT logger (msg) SELECT 'pre full backup'
```
Next, I made a backup and added a message.
```
INSERT logger (msg) SELECT 'pre-log backup 1'
BACKUP LOG nba TO disk = 'nba_1.trn'
INSERT logger (msg) SELECT 'log backup 1 complete'
```
Once this is done, I’m in a state that I expect. A normal full backup, a normal log backup, and some data to help me track where I am.

Now let’s make a copy only backup.
```
INSERT logger (msg) SELECT 'pre copy-only backup '
BACKUP DATABASE nba TO DISK  = 'nba_copy.bak' WITH COPY_ONLY
INSERT logger (msg) SELECT 'copy-only backup complete'
```
This now means I have an open log sequence in the first log backup (post full backup) and a few log records since then. Some of these are inside the copy only backup.

Now let’s add more data and make a new, regular, log backup.
```
INSERT logger (msg) SELECT 'pre-log backup 2'
BACKUP LOG nba TO disk = 'nba_2.trn'
INSERT logger (msg) SELECT 'log backup 2 complete'
```
It’s at this point that I have this sequence:
- Full backup
- Log backup
- Copy-Only Full backup
- Log backup
The Restore

What I want to test is can I restore the Copy-Only backup and a log backup? I think I can, so let’s do that. First, restore from the copy-only backup, using the MOVE option.
```
USE [master]
RESTORE DATABASE [NBA2] 
FROM  DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\nba_copy.bak' 
WITH  FILE = 1,  
      MOVE N'NBA' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2.mdf',  
      MOVE N'NBA_log' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_log.ldf',  
      MOVE N'nba_mo_file1' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo',  
      MOVE N'nba_mo_file2' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo2'
,  NOUNLOAD,  STATS = 5
, NORECOVERY
```
Tip: Always use NORECOVERY

Now let’s try to restore the log.
```
RESTORE LOG NBA2 FROM DISK = 'nba_2.trn' WITH NORECOVERY

RESTORE DATABASE nba2 WITH RECOVERY
```
This works:

That should prove things. Let’s check the logger table.

That’s what we expect. The final message after log backup2 wasn’t captured in our backup files.

Copy Only Backups

What is a copy only backup? If we check the Copy-Only Backups page, we find that this is a regular backup in and of-itself, but it has the restriction that it cannot be used with differential backups. This also doesn’t change the differential bitmap, so that any differentials that are made ignore this backup and go back to include data changed since the last “normal” full backup.

SQLNewBlogger

Understanding backup and recovery is critical for a data professional. I’d say this is the most important skill, and it’s always worth writing about. Spend a few minutes reviewing scenarios and creating some posts like this to show you understand how the system works.
December 27, 2017
Renaming a Column–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things I rarely do is rename objects. There are good reasons to do so, but often the changes required in other objects and applications isn’t worth the hassle. This is one reason why it would be good to spend a few minutes in design and come up with good names from the beginning.

In any case, do you know how to do this? You could use SSMS and easily change the design of the table. Of course, SSMS might try to rebuild the table, which might not be what you want. Hopefully that’s not the case, though you should always get the script instead of just saving the change.

The code you’d like to see is a simple meta data change that uses sp_rename. In my case, I want to change Qty to Quantity. I’d use this code:

EXEC sp_rename @objname = ‘Sales.OrderLines.Qty’ ,
@newname = ‘Quantity’ ,
@objtype = ‘column’;

I wish we had a direct ALTER TABLE statement that worked here, or better yet, an ALTER TABLE that allowed the entire table code to be shown (that’s not coming), but I’m not holding onto any hope that Microsoft will change this.

If you’re a person that thinks you might need a temp table that you insert data into and then two renames of the tables, that’s not the best way. Simple meta data changes are always preferred.

SQLNewBlogger

I ran into this while helping someone test a change and thought this was a good, easy reminder of how to change names. You could show you understand this in a blog in five minutes.

December 11, 2017
ROLLing UP Totals–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was editing an article recently that talked about ROLLUP, and I wanted to play with this a bit more. I hadn’t used this much in my career, but it’s a handy operator that’s worth including in your toolkit.

ROLLUP is used to provide additional totals for your aggregates while using GROUP BY. Here’s a little example. I’ve got some data for American Football quaterbacks. In this case, I’ve extracted some stats for a few noteworthy players today. Here’s a sample:

I want to aggregate some data together. For example, let’s say that we want to look at the total touchdowns scored per year by these players in their first 5 years. That’s a simple query:

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
FROM dbo.QBStats AS qs
WHERE qs.CareerYear <= 5
GROUP BY qs.CareerYear

This gives me data like this:

However, when I look at this, I don’t have a total across all years. In my set, there are players that have played up to 18 seasons, and I want to know the aggregate number of TDs. I can get that by adding WITH ROLLUP. This is added

after the group by. However, I can also change this to GROUP BY ROLLUP and include the columns in parenthesis.

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
FROM dbo.QBStats AS qs
WHERE qs.CareerYear <= 5
GROUP BY ROLLUP (qs.CareerYear)

This is OK, but the NULL isn’t great. What can I do here? I can add an ISNULL or COALESCE to my query and get this:

Useful, but what about if I’m aggregating by individual players? I can add the player to the column list, but I also need to add the player to the ROLLUP (or GROUP BY) list as well. If I do that, I get this:

That’s quite a bit more data, and I now see NULL values in the Player name. What’s this?

These are the totals for that subgroup. In this case, the first NULL above, next to the 1, is the total for all players for season 1. I can use another trick to clean this up:

This now shows some data in an easier to understand fashion, with some totals. We can see for season 1 that 3 players really started their careers in a strong fashion and 3 didn’t.

At the end, however, we get a different result.

This is because we have handled the player name as a NULL for the second column, but not the season as a NULL. For this last row, out “Total” text comes from an ISNULL of the first column. Here we’d want to do this:

This is where COALESCE comes in handy, allowing us to account for multiple NULL values.

ROLLUP is a nice way to get totals for each of your GROUP BY columns. This can make some of your reporting easier, and perhaps even faster by having the totals calculated in the result set.

SQLNewBlogger

This was a fairly easy piece to write. It took longer to get data together and find a set that had two grouping elements and was interesting than write the queries. I also had to play with ROLLUP a bit to clean up the NULLs, which always made this seem like a useless operator.

I think I’d look to use this more if I had the chance for many reports, as summing totals in some client tools is a pain.

November 6, 2017
Basic FORMATting– #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw the addition of FORMAT() to the T-SQL language, but didn’t play with it much. Recently it appeared in some code, and decided to experiment a bit. I had assumed this was mainly for dates, but it’s a general format/culture function that handles numbers as well.

On the doc’s page, there are the basic description of the parameters, which are NVARCHAR(), so passing in VARCHAR() causes an implicit conversion. It shouldn’t be much, but there are already performance penalties (see Aaron Bertrand’s piece), so don’t add to the overhead.

One good thing to note is that if you pass in invalid formats or cultures, a NULL is returned. Since the format and culture strings aren’t completely intuitive, this might be a source of issues in your code.

This is a neat function, relying on CLR formatting rules. That means I can do fun things like:
```
DECLARE @i int = 5000;

SELECT FORMAT(@i, N'USD$#');
```
Which returns:
```
USD$5000
```
Or even:
```
DECLARE @i INT = 5000
;
SELECT  FORMAT(@i, N'# dahlahs')
;
GO
```
Which gives me:
```
5000 dahlahs
```
There are lots of formats, and certainly lots of nuances to numeric formatting strings. It’s worth reading up if you plan to use this, but again, beware of performance. I’d avoid using this if the data size is large, maybe more than a few hundred rows.

After all, the database server is a shared resource, and using this CPU to handle simple formatting may not be the best use of your system.
October 25, 2017