• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

  • Services
    • SQL Server Health Check
  • Pocket DBA®
  • Blog
    • Speaking
  • Testimonials
  • Contact
  • About

syndicated

Code That Writes Code

April 28, 2023 by Kevin3NF Leave a Comment

“Work Smarter, not Harder”

We’ve all heard it before, pretty much any job, anywhere.

In our DBA slice of the IT world, this is very relevant to how we manage SQL Servers and the databases on them.

From scripts to check for Bad Things™, to writing efficient code.

In this video, I show a simple way to get SQL Server generate scripts you can run.  In this case I needed to run the same command against a large number of databases. Running it manually, or worse, doing it in the GUI is just not an option.  Enjoy!

The code that I will now use as a starter script:

-- Don't run this if you don't understand it 100%

Select 'ALTER Database ['+[name]+'] set Page_Verify Checksum, recovery Full, auto_shrink off;
go'
From master..sysdatabases
Where DBID > 4

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: SQL, TSQL, video Tagged With: SQL, syndicated

Organize Your Tabs in SQL Server Management Studio

November 2, 2022 by Kevin3NF Leave a Comment

I blogged about this in August, but wanted to do this video as well:

Thanks for reading and/or watching!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: SSMS Tagged With: syndicated

Where Is My SQL Server Errorlog?

November 1, 2022 by Kevin3NF Leave a Comment

If you need to find the SQL Server ErrorLog in a hurry and don’t want to spend 30 minutes drilling into every drive on the server:

“I don’t watch videos” version:

  • Right-click the instance
  • Choose ‘Facets’ from the shortcut menu
  • Look for ‘ErrorLogPath’:

 

 

 

That’s it…let me know if you already knew this or not!

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: SQL, SSMS Tagged With: syndicated

SQL Server Registered Servers

October 28, 2022 by Kevin3NF 3 Comments

Query multiple SQL Server instances at one time!

Thanks for watching!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: SQL, SSMS Tagged With: syndicated

SQL Server Best Practices

September 14, 2022 by Kevin3NF Leave a Comment

As a CIO or CTO, one of your primary responsibilities is to ensure that your organization’s data is managed effectively and efficiently. To do this, you need to have a strong understanding of SQL Server best practices. In this post, we’ll discuss some of the most important best practice areas for SQL Server management.

This is a high-level discussion with items that we will explore more in future posts

Deploying regular backups:

One of the most important best practices for SQL Server is to deploy regular backups. This will ensure that your data is always protected in the event of a system failure or data loss. There are many different ways to backup your SQL Server data, so it’s important to choose the method that best suits your needs.

Security measures:

Another important best practice is to incorporate security measures into your SQL Server deployment. This will help to protect your data from unauthorized access and prevent malicious attacks. There are many different security features available in SQL Server, so it’s important to choose the ones that best fit your organization’s needs.

Monitoring performance:

Another vital best practice is to monitor the performance of your SQL Server regularly. This will help you identify any potential issues and correct them before they cause major problems. There are many different performance monitoring tools available, so it’s important to choose the ones that best fit your organization’s needs. Some of these are free, some require licensing.

High Availability and Disaster recovery plans:

Another crucial best practice is to implement a disaster recovery plan for your SQL Server estate. This will ensure that your data is always safe and accessible in the event of a major outage or disaster. There are many different disaster recovery strategies available, so it’s important to choose the ones that best fit your organization’s needs and budget.

Maintaining documentation:

Finally, it’s also important to maintain comprehensive documentation for your SQL Server deployment. This will help you keep track of all the different settings and configurations, and make it easier to troubleshoot any issues that may arise. Comprehensive documentation also makes it easier to train new staff members on how to use and manage your SQL Server deployment.

These are just a few of the most important SQL Server best practices for CIOs and CTOs. By following these best practices, you can help ensure that your organization’s data is managed effectively and efficiently. If you have any questions about these best practices, or if you need assistance implementing them, please contact us today. We would be happy to help you get started!

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: Uncategorized Tagged With: syndicated

Restore Database or Restore Log?

May 11, 2022 by Kevin3NF Leave a Comment

How long has this worked, and why didn’t anyone tell me?

A typical restore statement from a FULL backup and a LOG backup:

USE [master]

RESTORE DATABASE [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak' 
    WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 25, REPLACE

RESTORE LOG [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn' 
    WITH  FILE = 1,  NOUNLOAD,  STATS = 25

GO

Results:

100 percent processed.
Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE DATABASE successfully processed 354 pages in 0.016 seconds (172.393 MB/sec).
30 percent processed.
60 percent processed.
90 percent processed.
100 percent processed.
Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE LOG successfully processed 27 pages in 0.011 seconds (18.909 MB/sec).

Completion time: 2022-05-11T09:26:44.3023396-05:00

Change the Restore Log to Restore Database:

USE [master]

RESTORE DATABASE [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak' 
    WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 25, REPLACE

RESTORE DATABASE [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn' 
    WITH  FILE = 1,  NOUNLOAD,  STATS = 25

GO

Results:

100 percent processed.
Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE DATABASE successfully processed 354 pages in 0.015 seconds (183.886 MB/sec).
30 percent processed.
60 percent processed.
90 percent processed.
100 percent processed.
Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE LOG successfully processed 27 pages in 0.006 seconds (34.667 MB/sec).

Completion time: 2022-05-11T09:30:42.0225244-05:00

I had no idea the engine would determine the type of backup file and apply it appropriately. I knew it worked for Differential backups, but not Log.

20+ years and I still find new (to me) things every day.  Technically Jon (t) found it but hey…we’re a team here 🙂

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: backup, Restore, TSQL Tagged With: Restore, syndicated

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 8
  • Go to Next Page »

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Copyright © 2023 · Genesis Sample on Genesis Framework · WordPress · Log in

Privacy Policy
 

Loading Comments...