New answers tagged

0

You can generate the scripts from SSMS and include the data. See this. This is what I would do if I needed a one time move of the schema and the data.


3

You generally do not do it. It is not possible. SQL Server is not forward compatible. 2017 does not know how to handle a 2019 backup. PERIOD - no discussion, it will tell you so. Which is why it is an utterly stupid idea no to use the same versions over the whole application development team cycle - you make copy operations complex, you run into ...


4

A backup is overkill for only one table and I'm not sure what compatibility issues you can run into restoring to a lower version of SQL Server (since backups occur at the database level). Instead you should look to just copy the data between servers. There are a multitude of ways and tools out there to help you do this, but I've always found the SQL Examiner ...


0

Heterogeneous Replication is what used to be the way to accomplish this but that is much long deprecated. Please see this StackOverflow answer which essentially recommends either using Change Data Capture or SSIS to move the data from PostgreSQL to SQL Server.


2

That is a pretty hefty database to shrink. I'd just like to point out two things you want to check for, before you start your shrink operations: LOB data. This will take forever, since there are no backpointers for the LOB data. I.e., a LOB page was moved and all SQL Server know is what table it belongs to. It has to do a table scan to find the row, one for ...


0

One possibility would be to use Transactional replication and add static row filters to filter out the older data, this would also have the advantage of a shorter cut over time to the new server (I'm assuming you're doing a side by side migration not an in place upgrade).


3

you can read about this here - https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/ and about when shrinking is a must what can you do here - https://www.brentozar.com/archive/2020/07/what-if-you-really-do-need-to-shrink-a-database/ When we shrink our database, it introduces both external and internal fragmentation, it ...


0

You can utilize the below to see how much free space is left in the data file(s) as well as how much you've already used. The limit for data files in Express edition is 10 GBs currently(https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15) The code is based off of: https://am2.co/2016/04/shrink-...


0

Had the same error (47106) - the reason was that SQL Servers (SQL01 and SQL02) were running under local accounts (e.g. NT Service\MSSQLServer) and therefore granting connect permissions were required. On SQL01: CREATE LOGIN [DOMAIN\SQL02$] FROM WINDOWS GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DOMAIN\SQL02$]; On SQL02: CREATE LOGIN [DOMAIN\SQL01$] FROM ...


0

For the cumulative DMVs, if you're capturing them routinely, then you just need to compare the differential changes between the timeframe of when your query that you're tracing slows down. Then you should be able to pinpoint abnormalities. You can also look into the sp_Blitz procedures, specifically sp_BlitzFirst during the moment of contention.


1

All I had to do is start the job running on the primary server using EXEC msdb.dbo.sp_start_job 'cdc.DbName_capture'; The nice thing is that it caught up with all the changes made since the job went down.


1

it seems that the problem is generated by the fact that the database is not primary. In fact it is read-only state. Try to fail back the relative AG group. https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver15 About the cdc job you need to ...


0

In short, the OLEDB v11 doesn't support the MultiSubnetFailover parameter. Check out this helpful article. You can instead set the ConnectionTimeout to 30 seconds, which seems to let it failover to the other node, but then every OLEDB step in your package could take 30 seconds to fire up.


4

This is not likely a permissions issue, at least not the type of permission you are thinking of. Typical permissions errors show up as follows: Msg 262, Level 14, State 1, Line XXXXX CREATE TABLE permission denied in database 'tempdb'. Now that we have a more complete error message, we can see that indicates a "severity" of 19. According to ...


1

Are you able to login with sa and try to create the table? sa Login doc says: The sa login has all permissions on the server and cannot be limited. The DENY doc says: DENY takes precedence over all permissions, except that DENY does not apply to object owners or members of the sysadmin fixed server role. Security Note Members of the sysadmin fixed server ...


1

I found a similar question/answer here regarding SQL Server: https://dba.stackexchange.com/a/82367/123612 The problem solved. In the Database options - Permissions tab public Database role was added and it was Deny permission for Create database option. After I removed Deny permissions it is OK now. User can create table. Edit: While this may seem obvious, ...


3

> is this the best approach for performance? That is at the same time too broad a question (i.e. will be very opinion based) and to narrow (other people looking for advice are unlikely to have that same config) > Please remember downtime is acceptable for some servers at a time in the case of a drive failure because this is not production workloads Are ...


Top 50 recent answers are included