| Author |
Topic |
|
John T.
Posting Yak Master
112 Posts |
 Posted - 2003-06-11 : 12:52:57
|
| I am trying to use Enterprise Manager for the first time. I have connected and have an alias setup. I simply want to backup my db(tables and sprocs) to a cd-rw. When I open the tree to Backup and right click, the New Backup Device option is shaded and I can't access it. If I try going to Backup DataBase through DataBases folder, I eventually am told that I do not have permission to create a new backup device. Any help, thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-11 : 12:57:24
|
| You could be getting this error because you can't backup your database directly to CD. You'll need to back it up to disk first then copy it to CD.If you still get this error when trying to back it up to disk, then contact your DBA for permissions if this server isn't yours. If this server is yours, then you could either use the sa account to do this work or just simply add your account to the administrators group on the machine or add your account to the system administrators role in SQL Server.Tara |
 |
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-06-11 : 15:34:05
|
| Thanks for the reply. As I said, this is my first attempt to do anything with EM. All I have to go with is a "Dummy" book. Appropriate.According to it, you need to set up a backup device. They mention tape or disk. May I ask this? I need to make a backup of data accumulated on a daily basis. From a website. Should I backup to my hard drive and an external source? I can do searches but nothing beats advice from a forum like this.Thanks. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-11 : 15:39:21
|
| You don't need to set up a device - you can backup to a file which will be created.I rarly use enterprise manager - try it with sql from query analyser.backup database mydb to disk="c:\mydb.bak"use the with inti clause if you want to overwrite.From e-m you can backup to a file rather than device too.Read about backup database in bol (books on-line).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-11 : 15:41:23
|
| You do not need to use a backup device anymore (that was a requirement in SQL 6.5), but you still have the option of doing it that way. You can now backup your database directly to a file. Here is how to do it:Go to your database, click all tasks, click backup database, click add, in the file name field either specify a valid path, or hit the ... button to navigate your file system, then click ok. You can then specify whether or not to overwrite this file if it exists or append the backup to the file. I recommend overwriting it since if you append all of the time your file is going to pretty big. Also,If this is a production system, I would recommend backing up the database to disk first then backup the file to tape using tape software. This is typically how it is done.If you want to make a backup on a daily basis, then you have to schedule a job to do this. You can do the whole process by creating a maintenance plan. Or you could create a job that has the T-SQL statements for the BACKUP command. Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-11 : 15:42:58
|
| I agree with nr about doing it in Query Analyzer. You will learn much more by doing it that way. Enterprise Manager is an easy tool to use, but it doesn't really help learning SQL Server since everything is pretty much a wizard. I'll admit that I started with Enterprise Manager about 7 years ago, but I wish I had started with Query Analyzer since I would have learned so much more quicker.Tara |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-06-11 : 16:43:38
|
| Thanks very much. For all the help. When I get a permission denied to backup, is that something my remote host needs to correct? I assumed that if I can login to the db, I should be able to back it up.John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-11 : 16:47:20
|
quote:
When I get a permission denied to backup, is that something my remote host needs to correct?
So another company is hosting this SQL Server? If that's the case, then you are going to need to request permissions to do your work.quote:
I assumed that if I can login to the db, I should be able to back it up.
No, that's an inaccurate assumption. You must be a member of the db_owner fixed database role, a member of the db_backupoperator fixed database role, or a member of the sysadmin fixed server role.Tara |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-06-11 : 17:28:22
|
| Thanks very much. I appreciate it. |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-06-11 : 17:33:12
|
| Just thought of this. I know that EM is a tool that doesn't expire with the server 2000 download. How about query analyzer?Have a good evening. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-11 : 17:39:15
|
| >> doesn't expire with the server 2000 downloadNot sure what that means.They are both included in the client tools.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-06-11 : 18:21:25
|
| You answered it for me. The trial evaluation. The client tools remain after trial is over.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-11 : 18:40:20
|
| But what good are the tools if SQL Server isn't available anymore because it has expired? To use the tools, you need to connect to SQL Server.Tara |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-06-11 : 19:09:02
|
| I am sorry to be so behind here. For some reason, I thought that the client tools would allow you to do things like backups long after expiration of evaluation. Is there a less expensive alternative to making a db backup? This really throws the old proverbial monkey wrench at me. Thank you very much. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-11 : 19:16:33
|
| I think that you are missing the point. Client tools are tools to get into SQL Server. Databases are in SQL Server and not in Client Tools. What would you be backing up without SQL Server? You have NOTHING without SQL Server, no databases, no tables, no stored procedures, etc...Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-11 : 19:17:42
|
| And yes there is a less expensive way, it's called MSDE. You'll want to check MS' site for information on MSDE though.Tara |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-06-11 : 19:26:19
|
| I'll show my ignorance here, but aren't the db's and sprocs with my host's sql server. I am sure I will feel very ignorant after this, but should make a major learning of some kind. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-11 : 19:28:57
|
| Ok, if your databases are being hosted somewhere, then yes you can do everything that you need using the Client Tools, provided that you have the permissions to do "everything".Tara |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-06-11 : 19:40:54
|
| I don't know where you are Tara, but you may have heard my sigh of relief. Whew! |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-06-14 : 00:39:24
|
| I have been having, or should I say my host has been having problems. I finally am getting the chance to use EM. All Tasks to Backup Database. When I try backing up to a folder on my hard drive, I get a "successful backup" message box. But nothing is there. Anyone with any reason why? I have double checked the location and nothing is going there. And it happens in just a second or so. I don't have any data in about 8 tables but there are umpteen sprocs. I'm wondering about this.Thanks much. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-14 : 02:51:12
|
| When you make a database backup, the backup file is created on the server and not on your machine. Your backup file exists on the remote host server.Tara |
|
|
|
Next Page
|
First shot with EM