A complete T-SQL based procedure to migrate SQL Server instances — including logins, databases, and file relocations — with no external tools required.

Changelog

• (05/04/2014) Added compression to reduce bandwidth, space and transfer time
• Reduced stat value for very large databases
• Added backup type parameter: FULL, FULL_COPYONLY or DIFFERENTIAL
• Added Maxtransfersize and Buffercount parameters to improve backup performance (warning: requires more memory)
• Corrected a bug to show correct error messages of any kind

Introduction

There are many ways to migrate databases: backup-restore, detach-copy-attach, SQL database copy tool, and so on. This procedure uses the backup-restore approach — more flexible than others and requiring less bandwidth and disk space. Every DBA must choose the right strategy for each scenario, but this approach is one of the most adaptable for complex instances with many large databases.

The macro-steps are:

1. Migrate instance logins keeping the original SID and password hash
2. Backup databases and transfer to the destination site
3. Restore databases relocating data files to the new filesystem

Step 1 — Migrate Logins with Original SID and Password Hash

This step transfers logins from the source instance while preserving both the original password hash and the original SID. The SID alignment between instance logins and database users is critical to avoid orphaned users after restore. The procedure differs across SQL Server versions.

Official MS references: KB246133 (SQL 2000) · KB918992 (SQL 2005, 2008, 2012)

Run the following script on the source instance and save the complete output as text (ALT+T in SSMS before executing), then run the output on the destination instance:

USE [master]
GO

/****** Object: UserDefinedFunction [dbo].[fn_hexadecimal] *****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal]
(
    @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
        DECLARE @tempint int
        DECLARE @firstint int
        DECLARE @secondint int
        SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
        SELECT @firstint = FLOOR(@tempint/16)
        SELECT @secondint = @tempint - (@firstint*16)
        SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
        SELECT @i = @i + 1
    END
    return @charvalue
END
GO

SET NOCOUNT ON
GO

PRINT '-- Create Windows logins'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''')
CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' +
default_database_name + '], DEFAULT_LANGUAGE=[us_english]
GO
'
FROM master.sys.server_principals
WHERE type_desc IN ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] NOT LIKE 'BUILTIN%'
AND [NAME] NOT LIKE 'NT AUTHORITY%'
AND [name] NOT LIKE '%SQLServer%'
GO

PRINT '-- Create SQL Logins'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
CREATE LOGIN [' + [name] + ']
WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED,
SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ',
DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
ALTER LOGIN [' + [name] + ']
WITH CHECK_EXPIRATION=' +
CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' +
CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO
'
FROM master.sys.sql_logins
WHERE type_desc = 'SQL_LOGIN'
AND [name] NOT IN ('sa', 'guest')
GO

Step 2 — Backup Databases and Transfer to Destination

This metascript generates and executes the backup script, skipping and logging errors without halting the entire process — essential on large instances with many databases:

----------------------------------------------------
-- CONFIGURE HERE
----------------------------------------------------
-- DESTINATION PATH
    declare @destpath nvarchar(255) = 'G:ackup'
-- BACKUP TYPE: FULL_COPYONLY | FULL | DIFF
    declare @bck_type nvarchar(25) = 'FULL_COPYONLY'
-- DATABASE LIST
    create table #dbs (dbname nvarchar(255))
    insert into #dbs values('db1')
    insert into #dbs values('db2')
    insert into #dbs values('db3')
-- To backup ALL user databases:
-- Insert into #dbs select name from master..sysdatabases
--   where name not in ('master','model','msdb','tempdb')
----------------------------------------------------

declare @aDB nvarchar(255)
declare @aQry nvarchar(2048)

DECLARE em_cur CURSOR FOR select * from #dbs
OPEN em_cur
FETCH NEXT FROM em_cur INTO @aDB
WHILE @@FETCH_STATUS = 0
BEGIN
   set @aQry = 'BACKUP DATABASE [' + @aDB + '] TO DISK = ''' +
                 @destpath + @aDB + '.bak'' WITH NOFORMAT, NOINIT, NAME = N''' + @aDB +
                 '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 3, COMPRESSION'
   -- ADD buffercount=64, maxtransfersize=4194304 for better performance
   -- WARNING: requires more memory — use only when safe to do so
   if (@bck_type='DIFF')
       set @aQry = @aQry + ',DIFFERENTIAL'
   else if (@bck_type='FULL_COPYONLY')
       set @aQry = @aQry + ',COPY_ONLY'

   begin try
       print '-------------'
       print @aQry
       exec (@aQry)
       print '-------------'
   end try
   begin catch
       print 'ERROR during backup: ' + ERROR_MESSAGE()
   end catch

   FETCH NEXT FROM em_cur INTO @aDB
END

CLOSE em_cur
DEALLOCATE em_cur
drop table #dbs

Step 3 — Restore Databases with File Relocation

Run this metascript on the source instance to generate the restore script, then execute the output on the destination instance to restore all databases with data files relocated to the correct paths:

set nocount on
----------------------------------------------------
-- CONFIGURE HERE
----------------------------------------------------
-- backup files position
    declare @restorepath nvarchar(max) = 'G:ackup'
-- data files destination
    declare @pathdata nvarchar(max) = 'D:DataPath'
-- log files destination
    declare @pathlog nvarchar(max)  = 'L:LogPath'
-- DATABASE LIST
    create table #dbs (dbname nvarchar(255))
    insert into #dbs values('db1')
    insert into #dbs values('db2')
    insert into #dbs values('db3')
-- Insert into #dbs select name from master..sysdatabases
----------------------------------------------------

declare @aDB nvarchar(255)
declare em_cur CURSOR FOR select * from #dbs

OPEN em_cur
FETCH NEXT FROM em_cur INTO @aDB
WHILE @@FETCH_STATUS = 0
BEGIN
    select 'RESTORE DATABASE [' + @aDB + '] from disk=N''' + @restorepath + @aDB + '.bak'' WITH '
    union all
    select
    CASE
    WHEN m.type_desc = 'ROWS' THEN 'MOVE N''' + m.name + ''' TO N''' +
    @pathdata + RIGHT(physical_name, CHARINDEX('', REVERSE(physical_name))-1) + ''','
    WHEN m.type_desc = 'LOG'  THEN 'MOVE N''' + m.name + ''' TO N''' +
    @pathlog  + RIGHT(physical_name, CHARINDEX('', REVERSE(physical_name))-1) + ''','
    END
    from sys.master_files m
    inner join sys.databases d on (m.database_id = d.database_id)
    where d.name=@aDB
    union all
    select 'NOUNLOAD, STATS = 10'
    print 'GO'

    FETCH NEXT FROM em_cur INTO @aDB
END

CLOSE em_cur
DEALLOCATE em_cur
drop table #dbs

Take the generated script and run it on the destination instance to restore every database in the correct position.

Note: SQL Server 2012 Login Hash Workaround

(Update 22-07-2012) In testing on SQL 2012, the official MS procedure to export login password hashes does not function correctly. To export logins with the original password hash AND original SID, use this workaround:

select
    convert(varchar(50), 'CREATE LOGIN [' + name + '] with password='),
    CAST(password AS VARBINARY(26)),
    'HASHED,SID=',
    CAST(sid AS VARBINARY(26))
from sys.syslogins
where password is not null

''' } # POST 430 - Agents of SHIELD su Netflix (2016-11-08) posts[430] = { 'date': '2016-11-08 17:26:19', 'content': '''

Finalmente disponibili! Con l'arrivo della stagione 4 di Agents of S.H.I.E.L.D. sugli schermi americani, Netflix ha aggiunto al proprio catalogo italiano le prime stagioni della serie. Se non avete ancora visto le avventure dell'agente Coulson e del suo team, è il momento perfetto per recuperarle.

Un universo Marvel tutto da scoprire

Agents of S.H.I.E.L.D. è nata nel 2013 come spin-off cinematografico dell'Universo Marvel, ambientata nello stesso mondo degli Avengers ma con un focus su agenti umani, tecnologie avanzate e minacce meno note al grande pubblico. La serie ha saputo costruire nel tempo una mitologia propria, con personaggi profondi e colpi di scena che spesso dialogano direttamente con gli eventi dei film Marvel.

Si vocifera di futuri cross-over tra Agents e le serie Netflix — Daredevil e l'agente Coulson si conosceranno presto? L'idea entusiasma i fan di entrambe le produzioni, che da tempo sperano in una maggiore integrazione tra i due rami della Marvel Television.

Per chi non avesse ancora visto la serie, è questo il momento di recuperare le prime puntate. Buona visione!

''' } # POST 752 - C'era Una Volta (2016-12-10) posts[752] = { 'date': '2016-12-10 12:24:48', 'content': '''

Biancaneve, Cappuccetto Rosso, Pinocchio... sono tutti titoli che ci rimandano indietro nel tempo alle favole della buonanotte. Storie della tradizione popolare che, magari con qualche variazione, hanno attraversato i secoli giungendo fino a noi. Sarebbe facile quindi sottovalutare "C'era una volta" (Once Upon a Time) ricollegando i suoi personaggi alle favole per bambini. Ma sarebbe un errore.

Un abile stratagemma narrativo

Gli autori si sono messi di impegno per trasportare con un abile stratagemma narrativo tutti quei personaggi per noi familiari ai giorni nostri. "Abile" forse non è la parola giusta, visto che quando si usa la carta della magia tutto diventa semplice... tuttavia il risultato è assolutamente apprezzabile.

Non dovete pensare a una deriva fantasy dei personaggi delle favole raccolte dai Fratelli Grimm, bensì a un trasporto in chiave contemporanea dei personaggi che conoscete da una vita. La piccola città di Storybrooke, nel Maine, è il luogo in cui questi personaggi vivono ignari della loro vera natura, intrappolati da una maledizione che ha rubato loro la memoria.

Contaminazioni narrative da non perdere

La serie forse a tratti è un po' lenta e a volte le manca quella verve necessaria per tenervi incollati allo schermo... tuttavia è assolutamente interessante. L'effetto dei personaggi fuori dal loro contesto è particolare ed estraneante, come anche la revisione delle favole in cui troverete più di una contaminazione della letteratura più recente.

Un episodio tra tutti, facendo attenzione a non spoilerare: Cappuccetto Rosso e il lupo diventa una trasposizione horror-gotica molto, molto diversa dall'originale. Ma non posso dire altro.

Qualche purista urlerà al sacrilegio della letteratura classica... io invece le contaminazioni narrative le adoro. Sono sinonimo del fatto che le storie continuano a vivere e che hanno ancora qualcosa da dire.

Vale la pena vederla?

La serie vale assolutamente la pena. Dategli qualche episodio per ingranare — per dare spessore ai personaggi serve un po'. Poi sicuramente l'apprezzerete.

Benvenuti a Storybrooke... una piccola cittadina del Maine dove tutto è possibile.