Presumably this is 64bit? SQL loves memory and is designed to take whatever it needs. As data is read in it remains in memory and SQL will only release it...

i think you forgot to include the error

Jason, gotcha,

DEFINITELY do SQL backups of system dbs, the file copies are a belt and braces approach with particular usefulness if SQL wont start because of system db damage.

you are going to have to do this manually, scripting out jobs and saving and importing DTS/SSIS. (you have both?)

Is this actually SQL2005?. If so if the server has a...

CirquedeSQLeil (3/24/2010)

---

george sibbald (3/24/2010)

---

I'm beginning to bore myself saying it but I always keep file copies of the system databases in case of loss or corruption. Saves running setup to...

I'm beginning to bore myself saying it but I always keep file copies of the system databases in case of loss or corruption. Saves running setup to rebuild.

kwilt (3/23/2010)

---

George: "I would have flat file copies of all the system databases to speed up the recovery process, especially mssqlsytemresource."

kwilt: Sorry to step in on someone else's...

This is from SQL2000 (default instance) but should work in 2005 as well

declare @account varchar(50)

print 'MSSQLServer service account '

exec xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Services\MSSQLServer', 'Objectname', @account output

print @account

print 'SQLServerAgent service account '

exec...

I would copy them rather than move them, and delete originals once databases online again with new location

cheers John, I thought it would, seems sensible thing to do as roles can change.

I don't agree backup\restore is safest way, a restore that fails leaves a database unusable, plus there is the database owner issue.

what could be safer than amending sys.master_files table via...

log shipping updates the backupset table of the secondary server with backup information.

mirroring might too, but not sure about that, I can check tomorrow unless someone knows for sure.

you will have to weigh up which will take longer, backup\restore or the alter database and copy file method (detach\attach is the worst way to go). Note COPY the files....

when you say users I presume you mean logins.

do you have user defined error messages, user stored procedures held in master? Details of user default databases, default language, server roles...

and another thing, if you stick with the delete, reindex this table when you have finished, the likeliehood of internal fragmentation is high.