New answers tagged

4

Does SSMS change appearance depending on which SQL Server edition it is running against? Yes We cannot track everything ssms is executing or making decisions on but we can get a general idea. Tracing the right click on a table on a SQL Server 2014 standard edition instance will give us more of an idea on the lower amount of checks. In total, 11 rows/...


0

tibor-karaszi: My bet is on the REDO and UNDO phases. These aren't included in the % complete, since the amount of work is unknown in advance, and it happens after the data copying phase.


0

Have you seen this post on the MSDN forums? User guest does not have permission to run DBCC checkprimaryfile One possible solution proposed in that thread is to run SSMS as an administrator. The other option is to make sure that the user account you're using to the connect to SQL Server: has a login setup on the instance has the appropriate permissions ...


1

As Aaron Bertrand says in the comments, having a .mdf file as the method for adding a database to a server is unusual. Normally you should have a .bak backup file. If at all possible, contact your source and ask for backup to restore. If you have to use an .mdf You need to copy it to the folder on the server, where it will become the .mdf of the database ...


2

SQL Server also writes additional transaction logs while you are making the backup. After restore, these transactions need to be applied, so if a lot of activity happens during your backup it can take a while to apply those operations and then rezero the log file. One final thing, if you are still having mysteries around time you can turn on trace flags ...


4

Database restore process is performed in 3 steps: copy -Copy data from the backup redo - commit completed transactions in T-Log Undo - Rollback incomplete transactions Restore process doesn't perform any maintenance tasks like index rebuild etc. When I run a backup or restore through the SSMS GUI, the dialog shows Executing with 20% increments rather ...


0

Turn off Windows Firewall on your SQL server for the Domain network profile. Start Powershell Run Get-NetFirewallProfile -Profile Domain to check its current state If it's enabled, run: Set-NetFirewallProfile -Profile Domain -Enabled False to turn it off. If this was it, you can turn it back on later and fine tune your settings. Or, if you're in a safe ...


0

Old thread I know, but in case this helps anyone... I definitely typed my path out by hand because I just have a dedicated drive "f:\" in my case. When I read this and looked at my job, it was empty as well. This time I selected it via the ... button and it fixed the problem.


1

Deleting files on NAS should not be done by sql server. You should rather use powershell to do it. You can use my function Clean-NASOldFiles which uses robocopy to scan files extremely fast and will then delete based on your input.


4

Update to the latest version of SSMS. The database diagrams feature was reinstated starting with version 18.1.


2

I keep finding more tables that leak information about other databases, hence I decided to post this collection of things that I block to the public role. They do not seem to affect any db functionality, though I take no responsibility on that as I'm only using a small subset of what SQL Server actually offers. It could very well be that this breaks ...


8

Tracing the queries When tracing the queries executed, below query is found that lists the folders on the drives one by one. declare @Path nvarchar(255) declare @Name nvarchar(255) select @Path = N'D:\' select @Name = null; create table #filetmpfin (Name nvarchar(255) NOT NULL, IsFile bit NULL, FullName nvarchar(300) not NULL) declare @...


1

I would recommend using Ola's scripts. https://ola.hallengren.com/sql-server-backup.html Not only are they awesome but they will give you the naming convention that you are looking for.


0

No, because the Data Migration Assistant helps you to move from SQL Server to Azure SQL. Azure SQL is a different product and thus the system databases are not the same. You will only be able to access the master database on your Azure SQL deployment. That being said, there really isn't anything you will be missing by not moving those over, since most ...


0

You can use this to build the script that you can then view and run separately. select 'ALTER SERVER ROLE [dbcreator] ADD MEMBER [' + sl.loginname+ '] GO ' as SQLCommand from sys.syslogins sl You can also modify the query to filter on database name sl.dbname or username `sl.loginname' Execute each row in the output to add the users to the role.


1

You can use dynamic sql. declare @sqltext nvarchar(max) = N'' -- here you are adding all the logins to dbcreator fixed server role ... -- this is just an example to get you started ...adjust it as per your needs !! select @sqltext += 'ALTER SERVER ROLE dbcreator ADD MEMBER '+QUOTENAME(name)+';'+char(10)+'go'+char(10) from sys.server_principals where ...


Top 50 recent answers are included