Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Marketware
Starting Member
9 Posts |
 Posted - 2010-04-16 : 08:05:12
|
| I am trying to attach 2 sample databases as part of the installation of our software. When I do, I get an access denied error (OS error 5) and It won't attach.Is there a way to guarantee that the mdf files have the proper rights when they are installed onto a new users PC? (i.e., what user group or whatever can I set the files to on my machine prior to copying them onto another's PC).The attachement code I've written works fine on my PC, but when I try to install on other's PCs I get this access denied 5 error.Thanks!! |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-16 : 08:11:31
|
| you should take a backup and restore the backup to the other machine rather than trying to port across the data filesCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Marketware
Starting Member
9 Posts |
Posted - 2010-04-16 : 08:41:14
|
| Sounds good, but can you briefly help me understand why? I need to learn. Thank you!!! |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-16 : 10:26:51
|
| I'm assuming that you are using the simple recovery model.when you take a backup then you end up with (typically) one file which is transactionally consistent (it contains all you need to restore to the point in time you made the backup).You would then take the backup file and RESTORE it to the new server. you can give the database a new name if required.When you simply move the mdf files then you are not transactionally complete. Even if you were to stop the sql service and then copy both the mdf and ldf files to the new computer -- attaching those files is a painful process. just restoring a bak file is much easier.check the documentation on BACKUP / RESTORE.http://msdn.microsoft.com/en-us/library/ms186865.aspxand here;s a good link for the process:http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backupsCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-16 : 10:32:39
|
| BACKUP file will be smaller too - which may benefit your installation process.You may make a "starter" database that is, say, 100MB large (to allow for space for user's data). The MDF file is now 100MB but is, in effect, "empty". Then you decide that the Log file should be, say, 50MB. So now you have two files, MDF and LDF, that are 150MB.If you make a Backup it will only be a few bytes (there is some systems information, all your table/DDL structure, and so on ... so maybe the Backup file will be a few 10's of MB).When you Restore from the backup file it will recreate the database at its original size - i.e. 100MB for the .MDF and 50MB for the .LDFYou can easily provide parameters to Restore to the appropriate folder/path for the .MDF and .LDF files to be created (i.e. it doesn't have to be the same folder / drive as the original files were on your DEV machine) and, as TC said, you can also choose a different Database Name during restore. |
|
|
|
Marketware
Starting Member
9 Posts |
Posted - 2010-04-16 : 11:18:02
|
| Thank you all for the information. I am rewriting my "attaching" code now and will give it a try.bob |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-16 : 11:21:19
|
| Detailed discussion of the various options / parameters for RESTORE here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300 |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2010-04-16 : 12:30:31
|
| Although backup and restore is good, detach/attach is much faster as per given in bol. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-16 : 13:26:27
|
Yes, I'm sure that's true. But is time an issue here? Download a 150MB install package and install "instantly" using ATTACH ... or download a 10MB package that uses RESTORE (and which takes a bit longer to install).I know which I would prefer!We used to name our database "OurCompany" and the files OurCompany.MDF / .LDF. When clients needed to send us their database to sort out normally used DETACH which was a PITA for us as all client-files were the same. I know that isn't the same as this issue, but given a BACKUP file to restore I could restore it to any suitable name that I liked  |
|
|
|
|
|
|
|
|
OS Access Denied Error when attempting to Attach