SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Backup / Restore devices
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ojoonline
Starting Member

4 Posts

Posted - 09/28/2006 :  20:42:29  Show Profile  Reply with Quote
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.html

I 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

USA
37287 Posts

Posted - 09/29/2006 :  01:13:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
Don't bother with a device. I haven't used one since SQL Server 6.5.

Just backup directly to the file:

BACKUP DATABASE DBName
TO DISK = 'F:\Backup\DBName.BAK'
WITH INIT

Then you can restore directly from that file:

RESTORE DATABASE DBName
FROM DISK = 'F:\Backup\DBName.BAK'
WITH REPLACE

But 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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/29/2006 :  01:13:40  Show Profile  Reply with Quote
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,Backup

Kristen
Go to Top of Page

ojoonline
Starting Member

4 Posts

Posted - 09/29/2006 :  01:19:28  Show Profile  Reply with Quote
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 DBName
TO DISK = 'F:\Backup\DBName.BAK'
WITH INIT

Do I just put that straight into the SQL Editor?

oooooooo - I hope so - that seems too easy :-) just the way i like it!

jo
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/29/2006 :  01:23:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
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.aspx

I 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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/29/2006 :  01:39:36  Show Profile  Reply with Quote
"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
Go to Top of Page

ojoonline
Starting Member

4 Posts

Posted - 09/29/2006 :  01:40:54  Show Profile  Reply with Quote
Hi Tara,

thanks. I just did a quick test and this is what happened:

BACKUP DATABASE ophelia
TO DISK = 'C:\backups\ophelia.BAK'
WITH INIT

got 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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/29/2006 :  01:45:22  Show Profile  Reply with Quote
"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
Go to Top of Page

ojoonline
Starting Member

4 Posts

Posted - 09/29/2006 :  01:53:36  Show Profile  Reply with Quote
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?
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/29/2006 :  07:14:24  Show Profile  Reply with Quote
"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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000