Credits:
Description
- The DEV server was located in the building
- The PROD server was located 300 miles away in a managed hosting facility.
- The only direct connection was 100Mb WAN
- The PROD DB was 150+ GB
- SQL Server 2005.
- The company was a financial institution and (rightly) concerned about securre data transfer, especially as the DW, but its very natrue, contained the full customer base and their financial records.
- The company had a very strict set of processes and change control
- The WAN was critical to internal business processes during the working day.
- No direct connection to PROD SQL Server on port 1433 (or any other port) other than via a 2-hop Remote Desktop connection or through business applications.
Compression:
Security:
Network bandwidth
Doh!
Code
Secure Backup with striping and compression:
-- PARAMETERS -- <DATABASENAME> - name of the single database to be backed up -- <FILELOCATION> - name of the single database to be backed up -- <PASSWORD> - replace this with the pasword to be used – must-- comply with MDM security guidelines delivered as part of CR-112 -- <AUTO> - can be left untouched for an auto-generated file name or -- replaced with a fixed file name
-- SERVER DETAILS -- To be run in a SQL query window connected to the required -- PRODUCTION SQL Server in Markham
DECLARE @exitcode int DECLARE @sqlerrorcode int EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [<DATABASENAME>] TO DISK = ''' + <FILELOCATION> + '\<AUTO>.sqb'' WITH COMPRESSION = 3, FILECOUNT = 32, COPY_ONLY, KEYSIZE = 256, PASSWORD = ''<PASSWORD>''"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0) BEGIN RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) END
--NOTE: Replace all <parameters> with operational data -- **** Can only be run on servers with Red-Gate SQL Backup PRO -- 6.2 or greater installed *** -- **** This backup will not interfere with or change the -- existing backup/log sequence *** |
Restore from a striped, compressed and encrypted backup (Red-Gate SQL Backup 6.2):
-- PARAMETERS -- <DATABASENAME> - name of the single database to be restored -- <DB_DATA_FILE_LOGICAL_NAME> - name of the single database to be -- restored -- <DB_LOG_FILE_LOGICAL_NAME> - name of the single database to be -- restored -- <RESTORE_FILE_BASE_NAME> - the assigned base file name for the -- 32-file backup set -- <RESTORE_LOCATION> - The location where the backup files are -- located -- <PASSWORD> - replace this with the pasword to be used – must -- comply with MDM security guidelines delivered as part -- of CR-112
-- SERVER DETAILS -- To be run in a SQL query window connected to the required -- DEVELOPMENT SQL Server
DECLARE @exitcode int DECLARE @sqlerrorcode int EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [<DATABASENAME>] FROM DISK = ''<RESTORE_LOCATION>\<RESTORE_FILE_BASE_NAME>_01.sqb'', DISK = ''<RESTORE_LOCATION>\<RESTORE_FILE_BASE_NAME>_02.sqb'', DISK = ''<RESTORE_LOCATION>\<RESTORE_FILE_BASE_NAME>_03.sqb'', ... ... DISK = ''<RESTORE_LOCATION>\<RESTORE_FILE_BASE_NAME>_32.sqb''
WITH PASSWORD = ''<PASSWORD>'', MOVE ''<DB_DATA_FILE_LOGICAL_NAME>'' TO ''<RESTORE_LOCATION>\<DATABASENAME>.mdf'', ... (repeat as necessary for multiple data files)
MOVE ''<DB_LOG_FILE_LOGICAL_NAME>'' TO ''<RESTORE_LOCATION>\<DATABASENAME>_log.ldf'', ... (repeat as necessary for multiple log files)
RECOVERY, ORPHAN_CHECK"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0) BEGIN RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) END
--NOTE: Replace all <parameters> with operational data -- **** Can only be run on servers with Red-Gate SQL Backup PRO -- 6.2 or greater installed *** -- **** This restored will not interfere with or change the - existing backup/log sequence ***
|