SQL Server
Find out when last bakcup was restored in SQL Server DB
Sometime we may need to find out which date some perticular database was restored and by whom? Use following query to find out this information.
The log or differential backup cannot be restored because no files are ready to rollforward
Sometimes with restoring backups in SQL Server Database you may end up by getting this error:
Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
You may usually receive this error while restoring a Differential Backup, in a situation when you haven’t restored FULL Backup OR you have restored Full Backup without “WITH NORECOVERY” option.
SOLUTION:
All you need to do is First restore FULL Backup with NORECOVERY option then restore a differential backup with “RECOVERY” option.
A Simple example is given below:
RESTORE DATABASE AdventureWorks FROM DISK = 'D:\AdventureWorks_Full.bak' WITH NORECOVERY RESTORE DATABASE AdventureWorks FROM DISK = 'D:\AdventureWorks_Diff.bak' WITH RECOVERY
Hands-On Data Science with SQL Server 2017: Perform end-to-end data analysis to gain efficient data insight
by Marek Chmel (Author), Vladimir Muzny (Author)
Find, explore, and extract big data to transform into actionable insights
Key Features
- Perform end-to-end data analysis―from exploration to visualization
- Real-world examples, tasks, and interview queries to be a proficient data scientist
- Understand how SQL is used for big data processing using HiveQL and SparkSQL
Book Description
SQL Server is a relational database management system that enables you to cover end-to-end data science processes using various inbuilt services and features.
Hands-On Data Science with SQL Server 2017 starts with an overview of data science with SQL to understand the core tasks in data science. You will learn intermediate-to-advanced level concepts to perform analytical tasks on data using SQL Server. The book has a unique approach, covering best practices, tasks, and challenges to test your abilities at the end of each chapter. You will explore the ins and outs of performing various key tasks such as data collection, cleaning, manipulation, aggregations, and filtering techniques. As you make your way through the chapters, you will turn raw data into actionable insights by wrangling and extracting data from databases using T-SQL. You will get to grips with preparing and presenting data in a meaningful way, using Power BI to reveal hidden patterns. In the concluding chapters, you will work with SQL Server integration services to transform data into a useful format and delve into advanced examples covering machine learning concepts such as predictive analytics using real-world examples.
By the end of this book, you will be in a position to handle the growing amounts of data and perform everyday activities that a data science professional performs.
What you will learn
- Understand what data science is and how SQL Server is used for big data processing
- Analyze incoming data with SQL queries and visualizations
- Create, train, and evaluate predictive models
- Make predictions using trained models and establish regular retraining courses
- Incorporate data source querying into SQL Server
- Enhance built-in T-SQL capabilities using SQLCLR
- Visualize data with Reporting Services, Power View, and Power BI
- Transform data with R, Python, and Azure
Who this book is for
Hands-On Data Science with SQL Server 2017 is intended for data scientists, data analysts, and big data professionals who want to master their skills learning SQL and its applications. This book will be helpful even for beginners who want to build their career as data science professionals using the power of SQL Server 2017. Basic familiarity with SQL language will aid with understanding the concepts covered in this book.
Table of Contents
- Data Science Overview
- SQL Server 2017 as a Data Science Platform
- Data Sources for Analytics
- Data Transforming and Cleaning with T-SQL
- Data Exploration and Statistics with T-SQL
- Custom Aggregations on SQL Server
- Data Visualization
- Data Transformations with Other Tools
- Predictive Model Training and Evaluation
- Making Predictions
- Getting It All Together – A Real-World Example
- Next Steps with Data Science and SQL
Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud
by Stacia Varga (Author), Denny Cherry (Author), Joseph D’Antoni
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.
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