T-SQL to find Server and SQL restart date time

This script comes handy when you would like to find out the Server and SQL services restart times.

DECLARE @SQLServiceRestartDateTime AS VARCHAR(50),
		@ServerRestartDateTime AS VARCHAR(50)

SELECT @SQLServiceRestartDateTime = create_date FROM sys.databases WHERE database_id=2
SELECT @ServerRestartDateTime = CAST(DATEADD(SECOND, (ms_ticks/1000) * -1, GETDATE()) AS datetime) FROM sys.dm_os_sys_info

SELECT @SQLServiceRestartDateTime AS [SQL Service Start DateTime], @ServerRestartDateTime AS [Server Start DateTime]

T-SQL to find Backup or Restore Progress

Here is a script that comes handy while performing a huge database Backup or Restore. This script provides the details on the progress of the Backup or Restore operation including the estimated finish time.

SELECT 
   session_id AS SPID, command AS [Command], a.text AS Query, start_time AS [Start Time], 
   percent_complete AS [Percent Complete],
   dateadd(second,estimated_completion_time/1000, getdate()) AS [Estimated Completion Time]
FROM sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command like 'BACKUP%' OR r.command like 'RESTORE%') 

T-SQL to find Navigation path in CMS

In large enterprises there will be huge number of SQL servers which will be registered within a Central Management Server (CMS) and times it may become difficult to find out where the server is registered atleast for new team members in the Huge pile of servers and folder structure. This query give you the path where the specified servers is registered within the CMS.

DECLARE @ServerName nvarchar(50)
SET @ServerName='SQL01';

WITH CMSCTE
AS
(
--Anchor
SELECT server_group_id, name, description, parent_id, 1 AS [Level], CAST((name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS A
WHERE parent_id IS NULL
UNION ALL
--Recursive Member
SELECT B.server_group_id, B.name, B.description, B.parent_id, C.[level] + 1 AS [Level], CAST((C.CMSPath + '->' + B.Name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS B
JOIN CMSCTE AS C 
ON B.parent_id = C.server_group_id  
)

SELECT TOP 1 CMSPath AS 'Path in CMS' , B.name as 'Server Name', B.description AS 'Server Description', A.name AS 'Group Name', A.description AS 'Group Description'
FROM CMSCTE AS A
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers AS B
ON A.server_group_id=B.server_group_id
WHERE B.name = @ServerName
ORDER BY [Level] DESC

T-SQL to find Remote Server Name of the linked server

At times it happens that you might have named a linked server with a friendly name that the actual server name. As the time flies and people change in the team, it will become difficult to identify how or to where this is linked.  You can find the information using the sp_linkedservers stored procedure but what if the linked server configured uses a entirely different DNS name than the actual server? The below query helps to get the Remote Server Name with few other details when run against a linked server.  

DECLARE @linkedServerName varchar(100), @sql varchar(max)
SET @linkedServerName='Your Linked Server Name'
SET @sql = CONCAT('SELECT * FROM OPENQUERY([',@linkedServerName , '], 
''SELECT ''''',@linkedServerName,''''' AS LinkedServerName, 
@@SERVERNAME AS RemoteServerName, SUSER_SNAME() AS ConnectedWith, DB_NAME() AS DefaultDB'')')
EXEC (@sql)

Tips to Move SQL Server Database from One Server to Another - SQL Tutorial by Rajan Singh

Leading organizations always look to find different solutions for high-performance data replication and secure storage environment. One such step is to move SQL Server database from one server to its updated version. In fact, when you migrate SQL server database, it provides an opportunity to scale the organizations data ecosystem efficiently at an optimum cost.

Why should you migrate SQL server database from one server to another?

1. Advancement in Technology: Technology advancement may be a buzzword of today but it is essential to be up-to-date when it refers to Tech-world and to stay ahead, organizations need to accommodate the innovative style of Business Intelligence and ensure agility and higher productivity. Moving database from old SQL version to the latest and advanced technology provides users with an edge over the competition. 
2. Reduce Cost: Moving SQL server database may be time taking and expensive initially, although the costs are lowered significantly in the long run 
3. Achieve higher ROI: One of the significant reasons to switch from a lower version to a higher version or from a different database to SQL server database is to achieve a higher rate of interest on investments. 
4. Consolidate data: Sometimes, data is scattered across different platforms. This is specifically valid in case of mergers and acquisitions. Then consolidating data on a single platform is conducive for users and easy to maintain for SQL administrators. 
5. Secure Storage Environment: Security is a priority for any and all database and database security is ensured through protection rules and compliances. As per the survey conducted by National Institute of Standards and Technology, SQL server is most secure and least vulnerable database and hence the most preferred Storage environment.

Key Challenges in migrating SQL Database from one server to another

Once the organization decides about migration, the Administrator has to analyze the challenges in moving the database from one platform to another. Following key challenges may create hindrance in smooth movement:

1. SQL Database Corruption
   Moving SQL Database from one server to another may lead to database corruption. 
2. Database backup not update
   Many times, when database gets corrupt and backups are also not up-to-date, then the administrator is unable to restore from backup.
3. Database backup not available
   If backup is not implemented and the organization’s database gets corrupt then the chances of database recovery are next to nil unless the organization employs a reliable and performing software like Stellar Phoenix SQL Database Repair.  
4. Cost involved in moving Database
   Moving database from one Database Management System (DBMS) to another involves cost to be incurred in deploying a new server and the cost of data migration and the cost of old server which is rendered non-functional. 
   
   The downtime cost is also considered. Database will remain inaccessible during database migration from one server to another. 

Methods to move SQL Server Database from one server to another

1. Backup SQL Database and Restore to another server
   The easiest solution is to take the latest backup of database and restore on another server. However, in most cases, SQL Admins and Backup Admins are different and there are chances that backup is not up-to-date. 
2. Create a Duplicate Database in SQL Server
   If the earlier database is using SQL database and you want to move database to a higher version, then one of the methods is to create a duplicate copy of database in SQL server. However, Admins need to have more than 100% free space. This is not a feasible option and may lead to corrupt SQL Database. 
3. Move SQL Database to New Server
   SQL Server Management Studio enables copying database from one server to another. To proceed further, you should get acquainted with:
1. Source Server
2. Destination Server
3. Use Windows Authentication
4. Using SQL Server Authentication 
Steps to move:

• Go to Object Explorer and launch SQL Server Management Studio and connect with the Source Server
• Right click on the database to select the ‘Tasks’ option and select ‘Copy Database’ option ‘Copy Database Wizard’ screen displays. 
• Fill in the details of Source Server and select the option ‘Use Windows Authentication’ followed by User ID and Password  
• Next, fill in the details of Destination Server, and its Windows Authentication credentials
• Checkbox for ‘Use the SQL Management Object method’
• Select the Database from the available options and click on Copy option. Click Next to migrate SQL database to the destination server
• Verify MDF files and Log file path on the destination server 
• Copy all other jobs. If the SQL Management Studio fails to copy jobs then opt for the following:
• System Event View
• Local Path
• Click on Windows Event Logs followed by Next button
• To schedule the SSIS page, Click on Run immediately
• Complete the Wizard message displays. Tick mark this option and click on Finish to complete the process. 
4. Stellar SQL Repair to copy SQL Server Database from one server to another
   Deploying a trustworthy software to copy SQL Server Database. One such software is Stellar Phoenix SQL Database Repair software. This software allows users to repair a corrupt SQL server database. Once you have saved the repaired database as a New or Live database, then the next step is to deploy SQL Server Management Studio or SSMS. SSMS detaches the database from the source server and attaches it to the destination server.
   
   Reasons to deploy Stellar software for SQL recovery before using manual method are:

• Database may get corrupt during migration. Stellar software repairs the corrupt MDF file and ensures smooth migration
• In case MDF files or any other associated file is not detached properly, then the SQL Database will not give appropriate results

Conclusion

Error-free Migration of SQL Server database is possible when the different files associated with SQL Database are properly detached from the Source server and attached with the Destination server. However, there are times, when Admins are unable to detach the files and if these files are not detached from the source location, there is low possibility of getting these attached at the destination. In such circumstances, the best solution is to repair the database using Stellar Phoenix SQL Database Repair, save it as New Database and then move this SQL server database from one server to another. By following this procedure, SQL Administrators can ensure error-free and smooth data migration.

Checkout the MVP’s feedback about Stellar Phoenix SQL Database Repair is available here.

About Rajan Singh 
Rajan Singh is a Sr. Technology Consultant at Stellar Data Recovery Inc. and has published several article on MS Exchange emails, IOT, APIs, bug fixing etc.

Social Media Profiles
Google Plus: https://plus.google.com/101490924635563531512