SQL Server
Some notes and links for MSDE, Microsoft SQL Server.
-
Move database log files (e.g. to put them on a separate drive for speed and/or reliability):
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071
-
Attempt to restore database without the LDF (log file)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_4wrm.asp
-
Indexed views example: http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/
-
Bind view. Then create index.
-
Permissions: (SQL 2005 and newer, i think)
-
Read (all tables): db_datareader
-
Write (all tables): db_datawriter
-
God mode for the particular database: db_owner
-
God mode for everything on server: sysadmin
-
http://stackoverflow.com/questions/3998634/sql-server-2008-how-do-i-grant-privileges-to-a-username
-
Repair w/DBCC syntax: DBCC CHECKDB ('my_db', repair_allow_data_loss);
-
Take DB into Single User Mode - In the Database Properties dialog box, click the Options page. From the Restrict Access option, select Single.
-
TIMESTAMP datatype in SQL Server has NOTHING to do with the time on the calendar. It's a version stamping thing.
Master database
This is critical. IMHO, you should have a backup copy of the RAW mdf and ldf files. You can't start sqlserver w/o Master and a .bak does little good if you can't start sql server.
Corrupt DB - This WILL have data loss:
ALTER DATABASE dbname SET EMERGENCY;
ALTER DATABASE dbname SET SINGLE_USER
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
Physical File Notes - DRAFT:
The MDF files date modified seems to get updated to the last DBCC CHECKDB time
The LDF file date modified does not get updated as frequently. Trails by many days. Possibly last boot? Or last resize?
Tags: mssql, sqlserver, sql server, msde, microsoft, sql express, tsql