Feeds:
Posts
Comments

Archive for the ‘Move databases’ Category

Quick one today:

Occasionally, the need to perform DIFFERENTIAL backup arises. The underlying mechanism of Differential backup is one of the coolest things.

 

--
--  Syntax for DIFF backup
--
BACKUP DATABASE SampleDB
TO DISK = 'I:\MSSQL\Backup\DIFF\SampleDB.DIFF'
WITH      DIFFERENTIAL
	, COMPRESSION
	, STATS = 1
GO

 

DIFF_Backup_Syntax

 

Some highlights:

  • Differential backup only captures the data that has changed since the last FULL backup (called base of the differential).
    • Even when we perform multiple DIFF backups, it captures all the data that changed since in the last FULL backup (unlike Transactional log backups, where they only take the changes since the last Transactional backup)
  • The size of DIFF backup files is usually smaller than FULL backups;
    • Obviously, since we are only capturing the changes since last FULL backup.
  • Using Differential Bitmap page, Sql Server maintains a list of extents that were modified since last FULL backup.
    • Using this, Sql is able to quickly run through all the extents that need to be backed-up quickly.
    • See the image from MSDN for clarity
  • bnr-how-diff-backups-work
Hope this helps,
_Sqltimes

Advertisements

Read Full Post »

Quick one today:

Recently, ran into this error, about an error with implicit conversion.

Msg 402, Level 16, State 1, Line 45
The data types nvarchar and varbinary are incompatible in the add operator.

This is the query:

--
-- Generate script to CREATE/migrate Sql logins
--
SELECT
		  'CREATE LOGIN [' + L.name
		+ '] WITH PASSWORD = ' + L.password_hash + ' HASHED, sid = ' + L.sid
		+ ', CHECK_EXPIRATION = '
		+ CASE WHEN L.is_expiration_checked = 0 THEN 'OFF, ' ELSE 'ON, ' END
		+ 'CHECK_POLICY = '
		+ CASE WHEN L.is_policy_checked = 0 THEN ' OFF, ' ELSE 'ON, ' END
		+ 'DEFAULT_DATABASE = [' + L.default_database_name + '], '
		+ 'DEFAULT_LANGUAGE = [' + L.default_language_name + ']'
FROM sys.sql_logins AS L
WHERE type = 'S' -- SqlLogin
ORDER BY L.name ASC
GO

On the surface it seems fairly easy to fix, right? Just find the VARBINARY literal in the query and make the conversion EXPLICIT using a CONVERT() function.

In this case, it is ‘L.password_hash‘ & ‘L.sid‘.

As indicated in BoL, password_hash is a VARBINARY(256) dataype and sid is a VARBINARY(85). To concatenate VARBINARY values (represented in hex), with the rest of the query, written in VARCHAR, we need to use an an undocument function called: fn_varbintohexstr. 

Here our goal is to keep the hex representation intact (visual); and just convert the binary to VARCHAR, so they could be concatenated with the rest of the SQL script (written in VARCHAR). We do not want to convert the contents into VARCHAR, just the format to VARCHAR.

--
-- Generate script to CREATE/migrate Sql logins
--
SELECT
		  'CREATE LOGIN [' + L.name
		+ '] WITH PASSWORD = ' + master.sys.fn_varbintohexstr(L.password_hash) + ' HASHED, sid = ' + master.sys.fn_varbintohexstr(L.sid)
		+ ', CHECK_EXPIRATION = '
		+ CASE WHEN L.is_expiration_checked = 0 THEN 'OFF, ' ELSE 'ON, ' END
		+ 'CHECK_POLICY = '
		+ CASE WHEN L.is_policy_checked = 0 THEN ' OFF, ' ELSE 'ON, ' END
		+ 'DEFAULT_DATABASE = [' + L.default_database_name + '], '
		+ 'DEFAULT_LANGUAGE = [' + L.default_language_name + ']'
FROM sys.sql_logins AS L
WHERE type = 'S' -- SqlLogin
ORDER BY L.name ASC
GO

With this function, we are able to convert the contents of VARBINARY variable/column (represented in hexadecimal) to VARCHAR (again represented in hex), so we could generate a proper SQL script.

 

Hope this helps,
_Sqltimes

Read Full Post »

In the previous post we noted how simple it is to move user databases. Now we’ll look at moving System database.

Moving system databases is as simple as moving user databases (with a couple of extra steps). But if done incorrectly, could leave the Sql Server instance in a unstable and unresponsive state. So I strongly recommend that you try this only on a test instance before you actually run this on Production (been there, done that :().

In a typical Sql Server installation, we have the following system databases:

  • Master
  • Model
  • Msdb
  • TempDB
  • Resource   (not listed in SSMS or catalog views)

Following steps must be followed in the order listed here for a successful implementation.

Step 1: Make sure all the other Sql services are stopped except SqlServer database engine service (sqlservr.exe) and Sql Browser service (sqlbrowser.exe).

Stop these services, either from Services Console or SCM (Sql Server Configuration Manager)

  • Integration (MsDtsSrvr.exe)
  • Analysis (msmdsrv.exe)
  • Reports (ReportingServicesService.exe)
  • Sql Agent (SQLAGENT.EXE)
  • Full Text (fdlauncher.exe)

Also make sure there are no other services running that request database connection i.e. web server, app server, mid-tier services.

This step is not required, but I would recommend it as I ran into problems in the past.

Step 2: Move Model, Msdb, TempDB files

Run the following script for each database file for these databases.

USE MASTER
GO
--  MSDB Database
ALTER DATABASE [msdb]
MODIFY
FILE (NAME = 'MSDBData', FILENAME = 'E:\MDF\MSDBData.MDF')
GO
ALTER DATABASE [msdb]
MODIFY
FILE ( NAME = 'MSDBLog', FILENAME = 'E:\LDF\MSDBLog.LDF')
GO
--  Model Database
ALTER DATABASE [model]
MODIFY
FILE (NAME = 'modeldev', FILENAME = 'E:\MDF\model.MDF')
GO
ALTER DATABASE [model]
MODIFY
FILE (NAME = 'modellog', FILENAME = 'E:\LDF\modellog.LDF')
GO
--  TempDB Database
ALTER DATABASE [tempdb]
MODIFY
FILE (NAME = 'tempdev', FILENAME = 'E:\MDF\tempdb.MDF')
GO
ALTER DATABASE [tempdb]
MODIFY
FILE (NAME = 'templog', FILENAME = 'E:\LDF\templog.LDF')
GO

Step 3: Stop the Sql Server instance.

Step 4: Move database files to the new location.

Step 5: Start the Sql Server instance. (only the database engine)

Step 6: Confirm the success for these three system databases. Also run some sample queries to confirm the status of these system databases.

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id IN (DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'))
GO

Step 7: If you are using DBMail, make sure Service Broker is still enabled after the move. Also send a Test email to be sure.

USE MASTER
GO
SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb'
GO

Step 8: Now its time for Master and Resource databases.

It is important that Master and Resource data files must remain in the same location.

  • Step 8.1: Go to SCM (Sql Server Configuration Manager) and identify relevant Sql Server instance (MSSQLSERVER or named instance) and go to properties.
  • Step 8.2: In properties window, go to Advanced tab and look for Startup Parameters in the list.
  • Step 8.3: The start up parameters  -d, -l, -e specify where the master data file, master log file and folder location for ERROR LOGS respectively. Lets say, the new location is E:\MDF for data, E:\LDF for log and E:\Log\ERRORLOG for log files.


-dE:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-lE:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-eE:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

Change it to:

-dE:\MDF\master.mdf;-lE:\LDF\mastlog.ldf;-eE:\Log\ERRORLOG

  • Step 8.4: Make sure there is no other service running other than Sql Server database engine. Now, stop the service.
  • Step 8.5: Move master.mdf and mastlog.ldf to the new location (E:\MDF, E:\LDF).
  • Step 8.6: Now start Sql Server database service with a special start up parameter.  Go to command prompt and run the following command. This parameter allows us to limit the recovery operations just to master database that way we could perform some operations on other databases (on Resource database).
  • NET START MSSQLSERVER /f /T3608 or
  • NET START MSSQL$instancename /f /T3608
  • Step 8.7: Now open a new connection to the database and run the following command to indicate move of Resource database files.

ALTER DATABASE [MSSqlSystemResource]
MODIFY
FILE (NAME = 'data', FILENAME = 'E:\MDF\mssqlsystemresource.MDF')
GO
ALTER DATABASE [MSSqlSystemResource]
MODIFY
FILE (NAME = 'log', FILENAME = 'E:\LDF\mssqlsystemresource.LDF')
GO

  • Step 8.8: Move mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.
  • Step 8.9: Now set Resource database to read-only mode.ALTER DATABASE MSSqlSystemResource
    SET READ_ONLY;
  • Step 8.10: Exit database connection and restart the Sql instance.
  • Step 8.11: Now confirm the success of the move. Please note Resource database metadata is not available from the system catalog views.

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id = DB_ID('master')
GO

Hope this helps,

_SqlTimes

Read Full Post »

There are several ways to move ‘User’ database file location.

  1. Attach/Detach
  2. Backup/Restore
  3. ALTER DATABASE MODIFY FILE

1 (Attach/Detach) and 2 (Backup/Restore) are straight forward and are more popular that the 3rd option, which is what I cover in this post (Please note that there are other ways to do this too).

Following script allows to perform move on User databases databases to a different location.

Note: Please make sure you try it on a test server before you implement on a Production machine.

Step 1: First, identify the current location of the database files.

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id = DB_ID('DBA')
GO

Step 2: For each database file, run the following command to indicate to Sql Server that you are making meta data change to a database file.

ALTER DATABASE [DBA]
MODIFY
FILE
(
NAME = 'DBA_Primary_Data'
, FILENAME = 'E:\MSSQL_2K5\MDF\DBA_Primary.MDF'
)
GO
ALTER DATABASE [DBA]
MODIFY
FILE
(
NAME = 'DBA_Log'
, FILENAME = 'E:\MSSQL_2K5\LDF\DBA_Log.LDF'
)
GO

After running the above script you will see the following message.

Confirmation message after running ALTER DATBASE script

Step 3: Stop the Sql Server instance

Step 4: Move the database files to the new location.

Step 5: Restart the Sql Server instance for the user database to start at the new location.

Step 6: Verify the success of the move using the following query.

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id = DB_ID('DBA')
GO

Moving system databases is also similar with a couple of extra steps. We will look at that in the next post.

 

Hope this helps,

_SqlTimes

Read Full Post »