x

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!

more ▼

asked Apr 28, 2010 at 11:33 AM in Default

Ron 3 gravatar image

Ron 3
97 5 5 6

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Apr 28, 2010 at 11:50 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

Perfect, that is what I needed to know!
Apr 28, 2010 at 11:54 AM Ron 3
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 28, 2010 at 11:48 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

g'ah blue bar - beaten by seconds!
Apr 28, 2010 at 11:55 AM Fatherjack ♦♦
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.
Apr 28, 2010 at 11:55 AM Ron 3
Yup.. This is what i used.
Apr 28, 2010 at 12:03 PM Katie 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1943
x85
x60

asked: Apr 28, 2010 at 11:33 AM

Seen: 2093 times

Last Updated: Apr 28, 2010 at 11:33 AM