I am very new to being a Database admin, so please bear with me. :-)
I am looking into doing a backup/restore method in replacing my current MS SQL Server 2005 system.
Will using the backup/restore method carry over any necessary NTFS permissions? Is this something that I even need to worry about?
I am transferring over the shares from the old SQL server to the new, but I'm curious how the database files will be affected.
Thanks!
When a SQL Server has attached to database files (generally named with file extensions of .mdf, .ldf and .ndf) then they are locked for any access while the SQL service is running so, so long as the SQL Server service account has access to the file locations to make the obvious updates there is no need for anyone to have access to those directories and if they do they wont be able to get to the data as the file is locked. Obviously anyone with adequate server permissions to stop services can get past this.
When a database file is not attached to a SQL Server then the data therein is fully readable from notepad and you should therefore manage the locations and their permissions carefully. Obviously anyone with adequate server permissions to alter permissions can get past this.
Simply doing a backup/restore will not have any effect on NTFS permissions on either server and you should look to assign them as an individual step in your migration project
A database backup only stores the information directly associated with that database. If you restore the database to the same server, any connections between NT logins and the database will be restored. If you restore the database to a different server, you will probably have to reconnect logins using the ALTER LOGIN command.
