question

Ron 3 avatar image
Ron 3 asked

NTFS Permissions Using Backup/Restore

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!

sql-server-2005permissionsmigration
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

g'ah blue bar - beaten by seconds!
0 Likes 0 ·
I am moving the databases to a new server that will have the same name on the same domain. I am planning on going through this process( http://support.microsoft.com/kb/918992/) to take care of the logins.
0 Likes 0 ·
Yup.. This is what i used.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.