Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Red face Unanswered: Is it new in 2k8?

    When doing some testing, I had to rename a device name for a database. Issued a normal device rename statement:
    Code:
    alter database test1 modify file (name=test1, newname=test1Data)
    After that did a normal backup:
    Code:
    backup database test1 to disk = N'<local_disk>' with init
    When I glanced at the output, I thought I lost it...The command completed successfully, but the device name CHANGED!!!
    Code:
    Processed 168 pages for database 'test1', file 'test1Dat' on file 1.
    Processed 2 pages for database 'test1', file 'test1l' on file 1.
    BACKUP DATABASE successfully processed 170 pages in 0.053 seconds (24.929 MB/sec).
    
    RESTORE FILELISTONLY reports the same "changed" name, - test1Dat. SSMS GUI also shows that truncated version.

    Not that I was doing it often, but I do not recall having this issue in earlier editions. Of course I'll check it tonight on my 2K5 instance, but I don't have 2K any more (nor do I want to spend time installing it just to test this ).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This may be new in SQL 2008. I just tried this with SQL 2005 (using SQL 2000 Query Analyzer), and I got the new name in full in the backup result message, and from RESTORE FILELISTONLY. Is "Test1l" really the name of the log device, too? Sadly, I do not have a 2008 box handy enough to test with. Maybe later this week, I can.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It's "test1l"...Oh, I see It's "L" at the end, as in "Lima".

    Here's a test script that I wrote to demonstrate to the local DBA (I think I wasted my time writing it for him, considering the face he had when I tried to explain it to him):

    Code:
    use master;
    if db_id(N'test1') is not null begin
       alter database test1 set single_user with rollback immediate;
       alter database test1 set restricted_user with rollback immediate;
       exec ('use test1;')
       use master;
       drop database test1;
    end
    go
    if db_id(N'test2') is not null begin
       alter database test2 set single_user with rollback immediate;
       alter database test2 set restricted_user with rollback immediate;
       exec ('use test2;')
       use master;
       drop database test2;
    end
    go
    create database test1
       on primary (
          name = 'test1d'
         ,filename = 'D:\SQLData\test1_Data.mdf'
       ) log on (
          name = 'test1l'
         ,filename = 'L:\SQLLogs\test1_Log.ldf'
       )
    go
    backup database test1 to disk = N'B:\DBBackups\test1.bak' with init
    -- Output correctly shows the device names
    go
    restore filelistonly from disk = N'B:\DBBackups\test1.bak'
    -- Output correctly shows the device names
    go
    restore database test2
       from disk = N'B:\DBBackups\test1.bak'
       with replace
         ,move N'test1d' to N'D:\SQLData\test2_Data.mdf'
         ,move N'test1l' to N'L:\SQLLogs\test2_Log.ldf'
    -- No errors in the output
    go
    -- Now we're going to rebuild our environemtn by dropping the databases
    if db_id(N'test1') is not null begin
       alter database test1 set single_user with rollback immediate;
       alter database test1 set restricted_user with rollback immediate;
       exec ('use test1;')
       use master;
       drop database test1;
    end
    go
    if db_id(N'test2') is not null begin
       alter database test2 set single_user with rollback immediate;
       alter database test2 set restricted_user with rollback immediate;
       exec ('use test2;')
       use master;
       drop database test2;
    end
    go
    create database test1
       on primary (
          name = 'test1d'
         ,filename = 'D:\SQLData\test1_Data.mdf'
       ) log on (
          name = 'test1l'
         ,filename = 'L:\SQLLogs\test1_Log.ldf'
       )
    go
    -- Now we rename the logical device names.  Only 1 rename is needed to demonstrate the anomaly,
    -- but we'll rename both to prove the point:
    alter database test1 modify file (name = test1d, newname = test1Data)
    go
    alter database test1 modify file (name = test1l, newname = test1Log)
    go
    -- Let's look at sys.master_files:
    select * from sys.master_files
       where db_name(database_id) = 'test1'
    -- NAME column that contains the logical device name is correctly showing the new device names: test1Data and test1Log.
    go
    -- Now let's backup WITH INIT.  I also tried to delete the backup file before, to fully isolate the problem,
    -- but the final result (see below) was the same.  So just backing up WITH INIT will suffice.
    backup database test1 to disk = N'B:\DBBackups\test1.bak' with init
    go
    -- Notice the output from the BACKUP statement:
    /*
    Processed 168 pages for database 'test1', file 'test1Dat' on file 1.
    Processed 2 pages for database 'test1', file 'test1Lo' on file 1.
    BACKUP DATABASE successfully processed 170 pages in 0.114 seconds (11.590 MB/sec).
    */
    -- Let's check if RESTORE FILELISTONLY shows anything different:
    restore filelistonly from disk = N'B:\DBBackups\test1.bak'
    go
    -- Logical names for both devices are showing as truncated, just like in the BACKUP output above!!!
    -- From now on, any attempt to use test1Data and/or test1Log in any RESTORE operations will fail.
    -- You will have to use the truncated version for logical device names.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I know. Not the font for telling the difference between 1, l and I.

    If you were to move a file in a restore, do you really need to use the truncated name? That would be a sizeable problem in the system.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by MCrowley View Post
    ...If you were to move a file in a restore, do you really need to use the truncated name?..
    You don't have a choice. When you use MOVE, you have to specify the logical device name (BOL):
    Code:
    --Restore Operation Options
       MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' 
              [ ,...n ]
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I know you need to use the logical name of the device, I just don't see it in your example. At least after the rename, I don't see it.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    -- The command below will fail, because the backup device
    -- contains the truncated names for both data and log
    -- devices, while the database that was used to take
    -- the backup still has the renamed (test1Data and test1Log)
    -- devices, which can be seen by viewing output from sys.master_files.
    restore database test2
       from disk = N'B:\DBBackups\test1.bak'
       with replace
         ,move N'test1Data' to N'D:\SQLData\test2_Data.mdf'
         ,move N'test1Log'  to N'L:\SQLLogs\test2_Log.ldf'
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...Interestingly, I decided to rename the devices to 'd' and 'l' respectively. The BACKUP operation completely wiped out the names, and now SSMS is totally confused :-)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •