Check out this SlideShare Presentation: Exam 70-432 preparation questions View more documents from stuart broad .
Posts
When you back up a database, determine whether to overwrite or append to a backup file. The SQL Server default is to append (NOINIT) backups to a file. If you use the NOINIT option, SQL Server appends a backup to an existing backup file or backup set. If you use the INIT option, SQL Server overwrites any existing data on the backup media set, but it retains the header information. If the first file of the backup set on the device has an ANSI-standard label, SQL Server determines whether the previous backup set can be overwritten. Use the FORMAT option to overwrite the contents of a backup file and split up the backup set. --Backup device USE master EXEC sp_addumpdevice 'disk', 'AdventureWorksBack','D:\MyBackupDir\AdventureWorksBack.bak' --to see the media contents of the backup device RESTORE filelistonly from AdventureWorksBack RESTORE HEADERONLY from AdventureWorksBack --full backup --Full backup on backup device BACKUP DATABASE Adventu
Here is a script to check the status of SQL Server Services and there status from the SSMS /*---------------------------------------*/ /* SQL Server Service Check Utility */ /*---------------------------------------*/ SET NOCOUNT ON /* ------------------------------------------ Inital Setup -----------------------------------------------------*/ CREATE TABLE #RegResult ( ResultValue NVARCHAR(4) ) CREATE TABLE #ServicesServiceStatus /*Create temp tables*/ ( RowID INT IDENTITY(1,1) ,ServerName NVARCHAR(128) ,ServiceName NVARCHAR(128) ,ServiceStatus varchar(128) ,StatusDateTime DATETIME DEFAULT (GETDATE()) ,PhysicalSrverName NVARCHAR(128) ) DECLARE @ChkInstanceName nvarchar(128) /*Stores SQL Instance Name*/ ,@ChkSrvName nvarchar(128) /*Stores Server Name*/ ,@TrueSrvName nvarchar(128) /*Stores where code name needed */ ,@SQLSrv NVAR
SELECT o.name AS "Table Name", i.rowcnt AS "Row Count" FROM sysobjects o, sysindexes i WHERE i.id = o.id AND indid IN(0,1) AND xtype = 'u' AND o.name <> 'sysdiagrams' ORDER BY i.rowcnt DESC
For a DBA its a routine issue to keep watch on the tables and database usage. Hence, it should be possible for any DBA to track the changes made to either a database or a table and be able to take necessary action according to the situation. Consider, if there is a database which is never used in a server but still there in the server. The metadata of the idle database will be stored in the system databases as this is connected to the server and if the database is very big with many objects, its obvious that a huge data should be stored in the system databases related to that database. Hence to counter the problem we can check the server databases which are critical in respect to its usage and if we found some databases never being used, we can take them offline. We can check the database, when it was last used or updated by running a query as shown below. select distinct DB_NAME(database_id), object_name(object_id), last_user_seek,last_user_scan, last_user_lookup, last_user_update
Credentials (Database Engine) A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. This information is used internally by SQL Server. Most credentials contain a Windows user name and password. The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication to access resources outside the server instance. When the external resource is Windows, the user is authenticated as the Windows user specified in the credential. A single credential can be mapped to multiple SQL Server logins. However, a SQL Server login can be mapped to only one credential. System credentials are created automatically and are associated with specific endpoints. Names for system credentials start with two hash signs (##). To check the credentials available from an instance os SQL Server, execute select * from sys.credentials To create a credential In Object Explorer,