Migrate all user databases from one server to another

It is a common DBA task to migrate databases from one server to another.  When dealing with multiple user databases, I prefer to use scripts and the backup/restore method.  For very large databases, I schedule a full backup and full restore ahead of the migration time.  At the zero hour, I disconnect the client application, perform a differential backup, and then perform a differential restore.

Recently, I have been tasked with migrating a dozen or more small databases to a new server.  I have built a stored procedure that will receive a varchar parameter as the database name to be restored.  It locates the logical names of the data and log files from the .BAK file.  It then uses the parameter and logical names to perform a full restore.

The idea is that I can backup data directly from server 1 to server 2.  Then, I can remotely call the restore procedure from server 1.

NOTE:  This only migrates the user databases.  You will still need to migrate the SQL logins and SQL Agent jobs, if necessary.

Install this procedure on server 2.  Here is the stored procedure:


USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[restoreDBrecovery] @name VARCHAR(25)
AS

/**
Install this stored procedure on the remote server. Create a path on the remote server for storing the backup files. Grant the necessary permissions to the folder.

This procedure will be called remotely from the primary database server.

This procedure receives a varchar parameter for database name. It locates the backup file and restores with recovery.

Gary Gwyn
2/4/14
**/

-- Logical file name extraction: great stuff via stackoverflow: http://stackoverflow.com/questions/7089627/how-can-i-retrieve-the-logical-file-name-of-the-database-from-backup-file
DECLARE @Table TABLE (
 LogicalName VARCHAR(128)
 ,[PhysicalName] VARCHAR(128)
 ,[Type] VARCHAR
 ,[FileGroupName] VARCHAR(128)
 ,[Size] VARCHAR(128)
 ,[MaxSize] VARCHAR(128)
 ,[FileId] VARCHAR(128)
 ,[CreateLSN] VARCHAR(128)
 ,[DropLSN] VARCHAR(128)
 ,[UniqueId] VARCHAR(128)
 ,[ReadOnlyLSN] VARCHAR(128)
 ,[ReadWriteLSN] VARCHAR(128)
 ,[BackupSizeInBytes] VARCHAR(128)
 ,[SourceBlockSize] VARCHAR(128)
 ,[FileGroupId] VARCHAR(128)
 ,[LogGroupGUID] VARCHAR(128)
 ,[DifferentialBaseLSN] VARCHAR(128)
 ,[DifferentialBaseGUID] VARCHAR(128)
 ,[IsReadOnly] VARCHAR(128)
 ,[IsPresent] VARCHAR(128)
 ,[TDEThumbprint] VARCHAR(128)
 )
DECLARE @LogicalNameData VARCHAR(128)
 ,@LogicalNameLog VARCHAR(128)

INSERT INTO @table
EXEC (
 '
RESTORE FILELISTONLY
 FROM DISK = ''M:\Backups\Full\' + @name + '.bak''
 '
 )

SET @LogicalNameData = (
 SELECT LogicalName
 FROM @Table
 WHERE Type = 'D'
 )
SET @LogicalNameLog = (
 SELECT LogicalName
 FROM @Table
 WHERE Type = 'L'
 )
DECLARE @command VARCHAR(250);

SET @command = 'RESTORE DATABASE ' + @name + ' FROM DISK = ''M:\Backups\Full\' + @name + '.bak''
WITH recovery, move ''' + @LogicalNameData + ''' to ''D:\MSSQL\Data\' + @name + '_Data.mdf'',
 move ''' + @LogicalNameLog + ''' to ''D:\MSSQL\Log\' + @name + '_Log.ldf'''
EXEC (@command);

Here is the code to execute on server 1.  This code will perform the backups and remotely call the restore procedure:


USE master;
GO

/*
Use the below code to migrate user databases from SERVER 1 to SERVER 2.

This method works great for small databases. You can migrate several small databases from one server to another with minimal application downtime.

Disconnect your end user applications, then execute the below script. It backs up databases from server 1 to a share on server 2. At the end of the script, it makes a remote call
to a stored procedure which resides on server 2. That stored procedure remotely restores the data with recovery.

!!! The only necessary prep work is to install the stored procedure on server 2 and ensure that permissions are configured properly for the backup destination.  You will also need to create a linked server on server 1 to access server 2 remotely.
For large databases, it would be best to schedule a full backup and full restore with NORECOVERY. When ready for the migration, you then disconnect the app, perform
 a differential backup, then perform a differential restore with recovery.

Gary Gwyn
2/4/14
************/
-- Backup the databases
EXEC sp_msforeachdb '
if (''?'' not in(''master'', ''msdb'', ''tempdb'', ''model''))
begin

BACKUP DATABASE [?] TO DISK = N''\\SERVER2\BACKUPS\Full\?.bak'' WITH COMPRESSION, INIT, NAME = ''? - Full''

end'

-- Restore the databases
EXEC sp_msforeachdb '
if (''?'' not in(''master'', ''msdb'', ''tempdb'', ''model''))
begin

exec [SERVER2].[master].[dbo].[restoredbrecovery] @name = ''?''

end'

Tagged , ,

Script for automatically testing SQL backups

Recently, I have spent some time writing a custom stored procedure that automates the process of restoring a copy of a database, running CHECKDB against the copy, then dropping the database.

That can be quite a tedious process if you have more than a few servers.

This script will be perfect if you have a DR server where backups are housed. Configure the paths on lines 90, 92, and 93. That is the only customization required. You can then schedule your backups to be tested for corruption. See the code below for more information.

Edit:  The code is not properly highlighted on WordPress.  However, I have tested the code and it works.

USE master;
GO

/*
Gary Gwyn
2/10/14
Create logging table for retaining a restore history
This table will be created when this script is ran the initial time.

During every step of the AutoRestore procedure, events are logged to the RestoreLog table.

I capture the following:
dbfilename: The Windows file name of the backup file that was restored
dbname: The name of the database that was restored by the stored proc
restoreStart: The time stamp when the restore began
restoreEnd: The time stamp when the restore completed
restoreStatus: Successful or Failed
checkDBStart: The time stamp when the checkDB began
checkDBEnd: The time stamp when the checkDB completed
checkDBStatus: Successful or Failed

*/
IF EXISTS (
 SELECT *
 FROM sys.objects
 WHERE object_id = OBJECT_ID(N'[dbo].[RestoreLog]')
 AND type IN (N'U')
 )
 DROP TABLE [dbo].[RestoreLog]
GO

CREATE TABLE dbo.RestoreLog (
 log_id INT identity(1, 1) PRIMARY KEY CLUSTERED
 ,dbfilename VARCHAR(150)
 ,dbname VARCHAR(50)
 ,restoreStart DATETIME
 ,restoreEnd DATETIME
 ,restoreStatus VARCHAR(12)
 ,checkDBStart DATETIME
 ,checkDBEnd DATETIME
 ,checkDBStatus VARCHAR(12)
 )
GO

IF EXISTS (
 SELECT *
 FROM sys.objects
 WHERE object_id = OBJECT_ID(N'[dbo].[usp_AutoRestore]')
 AND type IN (N'P', N'PC')
 )
 DROP PROCEDURE [dbo].[usp_AutoRestore]
GO

CREATE PROCEDURE dbo.usp_AutoRestore @DBtoLocate VARCHAR(25)
AS
/*
Gary Gwyn
2/10/14

Install this stored procedure on the SQL Server where you want to restore and test backups.
You can schedule this procedure to run for named databases.

USAGE: exec master..AutoRestore 'msdb'
The above example command will run the stored procedure against the msdb database.

The procedure will do the following:
Locate the most recent full backup of the named database (passed as parameter to SP).
Restore the most recent full backup as "DR_copy"
Run DBCC CheckDB against "DR_copy"
Drop "DR_copy"

At any point of failure, the stored procedure generates an error which will cause the scheduled job to fail and send a notification.

*/
DECLARE @Database VARCHAR(20);

SELECT TOP 1 @Database = NAME
FROM sys.databases
WHERE NAME LIKE '%' + @DBtoLocate + '%'
ORDER BY database_id

/************************************************
******
*** Configure these directories:::
*/
--This is the location where the SQL backup files are stored. Customize as needed.
DECLARE @Directory VARCHAR(100) = 'F:\mssql\backup\' + @@Servername + '\' + @Database + '\FULL';
--These are the directories where the restored database will be housed temporarily during the CheckDB stage. Customize as needed.
DECLARE @DestinationData VARCHAR(15) = 'D:\MSSQL\Data\DR_copy.mdf'
DECLARE @DestinationLog VARCHAR(15) = 'D:\MSSQL\Log\DR_copy.ldf'

/**************
End Configuration
***************/
-- Step 1 -- Locate most recent full backup
-- Credit to Patrick Keisler for providing help with the use of xp_dirtree.
-- http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
 DROP TABLE #FileList;

CREATE TABLE #FileList (
 id INT IDENTITY(1, 1)
 ,subdirectory NVARCHAR(512)
 ,depth INT
 ,isfile BIT
 );

INSERT #FileList (
 subdirectory
 ,depth
 ,isfile
 )
EXEC master.sys.xp_dirtree @Directory
 ,1
 ,1;

DECLARE @file_to_restore VARCHAR(150);

SELECT TOP 1 @file_to_restore = subdirectory
FROM #FileList
WHERE isfile = 1
 AND RIGHT(subdirectory, 4) = '.BAK'
ORDER BY subdirectory DESC;

DECLARE @RecordID INT;

-- If a file was located, create a new record in the RestoreLog table. Else, exit the stored procedure with an error.
IF @file_to_restore IS NOT NULL
BEGIN
 INSERT INTO master..RestoreLog (
 dbfilename
 ,dbname
 )
 VALUES (
 @file_to_restore
 ,@Database
 )

SET @RecordID = scope_identity();
END
ELSE
BEGIN
 INSERT INTO master..RestoreLog (
 dbfilename
 ,dbname
 ,restoreStatus
 ,checkDBStatus
 )
 VALUES (
 'Does not exist'
 ,@Database
 ,'Failed'
 ,'Failed'
 )

PRINT error_message();

RAISERROR (
 'Backup for database could not be located'
 ,16
 ,1
 );

RETURN
 /**** Exit stored procedure *****/
END

DECLARE @full_path VARCHAR(250);

SET @full_path = @Directory + '\' + @file_to_restore;

-- Step 2 -- Restore full backup as DR_copy
-- Update the RestoreLog table
UPDATE master..RestoreLog
SET restoreStart = getdate()
WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID

BEGIN TRY
 -- Logical file name extraction: great stuff via stackoverflow: http://stackoverflow.com/questions/7089627/how-can-i-retrieve-the-logical-file-name-of-the-database-from-backup-file
 DECLARE @Table TABLE (
 LogicalName VARCHAR(128)
 ,[PhysicalName] VARCHAR(128)
 ,[Type] VARCHAR
 ,[FileGroupName] VARCHAR(128)
 ,[Size] VARCHAR(128)
 ,[MaxSize] VARCHAR(128)
 ,[FileId] VARCHAR(128)
 ,[CreateLSN] VARCHAR(128)
 ,[DropLSN] VARCHAR(128)
 ,[UniqueId] VARCHAR(128)
 ,[ReadOnlyLSN] VARCHAR(128)
 ,[ReadWriteLSN] VARCHAR(128)
 ,[BackupSizeInBytes] VARCHAR(128)
 ,[SourceBlockSize] VARCHAR(128)
 ,[FileGroupId] VARCHAR(128)
 ,[LogGroupGUID] VARCHAR(128)
 ,[DifferentialBaseLSN] VARCHAR(128)
 ,[DifferentialBaseGUID] VARCHAR(128)
 ,[IsReadOnly] VARCHAR(128)
 ,[IsPresent] VARCHAR(128)
 ,[TDEThumbprint] VARCHAR(128)
 )
 DECLARE @LogicalNameData VARCHAR(128)
 ,@LogicalNameLog VARCHAR(128)

INSERT INTO @table
 EXEC (
 '
RESTORE FILELISTONLY
FROM DISK = ''' + @full_path + '''
'
 )

SET @LogicalNameData = (
 SELECT LogicalName
 FROM @Table
 WHERE Type = 'D'
 )
 SET @LogicalNameLog = (
 SELECT LogicalName
 FROM @Table
 WHERE Type = 'L'
 )

RESTORE DATABASE [DR_copy]
 FROM DISK = @full_path
 WITH FILE = 1
 ,MOVE @logicalnamedata TO @DestinationData
 ,MOVE @logicalnamelog TO @DestinationLog
 ,NOUNLOAD
 ,STATS = 10

-- Update the RestoreLog table
 UPDATE master..RestoreLog
 SET restoreEnd = getdate()
 ,restoreStatus = 'Successful'
 WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID
END TRY

-- If there was an error with the restore, then exit the stored procedure.
BEGIN CATCH
 PRINT error_message();

-- Update the RestoreLog table
 UPDATE master..RestoreLog
 SET restoreEnd = getdate()
 ,restoreStatus = 'Failed'
 WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID

RAISERROR (
 'Restore database step failed'
 ,16
 ,1
 );

RETURN
 /***** Exit stored procedure ******/
END CATCH

-- Step 3 -- Run DBCC CheckDB against DR_copy
-- Update the RestoreLog table
UPDATE master..RestoreLog
SET checkDBStart = getdate()
WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID

BEGIN TRY
 --The DBCC CheckDB command
 DBCC CHECKDB ([DR_copy])
 WITH NO_INFOMSGS
 ,ALL_ERRORMSGS
 ,DATA_PURITY

-- Update the RestoreLog table
 UPDATE master..RestoreLog
 SET checkDBEnd = getdate()
 ,checkDBStatus = 'Successful'
 WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID
END TRY

-- If there was an error with the checkDB command execution, then generate an error and exit the stored procedure.
BEGIN CATCH
 PRINT error_message();

-- Update the RestoreLog table
 UPDATE master..RestoreLog
 SET checkDBEnd = getdate()
 ,checkDBStatus = 'Failed'
 WHERE dbfilename = @file_to_restore
 AND log_id = @RecordID

RAISERROR (
 'DBCC CheckDB step failed'
 ,16
 ,1
 );

RETURN
 /*** exit stored procedure ****/
END CATCH

-- Step 4 -- Drop DR_copy
IF EXISTS (
 SELECT NAME
 FROM sys.databases
 WHERE NAME = 'DR_copy'
 )
 DROP DATABASE DR_copy
GO

Tagged , , ,

My Favorite SQL websites

SQL blogs:

Brent Ozar is king.  http://brentozar.com — The best resource for FREE SQL training.  Brent’s team hosts a weekly webcast on Tuesdays.  There is also an abundance of videos and HOWTO articles posted on the blog page.

Whenever I search Google for help with SQL syntax, I frequently see two sites near the top:  Technet/BOL and Pinal Dave’s blog.  Both are great resources for T-SQL programming syntax.

SQL Forums:

http://stackoverflow.com

http://sqlservercentral.com

Microsoft / Tech

Scott Hanselman is an excellent speaker.  His technical content is geared towards C# / ASP.Net developers, but I routinely read his blog because he frequently posts on “general tech” / “general Microsoft” items.

Database information (online, readonly, backed up, etc.)

Have you ever noticed any inconsistencies in the various states of your databases?  Online? Read_only? Mirrored?  Not backed up?!?

I wrote a brief script that I could run on my central management server.  This script provides database information relating to the status of:  online, read only, mirrored, backed up locally.

/*

Gary Gwyn
1/27/14

This query provides information for all databases on a server:
Name: database name
Status: online or offline
ReadOnly: yes or no
Mirrored: yes or no
BackedUpLocally: yes or no (1 Full backup in past 24 hours)

*******************************/
SELECT db.NAME AS 'Name'
 ,db.state_desc AS 'Status'
 ,CASE
 WHEN db.is_read_only = 0
 THEN ''
 ELSE 'YES'
 END AS 'ReadOnly'
 ,CASE
 WHEN m.mirroring_state IS NOT NULL
 THEN 'YES'
 ELSE ''
 END AS 'Mirrored'
 ,CASE
 WHEN db.NAME IN (
 SELECT DISTINCT database_name
 FROM msdb..backupset
 WHERE backup_finish_date > GETDATE() - 1
 AND type = 'D'
 )
 THEN 'YES'
 ELSE ''
 END AS 'BackedUpLocally'
FROM sys.databases db
INNER JOIN sys.database_mirroring m ON db.database_id = m.database_id

Tagged , , , ,

Delete SQL Agent Jobs

I’ve recently been tasked with standardizing SQL maintenance jobs for a farm of several dozen SQL servers.  The previous DBA had a mixture of maintenance plans and custom scripts in place for scheduled maintenance.  I knew ahead of time that I wanted to use a Central Management Server and then mass deploy my maintenance job scripts (I use Ola Hallengren’s scripts).

I ran into a problem where I prematurely published maintenance scripts to all servers.  I also had some jobs enabled by default, while others were disabled by default.  The disabled jobs were scripts that were not quite ready for production.  In hindsight, I wished that I had never deployed those scripts.  So, I had the idea “Let’s delete every disabled job from every server”.  Doing so would allow me to start with a clean slate.

There is a system stored procedure for deleting jobs:   sp_delete_job

One caveat with deleting jobs is the @delete_unused_schedule parameter.  If unspecified, the stored procedure will delete the schedule that is associated with the deleted job (given that no other jobs are associated to the schedule).  I found out the hard way.  See the above link for writing your own job deletion code, or read further for a script to delete all disabled jobs from a server.  You can also run this on your Central Management Server to delete disabled jobs from all registered servers.


/***

Delete all disabled jobs.

Use a cursor to delete each job that is currently disabled.

Gary Gwyn

http://sqlexchange.wordpress.com

1/21/14
*************************/
DECLARE @j UNIQUEIDENTIFIER;
DECLARE @name VARCHAR(100);

DECLARE j_cursor CURSOR
FOR
SELECT job_id, NAME
FROM msdb..sysjobs
WHERE enabled = 0

OPEN j_cursor

FETCH NEXT
FROM j_cursor
INTO @j, @name

WHILE @@fetch_status = 0
BEGIN
 EXEC msdb..sp_delete_job @job_id = @j, @delete_unused_schedule = 0

PRINT 'Deleted job: ' + @name

FETCH NEXT
 FROM j_cursor
 INTO @j, @name
END

CLOSE j_cursor

DEALLOCATE j_cursor

Tagged , , , ,

Format your SQL code

I love the look of neatly formatted source code, but I hate writing it!  For me personally, it is much easier to ready neatly aligned and properly formatted code.

There is a free tool available which will allow you to apply automatic formatting against new and existing scripts.  A plugin is available for SQL Management Studio and for my favorite text editor (Notepad++).  The application is called Poor Man’s T-SQL Formatter.  I love the look of capitalized reserved keywords.

Tagged ,
Follow

Get every new post delivered to your Inbox.