Steps to Restore .bak SQL Server backup file to Amazon SQL Server RDS
Step by Step Guide to backup and restore SQL Server backups on AWS RDS through S3 Buckets.
Steps to Restore .bak SQL Server backup file to Amazon SQL Server RDS
Step by Step Guide to backup and restore SQL Server backups on AWS RDS through S3 Buckets.
Dynamic SQL Search Stored Procedure
This also includes how to safeguard against SQL Injection by escaping single quotes through SQL function.
SQL Server Cached Pages and ways to bring data pages to memory
We noticed considerable latency in stored procedure execution after restarting SQL Server Instance which was triggered by accessing data from disks instead of memory. So we ended up writing a simple script to bring data pages to memory. This helped us to avoid first touch penalty after restart in production environment.
Dynamically Restore SQL Server Database from Backup We always get requests to restore databases from production to lab environment.This script dynamically restores the backup file and moves the data and log files to default locations.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 |
/****************************************************************************************************** ** Project: Operations ** Issue: Restore Database from Backup file ** ShortDesc: Restore Database From Backup File and Dynamically move data and log files to Default Location.This is intended for single data and log file Databases. ** Auth: EMETRIC\rvenkataraman ** Date: 2016-07-25 Created ** Description: Restore Database From Backup File and Dynamically move data and log files to Default Location.This is intended for single data and log file Databases. ************************** ** Change History ************************** ** CR Date Author Description ** ----- ----------– ----------- ------------------------------------------------------------ ** 1 2016-07-25 Raju Venkataraman Restore Database From Backup File and Dynamically move data and log files to Default Location.This is intended for single data and log file Databases. ********************************************************************************************************/ SET NOCOUNT ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF SET XACT_ABORT ON -----------BEGIN SQL SCRIPT HEADER------------------- DECLARE @DB_NAME VARCHAR(128) SET @DB_NAME = ( SELECT DB_NAME(dbid) FROM master..sysprocesses WHERE spid = @@SPID ) PRINT '-----------------------------------------------------------------------' PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------' PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME PRINT '-----SCRIPT START TIME: ' + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121) PRINT '-----MachineName : ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR) PRINT '-----SQL Instance : ' + CAST(@@SERVERNAME AS VARCHAR) PRINT '-----DB User : ' + CURRENT_USER PRINT '-----System User : ' + SYSTEM_USER PRINT '-----Host : ' + HOST_NAME() PRINT '-----Application : ' + APP_NAME() PRINT '-----TranCount : ' + CAST (@@trancount AS VARCHAR) PRINT '-----------------------------------------------------------------------' ----------------END SQL SCRIPT HEADER--------------------- -- Script Specific Variables Declarations DECLARE @StartTime DATETIME2 , @EndTime DATETIME2 SELECT @StartTime = GETDATE() -- Script Block Start BEGIN TRY DECLARE @pBackupFile NVARCHAR(550); DECLARE @pDatabaseName sysname; SELECT @pDatabaseName = 'AdventureWorks'; SELECT @pBackupFile = '\\FileShare\AdventureWorks.BAK'; DECLARE @pDSQL NVARCHAR(MAX); DECLARE @pDataFileLocation NVARCHAR(550); DECLARE @pLogFileLocation NVARCHAR(550); DECLARE @pLogicalDataFileName NVARCHAR(550); DECLARE @pLogicalLogFileName NVARCHAR(550); DECLARE @pDataFileName NVARCHAR(550); DECLARE @pLogFileName NVARCHAR(550); SELECT @pDataFileName = @pDatabaseName + '.mdf' , @pLogFileName = @pDatabaseName + '.ldf'; SELECT @pDataFileLocation = CONVERT(VARCHAR(550), SERVERPROPERTY('instancedefaultdatapath')) , @pLogFileLocation = CONVERT(VARCHAR(550), SERVERPROPERTY('instancedefaultlogpath')); IF SERVERPROPERTY('productversion') >= '13.0.1400.361' -- SQL Server 2016 and Above Has SnapshotUrl BEGIN DECLARE @FileLists TABLE ( [LogicalName] NVARCHAR(128) , [PhysicalName] NVARCHAR(260) , [Type] NCHAR(1) , [FileGroupName] NVARCHAR(128) , [Size] BIGINT , [MaxSize] BIGINT , [FileId] BIGINT , [CreateLSN] DECIMAL(25, 0) , [DropLSN] DECIMAL(25, 0) , [UniqueId] UNIQUEIDENTIFIER , [ReadOnlyLSN] DECIMAL(25, 0) , [ReadWriteLSN] DECIMAL(25, 0) , [BackupSizeInBytes] BIGINT , [SourceBlockSize] INT , [FileGroupId] INT , [LogGroupGUID] UNIQUEIDENTIFIER , [DifferentialBaseLSN] DECIMAL(25, 0) , [DifferentialBaseGUID] UNIQUEIDENTIFIER , [IsReadOnly] BIT , [IsPresent] BIT , [TDEThumbprint] VARBINARY(20) , [SnapshotUrl] NVARCHAR(336) ); END ELSE BEGIN RAISERROR ('SnapshotUrl is Only Avialble for SQL Server 2016 and Above. Modify the Definition of Table Variable and Remove SnapShotURL.', -- Message text. 16, -- Severity. 1 -- State. ); END SELECT @pDSQL = ' RESTORE FILELISTONLY FROM DISK = ''' + @pBackupFile + ''''; SELECT @pDSQL; INSERT @FileLists EXEC(@pDSQL); SELECT @pLogicalDataFileName = LogicalName FROM @FileLists WHERE Type = 'D'; SELECT @pLogicalLogFileName = LogicalName FROM @FileLists WHERE Type = 'L'; SELECT @pDataFileLocation , @pLogFileLocation , @pDatabaseName; SELECT @pDSQL = ' RESTORE DATABASE ' + @pDatabaseName + ' FROM DISK = ''' + @pBackupFile + ''' WITH MOVE ''' + @pLogicalDataFileName + ''' TO ''' + @pDataFileLocation + @pDataFileName + ''', MOVE ''' + @pLogicalLogFileName + ''' TO ''' + @pLogFileLocation + @pLogFileName + ''''; SELECT @pDSQL; EXEC(@pDSQL); END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage END CATCH -- Script Block End SELECT @EndTime = GETDATE() -----------BEGIN SQL SCRIPT FOOTER-------------------------------------- PRINT '----------------------------------------------------------------' PRINT '---FINISHED SQL SCRIPT--' PRINT '---COMPLETED TIME:' + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121) PRINT '---TranCount : ' + CAST (@@trancount AS VARCHAR) |
Learning from Launching AWS SQL Server RDS
We are evaluating options to migrate our read/write heavy production SQL Server database to amazon SQL Server RDS. We have pretty high throughput needs for few hours a day for few months in a year ,which is mission critical for our business success. We are evaluating pros and cons of moving to amazon RDS with provisioned IOPS. Current hosted provider doesn’t offer on-demand scaling solution .
We have numerous applications using SQL Login accounts . Simple powershell scripts saves time for rotating passwords for SQL Logins.