Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4

    Answered: What is the differance between a restore from Database and device

    Excuse the newbie question but I have been doing some testing and I have to restore my database several times a day. I noticed there is a option to restore from Database or Device. I always selected device. Today I was looking at the Database option and noticed it had the same date and time as my device backup. Is the Database backup option just a set of parameters saved from the last backup or restore? I looked around on the web but never found a clear description of how the restore from database differs from the restore from device.

  2. Best Answer
    Posted by MCrowley

    "The Device terminology dates way back to the Sybase days. All disks were considered "devices". This included devices that held databases. This changed back in version 7.0, when database files were created directly, and you did not need to muck around with devices (which were sized on a different scale, as well).

    The Backup device can be considered to be just a shortcut to a file, or a tape device. The beauty of it is, you can change your tape device, or the path to your backup files, and not have to touch all of your backup jobs to keep up.

    That said, no one really uses backup devices anymore, as third parties backup applications have filled in that space. I think Backup devices (outside the usage in the GUI) are actually deprecated."


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    The Device terminology dates way back to the Sybase days. All disks were considered "devices". This included devices that held databases. This changed back in version 7.0, when database files were created directly, and you did not need to muck around with devices (which were sized on a different scale, as well).

    The Backup device can be considered to be just a shortcut to a file, or a tape device. The beauty of it is, you can change your tape device, or the path to your backup files, and not have to touch all of your backup jobs to keep up.

    That said, no one really uses backup devices anymore, as third parties backup applications have filled in that space. I think Backup devices (outside the usage in the GUI) are actually deprecated.

  4. #3
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4
    Thanks, so what you are saying is the Database option is just a set of stored variables that points to the last backup? I use device all the time. Especially when I want to restore from a different region. Like taking the production backup and restoring it to test. I just realized the Database had the same date as the last backup of the current database. Makes sense now. Thanks Again

  5. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    Some fairly extensive backup information is saved in a set of tables in the msdb database. This is where the GUI goes to collect information about available backups. When you get some time, check out the following tables:


    backupset
    backupmediafamily
    restorehistory

    Here is how the two backup tables go together
    Code:
    select b.database_name, 
    	case b.type
    		when 'L' then 'Log'
    		when 'I' then 'Diff'
    		when 'D' then 'Full' end,
    	b.backup_start_date,
    	f.physical_Device_name
    from backupset b join
    	backupmediafamily f on b.media_set_id = f.media_set_id
    Restorehistory has come in handy in that I have occasionally had to prove that the test database refresh was in fact refreshed from the right source.

  6. #5
    Join Date
    Aug 2017
    Location
    United States
    Posts
    9

    What is the differance between a restore from Database and device

    If I have the following entries in my database and this is what i get using order by :
    1 null
    2 empty string
    3 Alpha
    4 Bravo
    5 Charlie

    But i actually want the below :
    3 Alpha
    4 Bravo
    5 Charlie
    1 null
    2

    How can I order the rows with names to the front when using ORDER BY?

  7. #6
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4
    I would use a union. The outer select allows you to order each query separately.
    SELECT * FROM
    (
    SELECT * FROM Table1 WHERE field1 <> NULL AND field1 <> "" ORDER BY field1 DESC
    UNOIN
    SELECT * FROM Table1 WHERE field1 = NULL OR field1 = "" ORDER BY field1 ASC
    )

Tags for this Thread

Posting Permissions

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