Backup Database Role\permission in SQL Server 2005

I have 20+ databases on the SQL Cluster. How can I give a user the permission to backup any database without giving sysadmin access ?
I dont want to make the user dbo on all databases either.BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
If not you could take advantage to EXECUTE AS in this case.|||thanks Satya. I was hoping in SQL 2005, may be they had come up with something better. The only problem with db_backupoperator approach is that every time you add a new database to the server, thethe id needs to be given access to that database.

Backup and Restore of the Cluster

Hello
I want backup my SQL Server Cluster (two nodes) and to restore it on other
two nodes on another site.
I need to know that procedure to test my DRP. I know that the best would to
have a geocluster but for now (budget limitations) I just want to be able to
restore the cluster.
I would like your help to point me documentation about how to do this
procedure or if someone has any experience about I would be glad to know.
Thanks in advance.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.org
Carlos hi,
You will find plenty of information in BOL. Search by planning for disaster
recovery
Also, read the following article and it's links
http://support.microsoft.com/default...b;en-us;307775
HTH
Andreas
"Carlos Eduardo Selonke de Souza" wrote:

> Hello
> I want backup my SQL Server Cluster (two nodes) and to restore it on other
> two nodes on another site.
> I need to know that procedure to test my DRP. I know that the best would to
> have a geocluster but for now (budget limitations) I just want to be able to
> restore the cluster.
> I would like your help to point me documentation about how to do this
> procedure or if someone has any experience about I would be glad to know.
> Thanks in advance.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
|||Hello Andreas
Thanks for you repply but Im looking for information about backup and
restore a VIRTUAL SQL Server running on a cluster to another VIRTUAL SQL
Server.
I am most interessed in how to restore the master and msdb database on the
new virtual server, since the user databases is pretty easy.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.org
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Carlos hi,
> You will find plenty of information in BOL. Search by planning for disaster
> recovery
> Also, read the following article and it's links
> http://support.microsoft.com/default...b;en-us;307775
> HTH
> Andreas
> "Carlos Eduardo Selonke de Souza" wrote:
|||Hi Carlos,
If you don't have a different instance name, the things that you have to do
are to create your logins to the new server with a DTS Package and after
restoring all your databases, except master, msdb, execute the
sp_change_users_login (Ref. BOL) to assign the roles. Then you will be ready
to check and follow the article below.
http://msdn.microsoft.com/library/de...kprst_4g4w.asp
Please, use them first in a test environment if applicable.
HTH
Andreas
"Carlos Eduardo Selonke de Souza" wrote:
[vbcol=seagreen]
> Hello Andreas
> Thanks for you repply but Im looking for information about backup and
> restore a VIRTUAL SQL Server running on a cluster to another VIRTUAL SQL
> Server.
> I am most interessed in how to restore the master and msdb database on the
> new virtual server, since the user databases is pretty easy.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
>
> "Andreas Mavrogenis" wrote:

Backup a cluster

I have a sql 2000 cluster act/pas - i want to attach a dlt to backup the
databases. Is there any docmentation for this? which server do i attach it
to etc?
Thanks
Lap
IMHO, backing up to tape directly is asking for trouble. I prefer to have a
file server nearby that all nodes back up to across the wire. I then take
that system and back it up to tape. You get better performance and faster
recovery.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Lapchien?" <ccarswell@.catchmyspameclipse.co.uk> wrote in message
news:HYadnZ2dnZ2_-hHEnZ2dnYS3kd6dnZ2dRVnyq52dnZ0@.eclipse.net.uk...
>I have a sql 2000 cluster act/pas - i want to attach a dlt to backup the
>databases. Is there any docmentation for this? which server do i attach
>it to etc?
> Thanks
> Lap
>

Backup / Restore databse with filegroups

I hope this is the right forum, if not sorry about that. On Friday I will be doing support with another orgnaization on a SQL 2000 cluster system. The database will be backed up , another team will do the major application upgrade, and I will be restoring the database back. From the SCN, there will be no database changes. I have been searchng for information on databse backups / filegroups.

My questions are:

1. The database has 3 filegroups, besides the primary. If I do a complete backup, will it also do the filegroups?

2. If not, how do I backup the filegroups?

3. Whne I resote the databse, how do I bring in the filegroups from the backup?

Any info or pointers to links is greatly appreciated. Have a good day.

Carl

Regular database backup will backup all the filegroups including primary unless you are taking the exclusive filegroup backup...

Check the Books online for Backup command...

When you restore full backup it will restore all the filegroups... if you are restoring to different drive you may need to use WITH MOVE option...

Again check Books Online for the correct syntax...

Backing up/restoring using hard disk or tape

Here is my situation:
I have a 2 node SQL 2000 enterprise cluster for which i need to backup
3 databases. Here is my plan. I've changed the recovery mode to Full
on all 3 databases and have performed complete backups. I've
scheduled a complete backup at midnight and differentials every hour
and t-logs every 15 minutes. As an alternate backup, I am backing up
to tape using Arcserve's SQL agent. The schedule for this is a
complete backup nightly, and differential every 4 hours. What I'm
unclear on is the truncation of t-logs. Do they auto truncate every
time a differential or complete backup is run? I'm worried that the
tape backup (when it runs) may contain some transactions that the SQL
backup to disk does not have and vice versa...is this true? I know
it simple recovery mode, the databases will auto truncate the t-log
after a backup but not sure what will happen or where to find the
setting for full recovery mode. I would assume that SQL would allow me
to create multiple backups of the same databases but I just cant find
out how/where...any help is greatly appreciated as we are deploying a
production server on Aug 31. TIA...
TonyA full/differential backup doesn't truncate the tran log.
Linchi
>--Original Message--
>Here is my situation:
>I have a 2 node SQL 2000 enterprise cluster for which i
need to backup
>3 databases. Here is my plan. I've changed the recovery
mode to Full
>on all 3 databases and have performed complete backups.
I've
>scheduled a complete backup at midnight and differentials
every hour
>and t-logs every 15 minutes. As an alternate backup, I
am backing up
>to tape using Arcserve's SQL agent. The schedule for
this is a
>complete backup nightly, and differential every 4 hours.
What I'm
>unclear on is the truncation of t-logs. Do they auto
truncate every
>time a differential or complete backup is run? I'm
worried that the
>tape backup (when it runs) may contain some transactions
that the SQL
>backup to disk does not have and vice versa...is this
true? I know
>it simple recovery mode, the databases will auto truncate
the t-log
>after a backup but not sure what will happen or where to
find the
>setting for full recovery mode. I would assume that SQL
would allow me
>to create multiple backups of the same databases but I
just cant find
>out how/where...any help is greatly appreciated as we
are deploying a
>production server on Aug 31. TIA...
>Tony
>.
>

backing up to a share from an sql 2000 cluster

Hi there,
Does any know a way of backing up to a share from an sql
2000 active/active cluster. I have no problems doing this
for stand alone installtions of sql 2000, but just can't
seem to get it to work from a cluster.
any help appreciated
Shaun
This shouldn't be an issue. What exactly is the problem you are having?
Can't get it to work isn't much of a clue<g>.
Andrew J. Kelly SQL MVP
"Shaun Buckley" <anonymous@.discussions.microsoft.com> wrote in message
news:4baf01c4805a$1f9113f0$a501280a@.phx.gbl...
> Hi there,
> Does any know a way of backing up to a share from an sql
> 2000 active/active cluster. I have no problems doing this
> for stand alone installtions of sql 2000, but just can't
> seem to get it to work from a cluster.
> any help appreciated
> Shaun
|||http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Shaun Buckley" <anonymous@.discussions.microsoft.com> wrote in message
news:4baf01c4805a$1f9113f0$a501280a@.phx.gbl...
> Hi there,
> Does any know a way of backing up to a share from an sql
> 2000 active/active cluster. I have no problems doing this
> for stand alone installtions of sql 2000, but just can't
> seem to get it to work from a cluster.
> any help appreciated
> Shaun