Backup/Restore scripting

I comandi T-SQL per backuppare i database sono:

DBCC SHRINKDATABASE (WMDBProd, TRUNCATEONLY);
BACKUP DATABASE WMDBProd 
TO DISK = 'D:\WMDBProd.bak'
WITH FORMAT;
GO

DBCC SHRINKDATABASE (TrovaProdottiWM4, TRUNCATEONLY);
BACKUP DATABASE TrovaProdottiWM4
TO DISK = 'D:\FTP\TrovaProdottiWM4.bak'
WITH FORMAT;
GO

DBCC SHRINKDATABASE (WMDBSearch, TRUNCATEONLY);
BACKUP DATABASE WMDBSearch
TO DISK = 'D:\FTP\WMDBSearch.bak'
WITH FORMAT;
GO

DBCC SHRINKDATABASE (WMStats, TRUNCATEONLY);
BACKUP DATABASE WMStats
TO DISK = 'D:\FTP\WMStats.bak'
WITH FORMAT;
GO

DBCC SHRINKDATABASE (WMDBBO, TRUNCATEONLY);
BACKUP DATABASE WMDBBO
TO DISK = 'D:\FTP\WMDBBO.bak'
WITH FORMAT;
GO

DBCC SHRINKDATABASE (Anagrafiche, TRUNCATEONLY);
BACKUP DATABASE Anagrafiche
TO DISK = 'D:\FTP\Anagrafiche.bak'
WITH FORMAT;
GO

Lo script per restorare il database di Produzione su quello di Test è il seguente

-- ***************** DA ESEGUIRE SU MACCHINA DI TEST ******************
USE _Test_WMDBProd -- se sottolineato di rosso, non siamo sul server corretto


USE Master
-- backup del database di test
DECLARE @DynamicSQLWMDBTestBkp NVARCHAR(4000)
SET @DynamicSQLWMDBTestBkp = 'BACKUP DATABASE _Test_WMDBProd TO DISK = ''D:\SQLServer\Backup\DB_RestoreSuTest_WMDBTest_' + CONVERT(VARCHAR(10),CONVERT(date, SYSDATETIME())) +  '.bak'' WITH FORMAT;'
EXECUTE dbo.sp_executesql @DynamicSQLWMDBTestBkp

-- backup del database di produzione
DECLARE @DynamicSQLWM NVARCHAR(4000)
SET @DynamicSQLWM = 'BACKUP DATABASE WMDBProd TO DISK = ''D:\SQLServer\Backup\DB_RestoreSuTest_WMDBProd_' + CONVERT(VARCHAR(10),CONVERT(date, SYSDATETIME())) +  '.bak'' WITH FORMAT;'
EXECUTE dbo.sp_executesql @DynamicSQLWM

---- backup del database del trovaprodotti
--DECLARE @DynamicSQLTP NVARCHAR(4000)
--SET @DynamicSQLTP = 'BACKUP DATABASE TrovaProdottiWM4 TO DISK = ''\\10.143.1.3\ftp\BackupDB\RestoreSuTest\DB_RestoreSuTest_TrovaProdottiWM4_' + CONVERT(VARCHAR(10),CONVERT(date, SYSDATETIME())) +  '.bak'' WITH FORMAT;'
--EXECUTE WM01SQLWMDB.master.dbo.sp_executesql @DynamicSQLTP
--GO

-- aggiornamento del database di test con il database di produzione
ALTER DATABASE _Test_WMDBProd SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
DECLARE @DynamicSQLWMRestore NVARCHAR(4000)
SET @DynamicSQLWMRestore = 'RESTORE DATABASE _Test_WMDBProd FROM DISK = ''D:\SQLServer\Backup\DB_RestoreSuTest_WMDBProd_' + CONVERT(VARCHAR(10),CONVERT(date, SYSDATETIME())) + '.bak'' WITH REPLACE'
EXECUTE sp_executesql @DynamicSQLWMRestore
ALTER DATABASE _Test_WMDBProd SET MULTI_USER


USE _Test_WMDBProd
EXEC sp_change_users_login 'Auto_Fix', 'wmuser', NULL, 'GPC.italy2010'


UPDATE Zone SET Host = 'dbdemo47.com' WHERE ID = 1
UPDATE Zone SET Host = 'dbdemo47.com' WHERE ID = 7
UPDATE Zone SET Host = 'dbdemo47.com' WHERE ID = 10
UPDATE Zone SET Host = 'dbdemo47.com' WHERE ID = 1000


UPDATE Zone SET GoogleMapsKey = 'ABQIAAAA5ifW8Q3nVxoBDt6tG_JlKxRuplZ2biShEqU6B0RYtVrROadeBxTGDdiA5R1608VNM0OpY_rneD6WAg' WHERE ID = 1
UPDATE Zone SET GoogleMapsKey = 'ABQIAAAA5ifW8Q3nVxoBDt6tG_JlKxTwI5OEweggBIBS7GqK23hJkqiaQBQxab81v0wBphEqmQ_HMJdrFKlXBA' WHERE ID = 2
UPDATE Zone SET GoogleMapsKey = 'ABQIAAAA5ifW8Q3nVxoBDt6tG_JlKxQ-PTJzU_a7KzIPzHqCQi3OvysEMxTnSIWwFmglGyO561VSkJJxsCGAVQ' WHERE ID = 3
UPDATE Zone SET GoogleMapsKey = 'ABQIAAAA5ifW8Q3nVxoBDt6tG_JlKxSGaFmm-Vzkcf7vfjtJo6rMadTLBRR2AS1hnlYw_ry8oVvUsxrV0y86Hg' WHERE ID = 7
UPDATE Zone SET GoogleMapsKey = 'ABQIAAAA5ifW8Q3nVxoBDt6tG_JlKxSGaFmm-Vzkcf7vfjtJo6rMadTLBRR2AS1hnlYw_ry8oVvUsxrV0y86Hg' WHERE ID = 10
UPDATE Zone SET GoogleMapsKey = 'ABQIAAAA5ifW8Q3nVxoBDt6tG_JlKxSGaFmm-Vzkcf7vfjtJo6rMadTLBRR2AS1hnlYw_ry8oVvUsxrV0y86Hg' WHERE ID = 1000

UPDATE Shop SET Visible = 1 WHERE ID = 44334	-- In ambiente di test è lo shop che usano gli agenti per mostrare le statistiche