SQL Backup/Restore Percentage and Time Remaining

When you perform a restore from the GUI with SQL Server, there is a status indicator at the bottom right that tells the percentage complete.  However, when you restore from T-SQL, there is not one by default.  That information is actually stored in the sys.dm_exec_requests as well as estimated time remaining, stored in milliseconds.    Here is an example query that shows the percentage complete, as well as an estimated time remaining.


USE MASTER
GO
SELECT percent_complete,start_time,command,b.name AS DatabaseName,
      DATEADD(ms,estimated_completion_time,GETDATE()) AS RemainTime,
      (estimated_completion_time/1000/60) AS MinutesToFinish
FROM sys.dm_exec_requests a
      INNER JOIN sys.databases b
      ON a.database_id = b.database_id
WHERE command like '%restore%' or command like '%Backup%' AND estimated_completion_time > 0


Example:

SQL Server Management Studio takes too long to start

When you first install SQL Server 2012 and try to open Management Studio, it can take up to a minute to open.  Why?  The answer is, Management Studio has to contact crl.microsoft.com to check for certificate revocations. It can take longer if it can't make that connection, because of no internet access.  To get around this, do the following:

1.  Close SSMS
2.  Open Internet Explorer
3.  Go to Tools->Internet Options (or Internet Options from the control panel)
4.  Go to the Security node and uncheck "Check publisher's certificate revocation"
5.  Click OK and close Internet Explorer

SSMS should now open much faster.  However, be cautious using this as any application that uses managed code may be at some risk.  In the past, there were some fake MS certificates floating around.

Change DBOwner

A lot of times people will move a database from one instance to another.  A good example of this is to move a database from production to a test environment.  With SQL 2012, you have partially contained databases that can handle the security requirements for this.  However, a lot of times people attach a database and then have no access to it.  The reason for this, usually, is that the DBOwner of that database does not exist in the new instance.  So, they need to change the DBOwner.  To do this, simply use the sp_changedbowner stored procedure.  Please keep in mind that you must run this command from a user that has the sysadmin fixed server role.


Example:

Use AdventureWorks2012
GO
EXEC sp_changedbowner 'John'


This will make John the DBOwner of the AdventureWorks2012 database.