No comments

SQL Server – A Better Way to Rename a SQL Server Database

  By , ,

I used to rename a database like this.

ALTER DATABASE ConorDB MODIFY NAME = KhabibDB;

But if you use the above method, the logical name and file name will not change. Let’s verify using the script below.

USE master
GO

SELECT 
name AS [Logical Name], 
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State] 
FROM sys.master_files
WHERE database_id = DB_ID(N'KhabibDB')
GO

So you will notice that, even though we renamed the database to KhabibDB, the logical name and file name is still ConorDB. Nevertheless, you have accomplished your objective of renaming your database.

But wait! There is a better way of renaming SQL Server databases that I’m going to show you now!

The procedures below will not only rename your database, but it will also rename the Logical Name and the File Name of the database.

A Better Way To Rename a SQL Server Database

1. Place database in a single user mode.

ALTER DATABASE [ConorDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

2. Modify the logical names.

ALTER DATABASE [ConorDB] MODIFY FILE (NAME=N'ConorDB_data', NEWNAME=N'KhabibDB_data')
GO
ALTER DATABASE [ConorDB] MODIFY FILE (NAME=N'ConorDB_log', NEWNAME=N'KhabibDB_log')
GO

3. Detach the database so that we can rename the physical data files.

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'ConorDB'
GO

4. After we have detach the database successfully, we can now rename the physical data files. We can do this either manually or by using xp_cmdshell system stored procedure. The xp_cmdshell feature can be enabled using the sp_configure system stored procedure.

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO

5. Now that xp_cmdshell is enabled, we can proceed to rename the physical files of the database using the script below.

USE [master]
GO
EXEC xp_cmdshell 'RENAME "S:\Demo\ConorDB_data.mdf", "KhabibDB_data.mdf"'
GO
EXEC xp_cmdshell 'RENAME "S:\Demo\ConorDB_log.ldf", "KhabibDB_log.ldf"'
GO

6. After that’s done, let’s attach the database using the script below.

USE [master]
GO
CREATE DATABASE KhabibDB ON 
( FILENAME = N'S:\Demo\KhabibDB_data.mdf' ),
( FILENAME = N'S:\Demo\KhabibDB_log.ldf' )
FOR ATTACH
GO

7. You may rename the actual database now if you have not done so.

ALTER DATABASE ConorDB MODIFY NAME = KhabibDB;

8. Lastly, let’s allow multi-user access for the database.

ALTER DATABASE KhabibDB SET MULTI_USER 
GO

9. Use the script below the verify the Logical and Physical File Names of the database.

USE master
GO

SELECT 
name AS [Logical Name], 
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State] 
FROM sys.master_files
WHERE database_id = DB_ID(N'KhabibDB')
GO

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

MySQL – Database Patch Upgrade on Windows Step-by-Step with Screenshots

  By ,

1. Download the latest patch for MySQL in My Oracle Support. I choose MySQL installer for the integrated installer for windows.

1.1. Go to Patches & Updates. Chooose Product or Family (Advanced). Type MySQL Server on Products. Expand the options in the Release drop down and choose your desired release. Choose Microsoft Windows x64 in Platform option.

1.2. Choose the latest patch.

1.3. Click Download

2. Verify the current version

3. Backup databases

4. Double click on the patch

5. Click Next >

6. I will choose to upgrade all components. Click Next >

7. Click Execute.

8. Click Next >

9. Click Next  >

10. Enter root password then click Check. If the connection succeeded, Click Next >

11. Click Execute

12. Click Finish

13. Verify new MySQL version. Click Next >

14. Click Finish

This is how I upgrade the patch of our MySQL database on windows. Just be sure to backup your databases and/or if your server is a VM, ask your Windows Admin team to perform a VM snapshot of the server before you apply the patch.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Installing SQL Server 2016 Developer Edition (Free) with Screenshots

  By ,

NOTE: SQL Server Management Studio is not part of the SQL Server 2016 media anymore Please download it here

1. Download SQL Server 2016 Developer Edition. I downloaded SQL Server 2016 Developer Edition using my MSDN account.

Alternatively you may go this link to get the SQL Server 2016 Evaluation.

2. Right-click then Run SQL Server 2016 setup as administrator.

 

3. Choose Installation on the left-pane then click New SQL Server stand-alone installation… (the first choice).

4. I’m installing a Developer edition (free) hence there is no need to enter a product key. Click Next.

5. Accept the license terms then click Next.

6. I opted not to tick User Microsoft Updates. Click Next.

7.  Ignore the warning about Windows Firewall. This means that the Windows Firewall for Database Engine Access is not yet configured. We can do that later. Click Next.

8. Select the necessary features that you need. You can read the descrption in the feature description box.

9. Choose Default instance if you are not going to create multiple instances of SQL Server on the same machine. Otherwise go for Named Instance. Click Next.

10. Enter the service accounts that you will use to run SQL Services. I’ve set the Startup Type to Automatic for all components.

11. Go to the Collation tab. I’ve accepted the defaults because this is exactly what our application needs. Some application require you to choose a specific collation. You can click Customize to change it. Click Next.

12. I’ve chosen Mixed Mode. This is highly recommended so that there is an additional built in SA accout with a separate user name and password. To add users such as the one I’m using, click Add Current User. to allow account to log into SQL Server.

13. I left the Data Directories to defaults. Though it can be changed if you have a multiple disk environment. Additionally, for better performance, you may want to separate out where different parts of the DBMS go.

14. Configure multiple tempDB data files. This is to increase the I/O throughput to tempdb. When you create multiple data files, they will all be in the primary filegroup and SQL Server uses a proportional fill algorithm to determine which file to use for each request to create an object. If all the files are exactly the same size, then SQL Server uses the files in a “round robin” fashion, spreading the load equally across the files. As a general guideline, create one data file for each CPU on the server and then adjust the number of files up or down as necessary.

15. I did not enable FILESTREAM but you may want to if you plan to explore large file types such as BLOB. Click Next.

16. If you have selected Analysis Services component, you will be shown with the following screen. Select the Multidimensional and Data Mining Mode. Click Add Current User. Click Next.

17. Leave the Data Directories to default. Click Next.

18. If you have selected Reporting Services component, you will be presented with the following screen. I chose Install only. We will configure it later. Click Next.

19. Review selected features and click Install.

20. Once completed. Click Close.

 

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Troubleshoot a Transaction Log Full Transaction Error (Msg 9002)

  By ,

I encountered the error below.

Msg 9002, Level 17, State 2, Line 1
The transaction log for database ‘DBName’ is full due to ‘LOG_BACKUP’.

The following are the corrective actions that can be done.

  • Take a log backup (The #1 cause of full transaction log is the lack of backups)
  • Kill a long-running transaction
  • Alter database mirroring, availability groups, or replication to allow the transaction log to clear
  • Either manually grow the existing log file(s) or add another log file (temporarily)
  • As a last resort, switch to SIMPLE recovery model

Execute the script below to know what is the reason why the log cannot be cleared.

SELECT [log_reuse_wait_desc]
	FROM [master].[sys].[databases]
	WHERE [name] = N'DB_Name';
GO

Some examples of why the log is not clearing are LOG_BACKUP, DATABASE_MIRRORING, NOTHING.

You may check the full list in Books Online at http://bit.ly/PoX2xe

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – View Orphaned Users

  By ,

Orphaned users in SQL Server occur when a database user is based on a login in the master database, but the login no longer exists in master. This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. This topic describes how to find orphaned users, and remap them to logins.

Below is a script that I use to view orphaned users.

DECLARE cur CURSOR FAST_FORWARD FOR 
SELECT name FROM sys.databases 
WHERE database_id > 4 

OPEN cur  

DECLARE @SQL NVARCHAR(MAX), @DBName SYSNAME 
DECLARE @Results TABLE (DBName SYSNAME, UserName SYSNAME, UserSID VARBINARY(MAX))  

FETCH NEXT FROM cur into @DBName  

WHILE @@FETCH_STATUS = 0 
BEGIN 
	SET @SQL = 'USE ' + @DBName + ';SELECT ''' + @DBName + ''' AS DBName, 
			UserName = name, UserSID = sid from sysusers 
			WHERE issqluser = 1 AND 
				(sid IS NOT NULL AND sid <> 0x0) AND 
				(LEN(sid) <= 16) AND SUSER_SNAME(sid) IS NULL'     

	INSERT INTO @Results 
	EXEC(@SQL)  

	FETCH NEXT FROM cur into @DBName  
END  

CLOSE cur 
DEALLOCATE cur  

SELECT * FROM @Results

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – MAXIMUMERRORCOUNTREACHED Error (Maintenance Plan)

  By ,

I setup a maintenance plan to backup my databases. I ran it  and I hit the error below.

The problem is because there are a couple of databases that are having an error -transaction log full due to CHECKPOINT (This is a different story). Hence, I’m not able to backup these databases. If you look at the log closely, you will notice the errors (refer to the screenshot below).

So what I did was to deselect these databases to make the job run successfully.

Alternatively though, you increase the maximum error count to make the job go thru successfully despite hitting errors.

  1. Right-click on the Backup Task then go to Properties.

2. Increase the number of the “MaximumErrorCount” parameter.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
2 Comments

SQL Server – Reinitializing a Single Article In Transaction Replication

  By ,

In this blog post, I will list down the steps on how to re-initialize just a single article in transaction replication

1.  Turn off @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'false'
GO
               
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'false'
GO

The reason we have to disable @immediate_sync is that everytime you add a new article, and if @immediate_sync is enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article.

2.  Add new article.

EXEC sp_addarticle
@publication = 'Replication_Test',
@article = 'REPL_TABLE',
@source_object = 'REPL_TABLE',
@force_invalidate_snapshot = 1

3.  Refresh the subscription

EXEC sp_refreshsubscriptions @publication = 'Replication_Test'
GO

4.  Check the current snapshot agent history.

use distribution
go
select * from dbo.MSsnapshot_history

5.  Start Snapshot agent.

EXEC sp_startpublication_snapshot @publication = 'Replication_Test';
GO

6.  Check the Snapshot Agent history again. You should see a snapshot generated only for the newly added article/s.

7.  Turn ON @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go

EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'true'
GO


EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'true'
GO

This is how you can re-initialize only the required articles.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Check The Port Configured in SQL Server

  By ,

Use the script below to find the tcp port number that the SQL Server is listening on.

SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Get Backupset Records

  By ,

The script below retrieves the backupset records.  A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.

 

USE msdb ;
SELECT 
backup_start_date ,
backup_finish_date,
CAST(ROUND ((backup_size / 1024 / 1024), 0, 1) AS DECIMAL (18,0)) AS Backup_Size_MB ,
recovery_model,
[type]
FROM dbo.backupset
WHERE database_name = 'RBD_DB'
ORDER BY backup_start_date DESC

/*
Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.

Reference: https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
*/

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter