Create SQL Script to Backup/Restore databases

Create SQL Script to create SQL Script to backup databases and to restore databases.

- Fix owner user : 'sa'

- Shrink databases

Tip: You can use a shared folder;

Tip: You can disconnect all users before to restore a database;

Tip: You can add this SQL Script to protect executing on the wrong SQL Server;

IF (NOT(@@servername = 'MyServerName'))
BEGIN
   PRINT 'You MUST execute over MyServerName';
   RETURN;
END

SQL Stript to backup databases

PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to backup databases'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT 'USE Master;'
DECLARE @BackupPath NVARCHAR(MAX) = NULL;
IF (@BackupPath IS NULL)
BEGIN
    PRINT '-- ';
    PRINT '-- Warning : you MUST "set @BackupPath"';
    PRINT '-- ';
    return;
END
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4)

DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
PRINT ''-- Database "?" '';
backup database [?] to disk = ''@BackupPath\?_backup.bak'' WITH NOFORMAT, INIT, NAME = N''?-Full Database Backup for migration'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
GO
';
SET @sqltemplate = REPLACE(@sqltemplate, '@BackupPath', @BackupPath);
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
-- EXEC(@sql); -- you can execute it right now.
END;

SQL Script to restore databases

print '-- SQL Script to restore databases'

PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to RESTORE databases'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT 'USE Master;'
DECLARE @BackupPath      NVARCHAR(MAX) =  null;
DECLARE @RestoreDataPath NVARCHAR(MAX) =  null;
DECLARE @RestoreLogPath  NVARCHAR(MAX) =  null;

IF (@BackupPath IS NULL)
BEGIN
    PRINT '-- ';
    PRINT '-- Warning : you MUST "set @BackupPath"';
    PRINT '-- ';
    return;
END
IF (@RestoreDataPath IS NULL)
BEGIN
    PRINT '-- ';
    PRINT '-- Warning : you MUST "set @RestoreDataPath"';
    PRINT '-- ';
    return;
END
IF (@RestoreLogPath IS NULL)
BEGIN
    PRINT '-- ';
    PRINT '-- Warning : you MUST "set @RestoreLogPath"';
    PRINT '-- ';
    return;
END
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE' AND (f.database_id > 4)

WHILE ((SELECT count(*) FROM #db)>0) BEGIN
    SET @sql = '';
    SELECT TOP 1 @dbname = name FROM #db f ORDER BY f.name;
    --
    SET NOCOUNT ON;
    SELECT  
        -- f.NAME,
        @sql = 'RESTORE DATABASE ['+f.name +'] FROM  DISK = N'''+@BackupPath+'\'+f.name+'_backup.bak'' with file = 1, MOVE N'''+g.name+''' TO N'''+@RestoreDataPath+'\'+ g.name +'.mdf'', '
        + stuff((
                SELECT DISTINCT ', MOVE N''' + cast(m.NAME AS VARCHAR(4096)) + ''' TO N'+@RestoreLogPath+'\'+cast(m.NAME AS VARCHAR(1024))+'.ldf'',  NOUNLOAD,  REPLACE,  STATS = 5;' AS [text()]
                FROM sys.master_files AS m
                INNER JOIN sys.databases AS d ON m.database_id = d.database_id
                WHERE (m.type_desc = 'LOG') AND f.database_id = d.database_id
                FOR XML path('')
                ), 1, 1, '')  
    FROM sys.master_files g INNER JOIN sys.databases f ON g.database_id = f.database_id
    WHERE  type_desc = 'ROWS' AND data_space_id = 1 AND f.name = @dbname
    --
    
    DELETE FROM #db WHERE name = @dbname;
    PRINT @sql;
END;

Restore database with check database logical names.

PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to restore databases'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT 'USE Master;'
DECLARE @BackupPath NVARCHAR(MAX) = null;
DECLARE @DataPath   NVARCHAR(MAX) = null;
DECLARE @LogPath    NVARCHAR(MAX) = null;
IF (@BackupPath IS NULL)
BEGIN
    PRINT '-- ';
    PRINT '-- Warning : you MUST "set @BackupPath"';
    PRINT '-- ';
    RETURN;
END
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4);

BEGIN TRY DROP TABLE #dbprob; END TRY BEGIN CATCH END CATCH;

SELECT db.name AS name ,
'ALTER DATABASE ['+db.name +'] MODIFY FILE (NAME=N'''+ m.name + ''',NEWNAME=N'''+
LEFT(REVERSE(LEFT(REVERSE(m.Physical_Name),CHARINDEX('\', REVERSE(m.Physical_Name), 1) - 1)),
LEN(REVERSE(LEFT(REVERSE(m.Physical_Name),CHARINDEX('\', REVERSE(m.Physical_Name), 1) - 1))) - 4) +''')' AS SqlScript
INTO #dbprob
FROM sys.master_files m INNER JOIN sys.databases db ON db.database_id = m.database_id WHERE db.database_id>4 AND m.Physical_Name NOT LIKE '%'+m.name+'%'


IF (EXISTS(SELECT * FROM #dbprob))
BEGIN
    PRINT '-- Warning --';
    PRINT '-- Warning --';
    PRINT '';
    PRINT '-- This databases the logical names don’t match with physical files names.';
    SET @sql = '';
    SELECT @sql = @sql + '--     [' + [name] + ']
' FROM #dbprob;
    PRINT @sql;
    PRINT '';
    PRINT '-- All databases then you could have problem are on top of the script.';
    PRINT '';
    PRINT 'You can fix the logical names with this script: '
    PRINT '/*'
    PRINT '-- Execute it on origin SQL Server'
    SET @sql = '';
    SELECT @sql = @sql + [SqlScript] + '
GO
'   FROM #dbprob;
    PRINT @sql;


    PRINT '*/'
    PRINT '-- If you have fixed the logical names you must create this script again.'
    PRINT '-- Warning --';
    PRINT '-- Warning --';
END

 

SET @sql = '
GO
EXEC sp_configure ''show advanced options'', 1; ---- To allow advanced options to be changed.
RECONFIGURE;                                    ---- To update the currently configured value for advanced options.
EXEC sp_configure ''xp_cmdshell'', 1;           ---- To enable the feature.
RECONFIGURE;                                    ---- To update the currently configured value for this feature.
GO
EXEC xp_cmdshell ''MD @DataPath'';              ---- Create data path if is not exists.
GO
EXEC xp_cmdshell ''MD @LogPath'';               ---- Create data path if is not exists.
GO
';
SET @sql = REPLACE(@sql,'@DataPath',@DataPath);
SET @sql = REPLACE(@sql,'@LogPath',@LogPath);
PRINT @sql;

DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
PRINT ''-- Restore Database [@dbname] '';
@SqlScriptKillAllConnection
RESTORE DATABASE [@dbname] FROM DISK = N''@BackupPath\@dbname_backup.bak'' with file = 1 @MoveDataFiles, NOUNLOAD, REPLACE, STATS = 5;
GO
';
DECLARE @sqltemplateMoveFiles nvarchar(MAX) = '
, MOVE N''@MasterName'' TO N''@DataPath\@MasterName.@MasterExtension'' ';

 

DECLARE @SqlScriptKillAllConnection  NVARCHAR(MAX) = 'PRINT ''Kill connection of database: @dbname'';DECLARE @kill AS NVARCHAR (MAX);SET @kill = '''';SELECT @kill = @kill + ''begin try  kill '' + CAST (spid AS NVARCHAR (10)) + ''; print ''''Kill spid: '' + CAST (spid AS NVARCHAR (10)) + '' ('' + RTRIM(ISNULL(hostname, ''*NULL*'')) + ''|'' + RTRIM(ISNULL(nt_username, ''*NULL*'')) + '')'''' ; end try begin catch print ''''Fail to kill : '' + CAST (spid AS NVARCHAR (10)) + ''''''; end catch;'' FROM master..sysprocesses WHERE dbid = DB_ID(''@dbname'') AND spid <> @@spid AND hostprocess <> '''';EXEC (@kill);';

 

DECLARE @ScriptMoveFiles NVARCHAR(MAX) = '';

SET @sqltemplate = REPLACE(@sqltemplate, '@SqlScriptKillAllConnection', @SqlScriptKillAllConnection);
SET @sqltemplate = REPLACE(@sqltemplate, '@BackupPath', @BackupPath);
WHILE ((SELECT count(*)    FROM #db)>0)
BEGIN
    PRINT 'GO'
    SET @sql = '';
    SET @dbname = NULL;
    SELECT TOP 1 @dbname = name, @sql = REPLACE(@sqltemplate, '@dbname', name) FROM #dbprob f ORDER BY f.name;
    IF (@dbname IS NOT NULL)
    BEGIN
        PRINT '-- Warning this database has identical logical name of another database'
        PRINT '-- You MUST check file name used to move.';
    END
    IF (@dbname IS NULL) SELECT TOP 1 @dbname = name, @sql = REPLACE(@sqltemplate, '@dbname', name) FROM #db f ORDER BY f.name;
    DELETE FROM #dbprob WHERE name = @dbname;
    DELETE FROM #db WHERE name = @dbname;

    SET @ScriptMoveFiles = '';
    SELECT @ScriptMoveFiles = @ScriptMoveFiles
    + REPLACE(REPLACE(@sqltemplateMoveFiles,'@MasterName',m.name),'@MasterExtension',(REVERSE(LEFT(REVERSE(m.Physical_Name),CHARINDEX('.', REVERSE(m.Physical_Name), 1) - 1))))
    FROM   sys.master_files AS m INNER JOIN sys.databases AS d ON m.database_id = d.database_id WHERE d.name = @dbname;
    
    SET @sql = REPLACE(@sql,'@MoveDataFiles',@ScriptMoveFiles);
    SET @sql = REPLACE(@sql,'@DataPath',@DataPath);
    SET @sql = REPLACE(@sql,'@LogPath',@LogPath);
    PRINT @sql;
    -- EXEC(@sql); -- you can execute it right now.
END;

GO

SQL Script to fix all local users over all databases

print '-- SQL Script to fix all local users over all databases'
SET NOCOUNT ON;
SELECT 'USE ['+ f.name+'];'+'DECLARE @username VARCHAR(25); DECLARE GetOrphanUsers CURSOR FOR SELECT UserName = NAME FROM sysusers WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND SUSER_SNAME(sid) IS NULL ORDER BY NAME; OPEN GetOrphanUsers; FETCH NEXT FROM GetOrphanUsers INTO @username; WHILE (@@FETCH_STATUS = 0) BEGIN IF @username = ''dbo'' EXEC sp_changedbowner ''sa''; ELSE EXEC sp_change_users_login ''update_one'', @username, @username; FETCH NEXT FROM GetOrphanUsers INTO @username; END; CLOSE GetOrphanUsers; DEALLOCATE GetOrphanUsers;' + CHAR(13) + CHAR(10) + 'GO'
FROM sys.databases f
WHERE (f.database_id > 4)  -- no system databases
ORDER BY f.name

SQL Script to :
* fix owner as 'sa'
* Recovery mode to simple 
* Shrink Database

print '-- SQL Script to fix owner and Recovery mode'
SET NOCOUNT ON;
SELECT
+ 'PRINT '''+f.name+ '''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
+'USE [MASTER]; ALTER DATABASE ['+ f.name+'] SET RECOVERY SIMPLE WITH NO_WAIT;'+ CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +
+'USE ['+ f.name+']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;;' + CHAR(13) + CHAR(10) + 'GO'+ CHAR(13) + CHAR(10)
+'DBCC SHRINKDATABASE(N'''+ f.name+''');' + CHAR(13) + CHAR(10) + 'GO'
FROM sys.databases f
WHERE (f.database_id > 4)  -- no system databases
ORDER BY f.name