Month: April 2019
RESTORE WITH MOVE
If you have taken backup from one server and are going to restore on another server, but the problem is on New server the same directory structure or even the drive is not available as of the one from where the backup was taken.. In that case you will get error on restoring as SQL Server tries to create files on same locations.
To get rid of this situation use WITH MOVE command with your RESTORE as shown below:
RESTORE Database [NewWorkDB]
FROM DISK = ‘E:\WorkDB.bak’
WITH MOVE ‘Test’ to ‘E:\NewWorkDB.mdf’, –Test is logical name of datafile
MOVE ‘Test_log’ to ‘E:\NewWorkDB_log.ldf’ –Test is logcal name of log file
To get list of files being restored prior to perform a RESTORE you can use FILELISTONLY command.
RESTORE FILELISTONLY – How many Files are in backup set?
Sometimes prior to restoring a backup we need to see how many files will be created if we restore a backup and on what location those files will be created. For that we can use RESTORE FILELISTONLY command to get list of files.
RESTORE FILELISTONLY FROM DISK=N'e:\location-of-backupset';
The above command does NOT restore the backup but will just only list the info about files that where SQL Server is going to create the data and log files and of how much size, their logical and physical names.
If “PhysicalName” location is not available in your restoring-server you can use WITH MOVE command to explicitly tell SQL Server that where these files should be created.
DBCC INPUTBUFFER (spid)
DBCC INPUTBUFFER (spid) shows the last statement executed for a given SPID. This statement is mostly used for troubleshooting purposes to determine the exact command a particular SPID is running or have executed. To run this command you must be a member of the sys admin fixed server role, or have VIEW SERVER STATE permission (if SQL 2005+).
To get the SPID you can use sp_who2 to find out which SPID is taking a lot of resources.
To execute, simply replace the SPID below with the one you want to spy on.
Difference Between sp_who & sp_who2
Purpose of sp_who and sp_who2 is same.But some key differences are:
sp_who supports the limited columns information about currently running process in the SQL Server.
sp_who2 supports some extra columns information about currently running process in the SQL Server then sp_who command.
sp_who command is documented and officially supported by the Microsoft.
sp_who2 command is undocumented and don’t have any support from Microsoft.
sp_who2 is more widely used for better information of the running process.
The following images show the clear information about sp_who and sp_who2.
sp_who
sp_who2
SQL Server DBA Exam 70-764
- Technology: SQL Server 2016/2017/2019
- Credit toward certification: MCSA
- Microsoft Site Link: Exam 70-764
Configure data access and auditing (20–25%)
- Configure encryption
- Implement cell-level encryption
- Implement Always Encrypted
- Implement backup encryption
- Configure transparent data encryption
- Configure encryption for connections
- Troubleshoot encryption errors
- Configure data access and permissions
- Manage database object permissions
- Create and maintain users
- Create and maintain custom roles
- Configure user options for Azure SQL Database
- Configure row-level security
- Configure dynamic data masking
- Configure auditing
- Configure an audit on SQL Server
- Query the SQL Server audit log
- Manage a SQL Server audit
- Configure an Azure SQL Database audit
- Analyze audit logs and reports from Azure SQL Database
Manage backup and restore of databases (20–25%)
- Develop a backup strategy
- Back up very large databases
- Configure alerting for failed backups
- Back up databases to Azure
- Manage transaction log backups,
- Configure database recovery models
- Configure backup automation
- Restore databases
- Perform piecemeal restores
- Perform page recovery
- Perform point-in-time recovery
- Restore file groups
- Develop a plan to automate and test restores
- Manage database integrity
- Implement database consistency checks
- Identify database corruption
- Recover from database corruption
Manage and monitor SQL Server instances (35–40%)
- Monitor database activity
- Monitor current sessions
- Identify sessions that cause blocking activity
- Identify sessions that consume tempdb resources
- Configure the data collector
- Monitor queries
- Manage the Query Store
- Configure Extended Events and trace events
- Identify problematic execution plans
- Troubleshoot server health using Extended Events
- Manage indexes
- Identify and repair index fragmentation
- Identify and create missing indexes
- Identify and drop underutilized indexes
- Manage existing columnstore indexes
- Manage statistics
- Identify and correct outdated statistics
- Implement Auto Update Statistics
- Implement statistics for large tables
- Monitor SQL Server instances
- Create and manage operators,
- Create and manage SQL Agent alerts
- Define custom alert actions
- Define failure actions
- Configure database mail
- Configure Policy-Based Management
- Identify available space on data volumes
- Identify the cause of performance degradation
Manage high availability and disaster recovery (20–25%)
- Implement log shipping
- Configure log shipping
- Monitor log shipping
- Implement AlwaysOn Availability Groups
- Configure Windows clustering
- Create an availability group
- Configure read-only routing
- Manage failover
- Create distributed availability groups
- Implement Failover Cluster Instances
- Manage shared disks
- Configure cluster shared volumes