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.