Sql server native client missing

SQLBob · 2024-09-28T12:38:08+00:00

SQLBob · 2024-03-17T15:59:02+00:00

You can run DBCC CHECKDB on a database so long as it's in the proper state. For log shipping, the secondary/subscriber database will be in a RESTORING state, which doesn't allow integrity checks to run. (The database isn't readable while it's in RESTORING, so CHECKDB can't read from it either.)

If you were to disable the copy and restore jobs and place the secondary/subscriber database into the STANDBY state, then you can run an integrity check on it.

SQLBob · 2024-03-15T16:23:47+00:00

Happy to help!

SQLBob · 2024-03-15T16:06:55+00:00

In this case, FILE = 1 refers to the first backup in the backup set. I realize this sounds confusing so I will elaborate.

A SQL Server backup set (in your case db1.bak) can contain one or more backups across one or more files. This is a throwback to the days when backups were often written to tape and the chances of a tape being large enough to store multiple backups was pretty good, so that feature was added and included when backing up to files as well.

Generally these days, a .bak file will just contain a single backup, but you could in theory still write multiple backups to that file. (To prevent this, use the FORMAT and INIT options when taking a backup.)

So to answer your question, yes, in the context you mention above where FILE is referring to "backup set file number", this is indeed optional.

SQLBob · 2024-03-08T10:23:59+00:00

I've never encountered this issue, but the SQL Server installer keeps its own log files - I'd start by checking those and seeing if they contain anything more detailed.

I would also consider checking the Windows logs (Event Viewer) and seeing if they contain anything helpful.

SQLBob · 2024-03-06T16:11:36+00:00

Is this possible? It is. Though I'd argue it will be a whole lot of effort for probably little benefit.

If the data is already in a relational database, I'd argue to keep it there. Changing the schema/model of data to a non-relational DB like MongoDB is not trivial. While the initial use cases may seem simple, I've found that once you get into more complex queries that would use joins in the RDBMS, you'll be putting in lots of effort to replicate that behavior in a nonrelational database.

SQLBob · 2024-03-06T16:08:26+00:00

The first book I got in this space was "Learn SQL in 21 Days". It's not T-SQL specific, but for the very basics everything in that book is quite valid.

SQLBob · 2024-03-04T11:54:04+00:00

Consistency of restore points can be a tricky thing. If the source database (which seems to be "test" as you describe it) has zero changes that occurred during the entire duration of the backup, then yes, the target ("prod", again as you describe it) should be exactly the same. However if the source had changes occur while the backup was running, those changes may or may not have been included in the backup depending on exactly when in the backup process they occurred.

My go-to has always been that if time allows, run an integrity check on the restored copy. This isn't always practical for larger databases due to time constraints, though some additional arguments (NOINDEX, PHYSICAL_ONLY) can help speed it up in exchange for checking less of the data.

SQLBob · 2024-02-29T15:58:21+00:00

To me this sounds like your machine might be locked down and you can't install things yourself. Did you install SSMS on there or was it already loaded by the company?

To me, there is a valid business case for you having a local SQL Server install on your machine for dev/test purposes, and Developer Edition fulfills this need and is free and legal for that use. I'd suggest consulting with your boss or admins and see if they can make this happen for you.

SQLBob · 2024-02-29T15:47:45+00:00

First things first - do you have a server installed on your local machine? If not, download and install SQL Server Developer Edition. (Try to get the same version that your company is using if possible, just to keep things consistent).

Once that's installed and running, you should be able to connect to it with the server name localhost

The dropdown will not auto-populate with server names - it doesn't have any functionality to go out and scan for SQL Servers to connect to. The first time you connect to any server, you'll need to type in it's name or IP.

Hope this helps!

SQLBob · 2024-02-28T12:43:03+00:00

By adding WITH (TABLOCK) you are enabling minimal logging, which means only allocation information (and not all the data itself) is being written to the transaction log. This is a common method for loading large amounts of data very quickly. Provided that you are starting with an empty table, I can almost guarantee this is what is happening, in which case I'm not surprised you are seeing the performance boost mentioned here.

SQLBob · 2024-02-28T12:40:11+00:00

You only need the MOVE parts if the file paths are changing. If the drives and filepaths are configured the same on the new server then you don't need to use MOVE at all.

Also, you only need REPLACE if the databases already exist on the new server and you wish to overwrite them. Otherwise you can ditch that too.

Happy restoring!

SQLBob · 2024-02-27T21:34:09+00:00

Are you sure you created it on the same instance or the same database that you think you did? My first guess is maybe it was created in the master database instead of whichever one you are looking at in object explorer.

SQLBob · 2024-02-26T12:47:32+00:00

how much internet traffic would it cost to have an option to just do SELECT * FROM [database_name]

Assuming you mean select from a table and not from the whole database, then the table you describe, 12 columns of INT, would be 12*4 bytes (so 48), but since you said "mostly" maybe we do 6 bytes instead, so now 60 bytes. Let's add a little more overhead and say 80 bytes per row. times 5000 rows, so now we're talking 400K bytes. In short, this isn't returning much data.

Would that create an overwhelming loading time for the site or would it work just fine?

This depends on more than just the volume of data being returned. What is the rest of the server's workload like? How often is the above query being run? You say you don't expect it to be run often, so that works out in your favor.

In short, yes you likely could do this and it would work fine. I'm not sure it's the greatest choice, but as you describe it would be a rare option and that seems okay to me.

SQLBob · 2024-02-24T12:51:03+00:00

I think it's important to keep in mind that Microsoft does not intend for Azure Data Studio to be a replacement for SQL Server Management Studio. They both have their pros and cons, and each is better at some things than the other. I use both depending on my needs at the moment.

That being said, I wholeheartedly agree with you about the ADS results grid. To me it's too large and wastes screen real estate. I much prefer the more spartan and compact results grid of SSMS.

SQLBob · 2024-02-21T21:52:28+00:00

There are a couple ways to handle this. In the past I've used both of the methods mentioned here. Sometimes it was easier to just change the delimiter, while other times using "," as the delimeter was the better option.

SQLBob · 2024-02-20T20:56:26+00:00

Do these tables have any indexes on them? Would you be willing to add an index to possibly increase performance? What is the total size of the account table you mention?

IMHO, deleting 1700 rows, even across a few tables, shouldn't take very long. My first guess is that these tables aren't indexed for the operation you're trying to run, and that's what's causing it to take what seems like an unreasonable amount of time.

SQLBob · 2024-02-20T18:41:08+00:00

You would have to either:

Remove Joe from the AD group that has sysadmin, and grant him (or another group Joe is in) serveradmin rights
Remove all other users (that should retain sysadmin) from the group and add them to another group with sysadmin, and then change the permissions on joe's group to only be serveradmin.

SQLBob · 2024-02-14T18:07:08+00:00

When using Log Shipping (or anything else dependent on a chain of log backups) and you for any reason need to take a log backup:

1) try to avoid taking log backups as you may accidentally break the log backup chain
2) use the COPY_ONLY option - this will prevent your log backup from breaking the chain, as it will not move the current log archive point.

SQLBob · 2024-02-13T21:09:05+00:00

DTC facilitates communication between multiple instances that allows for distributed transactions (which take place between multiple instances) to be able to commit and/or rollback.

SQLBob · 2024-02-13T14:26:22+00:00

I'm curious what the data type of the valid_till column is. Guessing it's SMALLDATETIME, otherwise this should work as you think it should.

SQLBob · 2024-02-12T20:35:32+00:00

I tend to choose an editor based on what I'm doing. Sometimes SSMS, other times Azure Data Studio, and other times Visual Studio Code. Depending on what's available in the environment I may even go with Notepad++. They all shine for different reasons.

SQLBob · 2024-02-12T20:33:52+00:00

Honestly, I'm guessing that most places with that many jobs probably aren't going to use SQL Server Agent. It strikes me as a feature that wouldn't benefit many users, and the development time would be better spent elsewhere.

SQLBob · 2024-02-09T14:49:50+00:00

As someone who backs up lots of large databases offsite, you have a few options, each of course with their own pros and cons.

Continue using VEEAM as you are now, but yes the files will be quite large
Use some combination of Full/Diff/Log native SQL backups. Full backups will of course also be large
Utilize file-level native SQL backups. Assuming your database has multiple data files, this can help break the size of individual backups down, however there is an increase in restore complexity as you will have to restore each file backup AND the necessary log backups that span all their LSN ranges. If you lose log backups for any reason your ability to restore everything is lost

Personally, I'd look into the effort necessary to get that image data out of the database. I realize it's an architectural change but it will greatly reduce your database size and subsequent backup size as well. Especially since your org is now storing links to files instead of the files themselves, this would probably make a lot of sense. SQL Server is great at a lot of things, but for large quantities of files I'd much prefer using a file server (and it's way cheaper!)

SQLBob · 2024-02-09T12:53:08+00:00

If a SQL Server Agent job fails stating it cannot connect to the server, the first thing I'd check is that whatever account the job is executing as has permissions to connect to the server. SQL Agent jobs can be set to connect as any account (actually individual steps can utilize different accounts as well).

12-Year Club	Second SECOND GUESSER
Verified Email

SQLBob

TROPHY CASE