{"id":939,"date":"2017-03-04T01:35:12","date_gmt":"2017-03-03T20:05:12","guid":{"rendered":"http:\/\/pramodsingla.com\/?p=939"},"modified":"2017-03-04T01:35:12","modified_gmt":"2017-03-03T20:05:12","slug":"mssql-server-automatically-restore-dbs-from-backup-files","status":"publish","type":"post","link":"https:\/\/pramodsingla.com\/?p=939","title":{"rendered":"MSSQL Server : Automatically Restore DBs from backup files"},"content":{"rendered":"

Problem Statement:<\/strong>Many a times we need to restore multiple dbs from the backfiles on disk. We must write scripts to restore each DB. Rather use the shared script to do the automatic restore.
\nSolution:\u00a0<\/strong><\/p>\n

Note<\/strong>:Change the DB name,data file path,log file path and backup file path before using this scirpt.<\/p>\n


\nBEGIN<\/span>\u00a0try<\/span>
\nDECLARE<\/span>\u00a0curDBNames<\/span>\u00a0CURSOR<\/span>\u00a0FOR<\/span>
\nSELECT<\/span>\u00a0dbname<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0(<\/span>
\n--Change\u00a0DB1\u00a0and\u00a0Db2\u00a0with\u00a0actual\u00a0db\u00a0names\u00a0and\u00a0more\u00a0dbs\u00a0as\u00a0per\u00a0you\u00a0requirement<\/i><\/span>
\nSELECT<\/span>\u00a0'DB1'<\/span>\u00a0AS<\/span>\u00a0dbname<\/span>
\nUNION<\/span>
\nSELECT<\/span>\u00a0'DB2'<\/span>\u00a0AS<\/span>\u00a0dbname<\/span>)<\/span>\u00a0a<\/span>
\nLEFT<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>databases<\/span>\u00a0d<\/span>
\nON<\/span>\u00a0a<\/span>.<\/span>dbname<\/span>\u00a0=<\/span>\u00a0d<\/span>.<\/span>NAME<\/span>
\nWHERE<\/span>\u00a0\u00a0d<\/span>.<\/span>NAME<\/span>\u00a0IS<\/span>\u00a0NULL<\/span>
\nDECLARE<\/span>\u00a0@ErrorMessage<\/span>\u00a0NVARCHAR<\/i><\/span>(<\/span>4000<\/span>)<\/span>;<\/span>
\nDECLARE<\/span>\u00a0@ErrorSeverity<\/span>\u00a0INT<\/i><\/span>;<\/span>
\nDECLARE<\/span>\u00a0@ErrorState<\/span>\u00a0INT<\/i><\/span>;<\/span>
\nDECLARE<\/span>\u00a0@v_DBName<\/span>\u00a0SYSNAME<\/i><\/span><\/code><\/p>\n

OPEN<\/span>\u00a0curDBNames<\/span><\/p>\n

FETCH<\/span>\u00a0NEXT<\/span>\u00a0FROM<\/span>\u00a0curDBNames<\/span>\u00a0INTO<\/span>\u00a0@v_DBName<\/span><\/p>\n

WHILE<\/span>\u00a0@@Fetch_Status<\/span>\u00a0=<\/span>\u00a00<\/span>
\nBEGIN<\/span>
\nBEGIN<\/span>\u00a0try<\/span>
\nSET<\/span>\u00a0nocount<\/span>\u00a0ON<\/span><\/p>\n

DECLARE<\/span>\u00a0@p_strDBNameFrom<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SYSNAME<\/i><\/span>\u00a0=<\/span>@v_DBName<\/span>,<\/span>
\n@p_strDBNameTo<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SYSNAME<\/i><\/span>\u00a0=<\/span>@v_DBName<\/span>,<\/span><\/p>\n

–Change\u00a0the data file,log file and backup file locations<\/i>
\n@p_strDataFileMoveToFolder<\/span>\u00a0VARCHAR<\/i><\/span>(<\/span>255<\/span>)<\/span>=<\/span>‘H:\\MSSQL\\DATA’<\/span>,<\/span>
\n@p_strLogFileMoveToFolder<\/span>\u00a0\u00a0VARCHAR<\/i><\/span>(<\/span>255<\/span>)<\/span>=<\/span>‘O:\\MSSQL\\log’<\/span>,<\/span>
\n@p_strFQNBackupFileNames<\/span>\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>max<\/span>)<\/span>=<\/span>
\n‘E:\\backupFileLocation\\’<\/span>\u00a0+<\/span>\u00a0@v_DBName<\/span>\u00a0+<\/span>
\n‘.bak’<\/span>,<\/span>
\n@v_strDBFilename<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR<\/i><\/span>(<\/span>100<\/span>)<\/span>,<\/span>
\n@v_strDBLogFilename<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR<\/i><\/span>(<\/span>100<\/span>)<\/span>,<\/span>
\n@v_strDBDataFile<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR<\/i><\/span>(<\/span>100<\/span>)<\/span>,<\/span>
\n@v_strDBLogFile<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR<\/i><\/span>(<\/span>100<\/span>)<\/span>,<\/span>
\n@v_strExecSQL<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>1000<\/span>)<\/span>,<\/span>
\n@v_strKillConSQL<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>1000<\/span>)<\/span>,<\/span>
\n@v_strExecSQL1<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>1000<\/span>)<\/span>,<\/span>
\n@v_strMoveSQL<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>4000<\/span>)<\/span>,<\/span>
\n@v_strREPLACE<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>50<\/span>)<\/span>,<\/span>
\n@v_strTEMP<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>1000<\/span>)<\/span>,<\/span>
\n@v_strListSQL<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>4000<\/span>)<\/span>,<\/span>
\n@v_strServerVersion<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>20<\/span>)<\/span>,<\/span>
\n@v_strFromDiskSQL<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR<\/i><\/span>(<\/span>max<\/span>)<\/span><\/p>\n

SET<\/span>\u00a0@v_strREPLACE<\/span>\u00a0=<\/span>\u00a0”<\/span><\/p>\n

IF<\/span>\u00a0EXISTS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a0NAME<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>databases<\/span>
\nWHERE<\/span>\u00a0\u00a0NAME<\/span>\u00a0=<\/span>\u00a0@p_strDBNameTo<\/span>)<\/span>
\nSET<\/span>\u00a0@v_strREPLACE<\/span>\u00a0=<\/span>\u00a0‘,\u00a0REPLACE’<\/span><\/p>\n

IF<\/span>\u00a0Object_id<\/i><\/span>(<\/span>‘tempdb..#Backupfiles’<\/span>)<\/span>\u00a0IS<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>
\nDROP<\/span>\u00a0TABLE<\/span>\u00a0#Backupfiles<\/span><\/p>\n

SELECT<\/span>\u00a0*<\/span>
\nINTO<\/span>\u00a0\u00a0\u00a0#Backupfiles<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0dbo<\/span>.<\/span>splitstring<\/b><\/span>(<\/span>@p_strFQNBackupFileNames<\/span>)<\/span><\/p>\n

IF<\/span>\u00a0Object_id<\/i><\/span>\u00a0(<\/span>‘tempdb..#FILE_LIST’<\/span>)<\/span>\u00a0IS<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>
\nBEGIN<\/span>
\nDROP<\/span>\u00a0TABLE<\/span>\u00a0#FILE_LIST<\/span>
\nEND<\/span><\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0#FILE_LIST<\/span>
\n(<\/span>
\nLogicalName<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR<\/i><\/span>(<\/span>64<\/span>)<\/span>,<\/span>
\nPhysicalName<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR<\/i><\/span>(<\/span>130<\/span>)<\/span>,<\/span>
\n[Type]<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR<\/i><\/span>(<\/span>1<\/span>)<\/span>,<\/span>
\nFileGroupName<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR<\/i><\/span>(<\/span>64<\/span>)<\/span>,<\/span>
\nSize<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECIMAL<\/i><\/span>(<\/span>20<\/span>,<\/span>\u00a00<\/span>)<\/span>,<\/span>
\nMaxSize<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECIMAL<\/i><\/span>(<\/span>25<\/span>,<\/span>\u00a00<\/span>)<\/span>,<\/span>
\nFileID<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BIGINT<\/i><\/span>,<\/span>
\nCreateLSN<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECIMAL<\/i><\/span>(<\/span>25<\/span>,<\/span>\u00a00<\/span>)<\/span>,<\/span>
\nDropLSN<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECIMAL<\/i><\/span>(<\/span>25<\/span>,<\/span>\u00a00<\/span>)<\/span>,<\/span>
\nUniqueID<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNIQUEIDENTIFIER<\/i><\/span>,<\/span>
\nReadOnlyLSN<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECIMAL<\/i><\/span>(<\/span>25<\/span>,<\/span>\u00a00<\/span>)<\/span>,<\/span>
\nReadWriteLSN<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECIMAL<\/i><\/span>(<\/span>25<\/span>,<\/span>\u00a00<\/span>)<\/span>,<\/span>
\nBackupSizeInBytes<\/span>\u00a0\u00a0\u00a0\u00a0DECIMAL<\/i><\/span>(<\/span>25<\/span>,<\/span>\u00a00<\/span>)<\/span>,<\/span>
\nSourceBlockSize<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INT<\/i><\/span>,<\/span>
\nfilegroupid<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INT<\/i><\/span>,<\/span>
\nloggroupguid<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNIQUEIDENTIFIER<\/i><\/span>,<\/span>
\ndifferentialbaseLSN<\/span>\u00a0\u00a0DECIMAL<\/i><\/span>(<\/span>25<\/span>,<\/span>\u00a00<\/span>)<\/span>,<\/span>
\ndifferentialbaseGUID<\/span>\u00a0UNIQUEIDENTIFIER<\/i><\/span>,<\/span>
\nisreadonly<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BIT<\/i><\/span>,<\/span>
\nispresent<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BIT<\/i><\/span>,<\/span>
\nTDEThumbpr<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DECIMAL<\/i><\/span>
\n)<\/span><\/p>\n

———————–create\u00a0from\u00a0disk\u00a0query\u00a0————————-<\/i><\/span>
\nDECLARE<\/span>\u00a0curFileLIst<\/span>\u00a0CURSOR<\/span>\u00a0FOR<\/span>
\nSELECT<\/span>\u00a0NAME<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0#Backupfiles<\/span>\u00a0–where\u00a0[Type]=’D’<\/i><\/span>
\nSET<\/span>\u00a0@v_strFromDiskSQL<\/span>\u00a0=<\/span>\u00a0”<\/span><\/p>\n

OPEN<\/span>\u00a0curFileList<\/span><\/p>\n

FETCH<\/span>\u00a0NEXT<\/span>\u00a0FROM<\/span>\u00a0curFileList<\/span>\u00a0INTO<\/span>\u00a0@v_strTEMP<\/span><\/p>\n

WHILE<\/span>\u00a0@@Fetch_Status<\/span>\u00a0=<\/span>\u00a00<\/span>
\nBEGIN<\/span>
\nSET<\/span>\u00a0@v_strFromDiskSQL<\/span>\u00a0=<\/span>\u00a0@v_strFromDiskSQL<\/span>\u00a0+<\/span>\u00a0‘,Disk=”’<\/span>\u00a0+<\/span>
\n@v_strTEMP<\/span>
\n+<\/span>\u00a0””<\/span><\/p>\n

FETCH<\/span>\u00a0NEXT<\/span>\u00a0FROM<\/span>\u00a0curFileList<\/span>\u00a0INTO<\/span>\u00a0@v_strTEMP<\/span><\/p>\n

PRINT<\/span>\u00a0@v_strFromDiskSQL<\/span>
\nEND<\/span><\/p>\n

CLOSE<\/span>\u00a0curFileList<\/span><\/p>\n

DEALLOCATE<\/span>\u00a0curFileList<\/span><\/p>\n

SET<\/span>\u00a0@v_strFromDiskSQL<\/span>=<\/span>\u00a0Stuff<\/i><\/span>(<\/span>@v_strFromDiskSQL<\/span>,<\/span>\u00a01<\/span>,<\/span>\u00a01<\/span>,<\/span>\u00a0”<\/span>)<\/span><\/p>\n

—————–create\u00a0\u00a0with\u00a0move\u00a0query——————————<\/i><\/span>
\nINSERT<\/span>\u00a0INTO<\/span>\u00a0#FILE_LIST<\/span>
\nEXEC<\/span>\u00a0(<\/span>‘RESTORE\u00a0FILELISTONLY\u00a0FROM\u00a0\u00a0‘<\/span>\u00a0+<\/span>\u00a0@v_strFromDiskSQL<\/span>\u00a0+<\/span>\u00a0”<\/span>)<\/span><\/p>\n

DECLARE<\/span>\u00a0curFileLIst<\/span>\u00a0CURSOR<\/span>\u00a0FOR<\/span>
\nSELECT<\/span>\u00a0‘MOVE\u00a0N”’<\/span>\u00a0+<\/span>\u00a0LogicalName<\/span>\u00a0+<\/span>\u00a0”’\u00a0TO\u00a0N”’<\/span>
\n+<\/span>\u00a0iif<\/b><\/span>(<\/span>type<\/span>=<\/span>‘L’<\/span>,<\/span>\u00a0@p_strLogFileMoveToFolder<\/span>,<\/span>
\n@p_strDataFileMoveToFolder<\/span>)<\/span>
\n+<\/span>\u00a0Replace<\/i><\/span>(<\/span>RIGHT<\/i><\/span>(<\/span>PhysicalName<\/span>,<\/span>\u00a0Patindex<\/i><\/span>(<\/span>‘%\\%’<\/span>,<\/span>\u00a0Reverse<\/i><\/span>(<\/span>
\nPhysicalName<\/span>)<\/span>
\n)<\/span>)<\/span>,<\/span>
\n@p_strDBNameFrom<\/span>,<\/span>\u00a0@p_strDBNameTo<\/span>)<\/span>
\n+<\/span>\u00a0””<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0#FILE_LIST<\/span>\u00a0–where\u00a0[Type]=’D’<\/i><\/span>
\nSET<\/span>\u00a0@v_strMoveSQL<\/span>\u00a0=<\/span>\u00a0”<\/span><\/p>\n

OPEN<\/span>\u00a0curFileList<\/span><\/p>\n

FETCH<\/span>\u00a0NEXT<\/span>\u00a0FROM<\/span>\u00a0curFileList<\/span>\u00a0INTO<\/span>\u00a0@v_strTEMP<\/span><\/p>\n

WHILE<\/span>\u00a0@@Fetch_Status<\/span>\u00a0=<\/span>\u00a00<\/span>
\nBEGIN<\/span>
\nSET<\/span>\u00a0@v_strMoveSQL<\/span>\u00a0=<\/span>\u00a0@v_strMoveSQL<\/span>\u00a0+<\/span>\u00a0@v_strTEMP<\/span>\u00a0+<\/span>\u00a0‘,\u00a0‘<\/span><\/p>\n

FETCH<\/span>\u00a0NEXT<\/span>\u00a0FROM<\/span>\u00a0curFileList<\/span>\u00a0INTO<\/span>\u00a0@v_strTEMP<\/span>
\nEND<\/span><\/p>\n

CLOSE<\/span>\u00a0curFileList<\/span><\/p>\n

DEALLOCATE<\/span>\u00a0curFileList<\/span><\/p>\n

PRINT<\/span>
\n‘———————————————————————————————————————————–‘<\/span><\/p>\n

PRINT<\/span>\u00a0‘Restoration\u00a0Started\u00a0for”‘<\/span>\u00a0+<\/span>\u00a0@p_strDBNameTo<\/span>
\n+<\/span>\u00a0‘”\u00a0database\u00a0from\u00a0“‘<\/span>
\n+<\/span>\u00a0@p_strFQNBackupFileNames<\/span>\u00a0+<\/span>\u00a0‘\u00a0at\u00a0‘<\/span>
\n+<\/span>\u00a0Cast<\/i><\/span>(<\/span>Getdate<\/i><\/span>(<\/span>)<\/span>\u00a0AS<\/span>\u00a0VARCHAR<\/i><\/span>(<\/span>30<\/span>)<\/span>)<\/span><\/p>\n

PRINT<\/span>
\n‘———————————————————————————————————————————–‘<\/span><\/p>\n

SET<\/span>\u00a0@v_strExecSQL<\/span>\u00a0=<\/span>\u00a0‘RESTORE\u00a0DATABASE\u00a0[‘<\/span>\u00a0+<\/span>\u00a0@p_strDBNameTo<\/span>\u00a0+<\/span>\u00a0‘]’<\/span>
\nSET<\/span>\u00a0@v_strExecSQL<\/span>\u00a0=<\/span>\u00a0@v_strExecSQL<\/span>\u00a0+<\/span>\u00a0‘\u00a0FROM\u00a0\u00a0‘<\/span>\u00a0+<\/span>\u00a0@v_strFromDiskSQL<\/span>
\n+<\/span>\u00a0”<\/span>
\nSET<\/span>\u00a0@v_strExecSQL<\/span>\u00a0=<\/span>\u00a0@v_strExecSQL<\/span>\u00a0+<\/span>\u00a0‘\u00a0WITH\u00a0FILE\u00a0=\u00a01,’<\/span>
\nSET<\/span>\u00a0@v_strExecSQL<\/span>\u00a0=<\/span>\u00a0@v_strExecSQL<\/span>\u00a0+<\/span>\u00a0@v_strMoveSQL<\/span>
\nSET<\/span>\u00a0@v_strExecSQL<\/span>\u00a0=<\/span>\u00a0@v_strExecSQL<\/span>\u00a0+<\/span>\u00a0‘\u00a0NOREWIND,\u00a0STATS=10,’<\/span>
\nSET<\/span>\u00a0@v_strExecSQL<\/span>\u00a0=<\/span>\u00a0@v_strExecSQL<\/span>\u00a0+<\/span>\u00a0‘\u00a0NOUNLOAD\u00a0‘<\/span>
\nSET<\/span>\u00a0@v_strExecSQL<\/span>\u00a0=<\/span>\u00a0@v_strExecSQL<\/span>\u00a0+<\/span>\u00a0@v_strREPLACE<\/span><\/p>\n

PRINT<\/span>\u00a0@v_strExecSQL<\/span><\/p>\n

PRINT<\/span>\u00a0‘Killing\u00a0active\u00a0connections\u00a0to\u00a0the\u00a0“‘<\/span>
\n+<\/span>\u00a0@p_strDBNameTo<\/span>\u00a0+<\/span>\u00a0‘”\u00a0database’<\/span><\/p>\n

—\u00a0Create\u00a0the\u00a0sql\u00a0to\u00a0kill\u00a0the\u00a0active\u00a0database\u00a0connections<\/i><\/span>
\nSET<\/span>\u00a0@v_strKillConSQL<\/span>\u00a0=<\/span>\u00a0”<\/span><\/p>\n

SELECT<\/span>\u00a0@v_strKillConSQL<\/span>\u00a0=<\/span>\u00a0@v_strKillConSQL<\/span>\u00a0+<\/span>\u00a0‘kill\u00a0‘<\/span>
\n+<\/span>\u00a0CONVERT<\/i><\/span>(<\/span>CHAR<\/i><\/span>(<\/span>10<\/span>)<\/span>,<\/span>\u00a0spid<\/span>)<\/span>\u00a0+<\/span>\u00a0‘\u00a0‘<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0master<\/span>.<\/span>dbo<\/span>.<\/span>sysprocesses<\/span>
\nWHERE<\/span>\u00a0\u00a0Db_name<\/i><\/span>(<\/span>dbid<\/span>)<\/span>\u00a0=<\/span>\u00a0@p_strDBNameTo<\/span>
\nAND<\/span>\u00a0DBID<\/span>\u00a0<><\/span>\u00a00<\/span>
\nAND<\/span>\u00a0spid<\/span>\u00a0<><\/span>\u00a0@@spid<\/span><\/p>\n

EXEC<\/span>\u00a0(<\/span>@v_strKillConSQL<\/span>)<\/span><\/p>\n

EXEC<\/span>\u00a0sp_executesql<\/b><\/span>
\n@v_strExecSQL<\/span><\/p>\n

PRINT<\/span>
\n‘———————————————————————————————————————————‘<\/span><\/p>\n

PRINT<\/span>\u00a0‘Restoration\u00a0Completed\u00a0FOR\u00a0“‘<\/span>
\n+<\/span>\u00a0@p_strDBNameTo<\/span>\u00a0+<\/span>\u00a0‘”\u00a0database\u00a0from\u00a0“‘<\/span>
\n+<\/span>\u00a0@p_strFQNBackupFileNames<\/span>\u00a0+<\/span>\u00a0‘\u00a0at\u00a0‘<\/span>
\n+<\/span>\u00a0Cast<\/i><\/span>(<\/span>Getdate<\/i><\/span>(<\/span>)<\/span>\u00a0AS<\/span>\u00a0VARCHAR<\/i><\/span>(<\/span>30<\/span>)<\/span>)<\/span><\/p>\n

PRINT<\/span>
\n‘———————————————————————————————————————————‘<\/span>
\nEND<\/span>\u00a0try<\/span><\/p>\n

BEGIN<\/span>\u00a0catch<\/span>
\nSELECT<\/span>\u00a01<\/span><\/p>\n

SELECT<\/span>\u00a0@ErrorMessage<\/span>\u00a0=<\/span>\u00a0ERROR_MESSAGE<\/b><\/span>(<\/span>)<\/span>,<\/span>
\n@ErrorSeverity<\/span>\u00a0=<\/span>\u00a0ERROR_SEVERITY<\/b><\/span>(<\/span>)<\/span>,<\/span>
\n@ErrorState<\/span>\u00a0=<\/span>\u00a0ERROR_STATE<\/b><\/span>(<\/span>)<\/span>;<\/span><\/p>\n

RAISERROR<\/span>\u00a0(<\/span>@ErrorMessage<\/span>,<\/span>—\u00a0Message\u00a0text.\u00a0\u00a0<\/i><\/span>
\n1<\/span>,<\/span>@ErrorState<\/span>\u00a0—\u00a0State.\u00a0\u00a0<\/i><\/span>
\n)<\/span>;<\/span><\/p>\n

SELECT<\/span>\u00a02<\/span>
\nEND<\/span>\u00a0catch<\/span><\/p>\n

SELECT<\/span>\u00a0@v_DBName<\/span><\/p>\n

FETCH<\/span>\u00a0NEXT<\/span>\u00a0FROM<\/span>\u00a0curDBNames<\/span>\u00a0INTO<\/span>\u00a0@v_DBName<\/span>
\nEND<\/span>
\nEND<\/span>\u00a0try<\/span><\/p>\n

BEGIN<\/span>\u00a0catch<\/span>
\nSELECT<\/span>\u00a03<\/span><\/p>\n

SELECT<\/span>\u00a0@ErrorMessage<\/span>\u00a0=<\/span>\u00a0ERROR_MESSAGE<\/b><\/span>(<\/span>)<\/span>,<\/span>
\n@ErrorSeverity<\/span>\u00a0=<\/span>\u00a0ERROR_SEVERITY<\/b><\/span>(<\/span>)<\/span>,<\/span>
\n@ErrorState<\/span>\u00a0=<\/span>\u00a0ERROR_STATE<\/b><\/span>(<\/span>)<\/span>;<\/span><\/p>\n

—\u00a0Use\u00a0RAISERROR\u00a0inside\u00a0the\u00a0CATCH\u00a0block\u00a0to\u00a0return\u00a0error\u00a0\u00a0<\/i><\/span>
\n—\u00a0information\u00a0about\u00a0the\u00a0original\u00a0error\u00a0that\u00a0caused\u00a0\u00a0<\/i><\/span>
\n—\u00a0execution\u00a0to\u00a0jump\u00a0to\u00a0the\u00a0CATCH\u00a0block.\u00a0\u00a0<\/i><\/span>
\nRAISERROR<\/span>\u00a0(<\/span>@ErrorMessage<\/span>,<\/span>—\u00a0Message\u00a0text.\u00a0\u00a0<\/i><\/span>
\n1<\/span>,<\/span>—\u00a0Severity.\u00a0\u00a0<\/i><\/span>
\n@ErrorState<\/span>\u00a0—\u00a0State.\u00a0\u00a0<\/i><\/span>
\n)<\/span>;<\/span><\/p>\n

SELECT<\/span>\u00a04<\/span>
\nEND<\/span>\u00a0catch<\/span><\/p>\n

CLOSE<\/span>\u00a0curDBNames<\/span><\/p>\n

DEALLOCATE<\/span>\u00a0curDBNames<\/span><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Problem Statement:Many a times we need to restore multiple dbs from the backfiles on disk. We must write scripts to[…]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[68,142,165,180],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/939"}],"collection":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=939"}],"version-history":[{"count":0,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/939\/revisions"}],"wp:attachment":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=939"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=939"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=939"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}