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;
1
2IF (NOT(@@servername = 'MyServerName'))
3BEGIN
4 PRINT 'You MUST execute over MyServerName';
5 RETURN;
6END
SQL Stript to backup databases
1
2PRINT 'PRINT ''----------------------'';';
3PRINT 'PRINT ''-- Script to backup databases'';';
4PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
5PRINT 'PRINT ''----------------------'';';
6PRINT 'USE Master;'
7DECLARE @BackupPath NVARCHAR(MAX) = NULL;
8IF (@BackupPath IS NULL)
9BEGIN
10 PRINT '-- ';
11 PRINT '-- Warning : you MUST "set @BackupPath"';
12 PRINT '-- ';
13 return;
14END
15SET NOCOUNT ON;
16DECLARE @sql nvarchar(MAX);
17DECLARE @dbname nvarchar(255);
18BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
19SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
20AND (f.database_id > 4)
21
22DECLARE @sqltemplate nvarchar(MAX);
23SET @sqltemplate = '
24PRINT ''-- Database "?" '';
25backup database [?] to disk = ''@BackupPath\?_backup.bak'' WITH NOFORMAT, INIT, NAME = N''?-Full Database Backup for migration'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
26GO
27';
28SET @sqltemplate = REPLACE(@sqltemplate, '@BackupPath', @BackupPath);
29WHILE ((SELECT count(*) FROM #db)>0) BEGIN
30SET @sql = '';
31SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
32DELETE FROM #db WHERE name = @dbname;
33PRINT @sql;
34-- EXEC(@sql); -- you can execute it right now.
35END;
SQL Script to restore databases
1
2print '-- SQL Script to restore databases'
3
4PRINT 'PRINT ''----------------------'';';
5PRINT 'PRINT ''-- Script to RESTORE databases'';';
6PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
7PRINT 'PRINT ''----------------------'';';
8PRINT 'USE Master;'
9DECLARE @BackupPath NVARCHAR(MAX) = null;
10DECLARE @RestoreDataPath NVARCHAR(MAX) = null;
11DECLARE @RestoreLogPath NVARCHAR(MAX) = null;
12
13IF (@BackupPath IS NULL)
14BEGIN
15 PRINT '-- ';
16 PRINT '-- Warning : you MUST "set @BackupPath"';
17 PRINT '-- ';
18 return;
19END
20IF (@RestoreDataPath IS NULL)
21BEGIN
22 PRINT '-- ';
23 PRINT '-- Warning : you MUST "set @RestoreDataPath"';
24 PRINT '-- ';
25 return;
26END
27IF (@RestoreLogPath IS NULL)
28BEGIN
29 PRINT '-- ';
30 PRINT '-- Warning : you MUST "set @RestoreLogPath"';
31 PRINT '-- ';
32 return;
33END
34SET NOCOUNT ON;
35DECLARE @sql nvarchar(MAX);
36DECLARE @dbname nvarchar(255);
37BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
38SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE' AND (f.database_id > 4)
39
40WHILE ((SELECT count(*) FROM #db)>0) BEGIN
41 SET @sql = '';
42 SELECT TOP 1 @dbname = name FROM #db f ORDER BY f.name;
43 --
44 SET NOCOUNT ON;
45 SELECT
46 -- f.NAME,
47 @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'', '
48 + stuff((
49 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()]
50 FROM sys.master_files AS m
51 INNER JOIN sys.databases AS d ON m.database_id = d.database_id
52 WHERE (m.type_desc = 'LOG') AND f.database_id = d.database_id
53 FOR XML path('')
54 ), 1, 1, '')
55 FROM sys.master_files g INNER JOIN sys.databases f ON g.database_id = f.database_id
56 WHERE type_desc = 'ROWS' AND data_space_id = 1 AND f.name = @dbname
57 --
58
59 DELETE FROM #db WHERE name = @dbname;
60 PRINT @sql;
61END;
Restore database with check database logical names.
1
2PRINT 'PRINT ''----------------------'';';
3PRINT 'PRINT ''-- Script to restore databases'';';
4PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
5PRINT 'PRINT ''----------------------'';';
6PRINT 'USE Master;'
7DECLARE @BackupPath NVARCHAR(MAX) = null;
8DECLARE @DataPath NVARCHAR(MAX) = null;
9DECLARE @LogPath NVARCHAR(MAX) = null;
10IF (@BackupPath IS NULL)
11BEGIN
12 PRINT '-- ';
13 PRINT '-- Warning : you MUST "set @BackupPath"';
14 PRINT '-- ';
15 RETURN;
16END
17SET NOCOUNT ON;
18DECLARE @sql nvarchar(MAX);
19DECLARE @dbname nvarchar(255);
20BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
21SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
22AND (f.database_id > 4);
23
24BEGIN TRY DROP TABLE #dbprob; END TRY BEGIN CATCH END CATCH;
25
26SELECT db.name AS name ,
27'ALTER DATABASE ['+db.name +'] MODIFY FILE (NAME=N'''+ m.name + ''',NEWNAME=N'''+
28LEFT(REVERSE(LEFT(REVERSE(m.Physical_Name),CHARINDEX('\', REVERSE(m.Physical_Name), 1) - 1)),
29LEN(REVERSE(LEFT(REVERSE(m.Physical_Name),CHARINDEX('\', REVERSE(m.Physical_Name), 1) - 1))) - 4) +''')' AS SqlScript
30INTO #dbprob
31FROM 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+'%'
32
33IF (EXISTS(SELECT * FROM #dbprob))
34BEGIN
35 PRINT '-- Warning --';
36 PRINT '-- Warning --';
37 PRINT '';
38 PRINT '-- This databases the logical names don’t match with physical files names.';
39 SET @sql = '';
40 SELECT @sql = @sql + '-- [' + [name] + ']
41' FROM #dbprob;
42 PRINT @sql;
43 PRINT '';
44 PRINT '-- All databases then you could have problem are on top of the script.';
45 PRINT '';
46 PRINT 'You can fix the logical names with this script: '
47 PRINT '/*'
48 PRINT '-- Execute it on origin SQL Server'
49 SET @sql = '';
50 SELECT @sql = @sql + [SqlScript] + '
51GO
52' FROM #dbprob;
53 PRINT @sql;
54
55 PRINT '*/'
56 PRINT '-- If you have fixed the logical names you must create this script again.'
57 PRINT '-- Warning --';
58 PRINT '-- Warning --';
59END
60
61SET @sql = '
62GO
63EXEC sp_configure ''show advanced options'', 1; ---- To allow advanced options to be changed.
64RECONFIGURE; ---- To update the currently configured value for advanced options.
65EXEC sp_configure ''xp_cmdshell'', 1; ---- To enable the feature.
66RECONFIGURE; ---- To update the currently configured value for this feature.
67GO
68EXEC xp_cmdshell ''MD @DataPath''; ---- Create data path if is not exists.
69GO
70EXEC xp_cmdshell ''MD @LogPath''; ---- Create data path if is not exists.
71GO
72';
73SET @sql = REPLACE(@sql,'@DataPath',@DataPath);
74SET @sql = REPLACE(@sql,'@LogPath',@LogPath);
75PRINT @sql;
76
77DECLARE @sqltemplate nvarchar(MAX);
78SET @sqltemplate = '
79PRINT ''-- Restore Database [@dbname] '';
80@SqlScriptKillAllConnection
81RESTORE DATABASE [@dbname] FROM DISK = N''@BackupPath\@dbname_backup.bak'' with file = 1 @MoveDataFiles, NOUNLOAD, REPLACE, STATS = 5;
82GO
83';
84DECLARE @sqltemplateMoveFiles nvarchar(MAX) = '
85, MOVE N''@MasterName'' TO N''@DataPath\@MasterName.@MasterExtension'' ';
86
87DECLARE @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);';
88
89DECLARE @ScriptMoveFiles NVARCHAR(MAX) = '';
90
91SET @sqltemplate = REPLACE(@sqltemplate, '@SqlScriptKillAllConnection', @SqlScriptKillAllConnection);
92SET @sqltemplate = REPLACE(@sqltemplate, '@BackupPath', @BackupPath);
93WHILE ((SELECT count(*) FROM #db)>0)
94BEGIN
95 PRINT 'GO'
96 SET @sql = '';
97 SET @dbname = NULL;
98 SELECT TOP 1 @dbname = name, @sql = REPLACE(@sqltemplate, '@dbname', name) FROM #dbprob f ORDER BY f.name;
99 IF (@dbname IS NOT NULL)
100 BEGIN
101 PRINT '-- Warning this database has identical logical name of another database'
102 PRINT '-- You MUST check file name used to move.';
103 END
104 IF (@dbname IS NULL) SELECT TOP 1 @dbname = name, @sql = REPLACE(@sqltemplate, '@dbname', name) FROM #db f ORDER BY f.name;
105 DELETE FROM #dbprob WHERE name = @dbname;
106 DELETE FROM #db WHERE name = @dbname;
107
108 SET @ScriptMoveFiles = '';
109 SELECT @ScriptMoveFiles = @ScriptMoveFiles
110 + REPLACE(REPLACE(@sqltemplateMoveFiles,'@MasterName',m.name),'@MasterExtension',(REVERSE(LEFT(REVERSE(m.Physical_Name),CHARINDEX('.', REVERSE(m.Physical_Name), 1) - 1))))
111 FROM sys.master_files AS m INNER JOIN sys.databases AS d ON m.database_id = d.database_id WHERE d.name = @dbname;
112
113 SET @sql = REPLACE(@sql,'@MoveDataFiles',@ScriptMoveFiles);
114 SET @sql = REPLACE(@sql,'@DataPath',@DataPath);
115 SET @sql = REPLACE(@sql,'@LogPath',@LogPath);
116 PRINT @sql;
117 -- EXEC(@sql); -- you can execute it right now.
118END;
119
120GO
SQL Script to fix all local users over all databases
1
2print '-- SQL Script to fix all local users over all databases'
3SET NOCOUNT ON;
4SELECT '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'
5FROM sys.databases f
6WHERE (f.database_id > 4) -- no system databases
7ORDER BY f.name
SQL Script to : * fix owner as ‘sa’ * Recovery mode to simple * Shrink Database
1
2print '-- SQL Script to fix owner and Recovery mode'
3SET NOCOUNT ON;
4SELECT
5+ 'PRINT '''+f.name+ '''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
6+'USE [MASTER]; ALTER DATABASE ['+ f.name+'] SET RECOVERY SIMPLE WITH NO_WAIT;'+ CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +
7+'USE ['+ f.name+']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;;' + CHAR(13) + CHAR(10) + 'GO'+ CHAR(13) + CHAR(10)
8+'DBCC SHRINKDATABASE(N'''+ f.name+''');' + CHAR(13) + CHAR(10) + 'GO'
9FROM sys.databases f
10WHERE (f.database_id > 4) -- no system databases
11ORDER BY f.name
Comments