| Author |
Topic |
|
ojoonline
Starting Member
4 Posts |
 Posted - 2006-09-28 : 20:42:29
|
| Hi,I am using EMS SQL Manager for SQL Server (www.sqlmanager.net). I am trying to learn how to do backups of my database.I am up to this step -> http://www.sqlmanager.net/products/mssql/manager/documentation/hs21640.htmlI don't think I actually understand what a device is, how it's created, where it resides, etc. I would like to backup my remote datasource to my local computer's harddrive.Can someone please explain how this 'device' works and how I should set up?Thank you so much for any help :-)jo |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-29 : 01:13:19
|
Don't bother with a device. I haven't used one since SQL Server 6.5. Just backup directly to the file:BACKUP DATABASE DBNameTO DISK = 'F:\Backup\DBName.BAK'WITH INITThen you can restore directly from that file:RESTORE DATABASE DBNameFROM DISK = 'F:\Backup\DBName.BAK'WITH REPLACEBut to answer your question about devices, here's the info from SQL Server Books Online 2000:quote:
A logical backup device is an alias, or common name, used to identify the physical backup device. The logical device name is stored permanently in the system tables within SQL Server. The advantage of using a logical backup device is that it can be simpler to refer to than a physical device name. For example, a logical device name could be Accounting_Backup, but the physical device would be C:\Backups\Accounting\Full.bak.When backing up or restoring a database, you can use either physical or logical backup device names interchangeably.
Tara Kizer |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-29 : 01:13:40
|
| A "Device" is basically an Alias for a physical file. I suppose it was originally defined that way because backups were to a Tape Device. Just add a device pointing to a suitable disk location.I notice that the documentation in your URL is confusing - it talks about ADD and CREATE device, yet there is no Create button. I flicked through a few pages and they are covering so many options, to cover all eventualities, that the whole thing is confusing (such as "Restarting an interrupted backup" and HAVING to choose which logical files you want to backup [I can;t imagine circumstances where a noob would do anything other than backup the whole lot!]) so I would have thought you are better off just using some SQL to do you backups - if you need some help with the syntax have a look at:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,BackupKristen |
|
|
|
ojoonline
Starting Member
4 Posts |
Posted - 2006-09-29 : 01:19:28
|
| Hi Kristen / tkizer,I'm pretty new to this... so, you're saying don't worry about the wizard and just use SQL...BACKUP DATABASE DBNameTO DISK = 'F:\Backup\DBName.BAK'WITH INITDo I just put that straight into the SQL Editor?oooooooo - I hope so - that seems too easy :-) just the way i like it!jo |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-29 : 01:23:09
|
| Well you should get familiar with the wizards too even if you don't plan to use them. And yes you can just put that straight into the query window to backup the database.Here are my alternatives to the wizards:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxI haven't posted my update to isp_Backup yet, so it doesn't support 2005. It has been finished and tested and is running in production, but haven't posted it to my blog yet. But it should give you an idea of how to do things via T-SQL and not through the wizards.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-29 : 01:39:36
|
| "that seems too easy"Hehehe ... well it may yet be: "I would like to backup my remote datasource to my local computer's harddrive" assuming the database is on a remote server getting it to backup directly to non-local disks, even if they are theoretically visible across the LAN/WAN, can be a challenge. But folk here can help with that, and that EMS Wizard isn't going to make that problem go away! ... try backing up to a disk local to the server that SQL is running on for a start.Kristen |
|
|
|
ojoonline
Starting Member
4 Posts |
Posted - 2006-09-29 : 01:40:54
|
| Hi Tara,thanks. I just did a quick test and this is what happened:BACKUP DATABASE opheliaTO DISK = 'C:\backups\ophelia.BAK'WITH INITgot this error:Cannot open backup device 'C:\backups\ophelia.BAK'. Device error or device off-line. See the SQL Server error log for more details.BACKUP DATABASE is terminating abnormally.(2.922 sec)why do you think? I made the file called ophelia.bak by saving a .txt file and renaming. Is that right? jo |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-29 : 01:45:22
|
| "I made the file called ophelia.bak"The file does not need to pre-exist.You made a file on your local drive, right?The TO DISK = 'C:\backups\ophelia.BAK'refers to the drive with regard to how the server sees it. Maybe there is no folder called "backups" on its C: drive?Your post probably crossed with my earlier one, see above for my suggestion to try a backup local to the server first.Kristen |
|
|
|
ojoonline
Starting Member
4 Posts |
Posted - 2006-09-29 : 01:53:36
|
| Hmmm... the database I'm trying to backup is on a remote server. And I am trying to backup to my local drive. Maybe that's the problem? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-29 : 07:14:24
|
| "Maybe that's the problem?"Yeah, that is what I said! : "... assuming the database is on a remote server getting it to backup directly to non-local disks, even if they are theoretically visible across the LAN/WAN, can be a challenge"So I recommend that you first try a backup on disks local to the server the database is on to make sure there are not problems that far, then we can tackle getting it onto your local disk.If you don't have enough permissions to run a backup at all, or something else fundamental, it would be better to sort that first.Kristen |
|
|
|
|
Backup / Restore devices