Subquestioning [2]
While copying Resource.mdf [1], I noticed that:
1)
It is possible to copy Resource.mdf without stopping SQL Server instance (I attached one having copied from running instance and it works after attaching) .
1a)
Should I understand that it as general possibility for all read-only databases or is it only in some versions (mine is 2008 R2 on Windows XP Pro Sp3)?
1b)
Conceptually, I cannot understand why a developing (Dev Ed) SQL Server on isolated/workgroup desktop Windows (in my case - XP Pro Sp3) should be stopped in order to copy a user-defined non-used (for ex., sample database) database.
1c)
When one should copy .LDF together with .MDF file and when copying .LDF can be skipped?
Why does read-only database have necessarily (transaction log? as I understood) file LDF?
2)
Resource.mdf (in my SQL Server 2008 R2 on default unnamed instance, Windows XP Pro SP3) is in
- %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\
and not in
- %ProgramFiles%\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\ [2]
I am curious - Is the location differing due to SQL Server version, edition or Windows edition, version?
[1]
My question "how to see/script definitions of system views?"
how to see/script definitions of system views?
[2]
Martin Smith's answer to [1]
how to see/script definitions of system views?
If the database has auto_close turned on then the engine will close the file handle when the database is no longer in use. Express instances create databases as auto-close ON and this property is preserved when database is attached to a non-Express instance.
It is not true that you have to shutdown the instance to copy a database file. All you have to do is to relinquish the exclusive handle held by the SQL Server instance on the database files(s). This can be done by simply setting the database offline, do the copy, then setting it back online. (If I remember correctly) for databases that are read-only, the handle held by SQL Server allows for another processes to open a shared handle on the file. This is why
you can copy an online read-only database, and msqlsystemreource is read-only under normal operating mode.
Other questions:
1c) never. Always copy the MDF, all NDFs and the LDF(s). Even when there are recomendations to copy the MDF alone, ignore them and copy the LDF as well.
2) the resource databases are part of your binaries. They will be in the binn folder of your installation, same location as sqlservr.exe. The path will differ for each installed instance and you can change it during Setup.
And one last consideration: why do you touch the resource db? You are up for a world of pain, you may easily corrupt your instance if you manipulate resource db in any way.
