Backing Up DB & its Permissions

HI All,

I am currently in a situation where I have been fed to the wolves to say the least.

My task is to take the current SQL server 05 we have running on a drive array partioned as C & D and rebuild it.

I need to have the current database backed up, as well as the unique permissions that are currently present on the database.

I then have to remove the hard drives from the RAID array, and replace them with Brand new ones. The OS will then have to be reinstalled, followed by the nessary updates and finally with SQL server.

I then have to import the database back to the new server build w/ the data and the permissions all intact.

Anyone have any idea of how I can go about doing this?

Steve

The permissions on database level are stored inside the database so these will be present once you restore the database.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

When backing up my database and then having to "recompose" it on the new drive arrays, would it be best to copy the DB's by doing an attach / detach, or by simply doing a full backup and then restoring that backup on a clean SQL Server 05 build.

Thanks.

|||

Detach / attach is the easiest way. Restore has the advantage that your backup file will only be as big as the data that is in it.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

To me Detach/Attach or Backup/Restore come out to be about the same, the backup file is smaller, the Detach/Attach seems quicker (no Backup/Restore time). I use a 7zip either way if space or transfer time is an issue.

The main issue I've run into is you have to make sure the Securty IDs (master..syslogins sid) match on the source & destination servers, this isn't an issue for domain logins if you stay within the domain. It's always a concern with SQL logins. The permissions stored in the DB are tied to the user, & the user is tied to the server login via the sid. There are ways to sync them up afterwards, I've had mixed luck there.

HTH,

Roger

|||Because of the login issues that Roger brings up, in this case I would also back up the system databases and restore them on the new system. As long as the version of SQL is the same on both sides, this is the simplest way to make sure that all IDs line up.

Backing Up DB & its Permissions

HI All,

I am currently in a situation where I have been fed to the wolves to say the least.

My task is to take the current SQL server 05 we have running on a drive array partioned as C & D and rebuild it.

I need to have the current database backed up, as well as the unique permissions that are currently present on the database.

I then have to remove the hard drives from the RAID array, and replace them with Brand new ones. The OS will then have to be reinstalled, followed by the nessary updates and finally with SQL server.

I then have to import the database back to the new server build w/ the data and the permissions all intact.

Anyone have any idea of how I can go about doing this?

Steve

The permissions on database level are stored inside the database so these will be present once you restore the database.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

When backing up my database and then having to "recompose" it on the new drive arrays, would it be best to copy the DB's by doing an attach / detach, or by simply doing a full backup and then restoring that backup on a clean SQL Server 05 build.

Thanks.

|||

Detach / attach is the easiest way. Restore has the advantage that your backup file will only be as big as the data that is in it.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

To me Detach/Attach or Backup/Restore come out to be about the same, the backup file is smaller, the Detach/Attach seems quicker (no Backup/Restore time). I use a 7zip either way if space or transfer time is an issue.

The main issue I've run into is you have to make sure the Securty IDs (master..syslogins sid) match on the source & destination servers, this isn't an issue for domain logins if you stay within the domain. It's always a concern with SQL logins. The permissions stored in the DB are tied to the user, & the user is tied to the server login via the sid. There are ways to sync them up afterwards, I've had mixed luck there.

HTH,

Roger

|||Because of the login issues that Roger brings up, in this case I would also back up the system databases and restore them on the new system. As long as the version of SQL is the same on both sides, this is the simplest way to make sure that all IDs line up.sql

Backing Up DB & its Permissions

HI All,

I am currently in a situation where I have been fed to the wolves to say the least.

My task is to take the current SQL server 05 we have running on a drive array partioned as C & D and rebuild it.

I need to have the current database backed up, as well as the unique permissions that are currently present on the database.

I then have to remove the hard drives from the RAID array, and replace them with Brand new ones. The OS will then have to be reinstalled, followed by the nessary updates and finally with SQL server.

I then have to import the database back to the new server build w/ the data and the permissions all intact.

Anyone have any idea of how I can go about doing this?

Steve

The permissions on database level are stored inside the database so these will be present once you restore the database.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

When backing up my database and then having to "recompose" it on the new drive arrays, would it be best to copy the DB's by doing an attach / detach, or by simply doing a full backup and then restoring that backup on a clean SQL Server 05 build.

Thanks.

|||

Detach / attach is the easiest way. Restore has the advantage that your backup file will only be as big as the data that is in it.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

To me Detach/Attach or Backup/Restore come out to be about the same, the backup file is smaller, the Detach/Attach seems quicker (no Backup/Restore time). I use a 7zip either way if space or transfer time is an issue.

The main issue I've run into is you have to make sure the Securty IDs (master..syslogins sid) match on the source & destination servers, this isn't an issue for domain logins if you stay within the domain. It's always a concern with SQL logins. The permissions stored in the DB are tied to the user, & the user is tied to the server login via the sid. There are ways to sync them up afterwards, I've had mixed luck there.

HTH,

Roger

|||Because of the login issues that Roger brings up, in this case I would also back up the system databases and restore them on the new system. As long as the version of SQL is the same on both sides, this is the simplest way to make sure that all IDs line up.

Avoiding transaction logging - at least reducing it

I have an application that uses work tables within an SP to generate a recordset output to the caller as the result of a search operation. The SP does not update the main database, and the results, although sizeable, are not saved.

Clearly I do not need to log the updates to the work tables. They start out empty and the contents could be discarded.

Does SQL Server recognise this and not log the updates to work tables? Is there any way I can indicate that a table is not significant and need not be logged at all?

Would it help to put the work tables in a separate database?

Maybe it would not make much difference anyway?

Thanks in advance for any helpFortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.

A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !|||Originally posted by fadace
Fortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.

A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !

Huh? Well I'm glad you didn't become very specific about what you're talking about.

Veritant:

You say Search to return result set, then you say update to work table

SELECTS are not logged

Creating a result set on the fly, like

SELECT * INTO #temp FROM...is not logged

TRUNCATE TABLE or DROP TABLE is not logged..

If you have some examples of what you're doing and need help, post it and we'll look at it...