Activity overview
Latest activity by agarg
Thanks for your reply. I have posted the idea at the link. / comments
Thanks for your reply. I have posted the idea at the link.
Suppress identical error
While using command line to generate a difference report (in html format), if the two databases are identical, the console output says:
Error: The selected objects are identical or no objects have ...
Thanks Peter. I am going to change my process to restore with norecovery by default, and then recover to standby mode only in the end. That should bring down the total restore time. Also, I looked at the VLFs and resized the log file. Will watch over next few days to see the results.
Will update later. Thanks for all your help.
Ajay / comments
Thanks Peter. I am going to change my process to restore with norecovery by default, and then recover to standby mode only in the end. That should bring down the total restore time. Also, I looked ...
Thanks Peter for sticking with me. So, I set the trace flags on and reran the restore. Here is the typical log file. Also see the attached image from sql server error logs for the activity while this particular trn log was being restored.
SQL Backup log file 7.3.0.57
-SQL "RESTORE LOG ODHR FROM DISK = 'Z:\WrkFolder\ODHR_Log201305131409.trn' WITH DISCONNECT_EXISTING, NORECOVERY "
PROCESSES COMPLETED SUCCESSFULLY
5/15/2013 11:06:01 AM: Restoring ODHR (transaction logs) from:
5/15/2013 11:06:01 AM: Z:\WrkFolder\ODHR_Log201305131409.trn
5/15/2013 11:06:01 AM: RESTORE LOG [ODHR] FROM
VIRTUAL_DEVICE = 'SQLBACKUP_40E17D6B-2515-4AA7-89DC-E17B6C50AC90',
VIRTUAL_DEVICE = 'SQLBACKUP_40E17D6B-2515-4AA7-89DC-E17B6C50AC9001',
VIRTUAL_DEVICE = 'SQLBACKUP_40E17D6B-2515-4AA7-89DC-E17B6C50AC9002',
VIRTUAL_DEVICE = 'SQLBACKUP_40E17D6B-2515-4AA7-89DC-E17B6C50AC9003'
WITH BUFFERCOUNT = 24, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY
5/15/2013 11:06:18 AM: Processed 0 pages for database 'ODHR', file 'ownersSQL_dat' on file 1.
5/15/2013 11:06:18 AM: Processed 9400 pages for database 'ODHR', file 'ownersSQL_log' on file 1.
5/15/2013 11:06:18 AM: RESTORE LOG successfully processed 9400 pages in 0.204 seconds (359.966 MB/sec).
5/15/2013 11:06:18 AM: SQL Backup process ended.
[img]C:\temp\FastStone\ODHRRestore.jpg[/img] / comments
Thanks Peter for sticking with me. So, I set the trace flags on and reran the restore. Here is the typical log file. Also see the attached image from sql server error logs for the activity while th...
Thanks Peter. Restoring with norecovery has indeed brought the restore times down, but not to the level I would have expected. Look at this log for example. Restore log line tells that it took 0.196 seconds, yet the total duration is 20 seconds. What could be the reason?
SQL Backup log file 7.3.0.57
-SQL "RESTORE LOG ODHR FROM DISK = 'Z:\WrkFolder\ODHR_Log201305132240.trn' WITH DISCONNECT_EXISTING, NORECOVERY "
PROCESSES COMPLETED SUCCESSFULLY
5/14/2013 12:10:07 PM: Restoring ODHR (transaction logs) from:
5/14/2013 12:10:07 PM: Z:\WrkFolder\ODHR_Log201305132240.trn
5/14/2013 12:10:07 PM: RESTORE LOG [ODHR] FROM
VIRTUAL_DEVICE = 'SQLBACKUP_6204C411-C9A0-4561-BB51-22925D632DFC',
VIRTUAL_DEVICE = 'SQLBACKUP_6204C411-C9A0-4561-BB51-22925D632DFC01',
VIRTUAL_DEVICE = 'SQLBACKUP_6204C411-C9A0-4561-BB51-22925D632DFC02',
VIRTUAL_DEVICE = 'SQLBACKUP_6204C411-C9A0-4561-BB51-22925D632DFC03'
WITH BUFFERCOUNT = 24, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY
5/14/2013 12:10:27 PM: Processed 0 pages for database 'ODHR', file 'ownersSQL_dat' on file 1.
5/14/2013 12:10:27 PM: Processed 9091 pages for database 'ODHR', file 'ownersSQL_log' on file 1.
5/14/2013 12:10:27 PM: RESTORE LOG successfully processed 9091 pages in 0.196 seconds (362.337 MB/sec).
5/14/2013 12:10:27 PM: SQL Backup process ended. / comments
Thanks Peter. Restoring with norecovery has indeed brought the restore times down, but not to the level I would have expected. Look at this log for example. Restore log line tells that it took 0.19...
I prefer writing my own reports - thanks for the tip. This gives me all the information I need. I need a clarification though. The following is the output from a log file. As you can see, the restore started at 9:14:18pm and finished at 9:36:59pm - that is in 22 min 36 sec. What does 313.414 seconds (in the RESTORE LOG line) signify here then?
SQL Backup log file 7.3.0.57
-SQL "RESTORE LOG DW FROM DISK = 'T:\MSSQL\TLogs\DW_Log201305111456.trn' WITH DISCONNECT_EXISTING, STANDBY = 'T:\MSSQL\TLogs\DW_undoLog.log' "
PROCESSES COMPLETED SUCCESSFULLY
5/11/2013 9:14:18 PM: Restoring DW (transaction logs) on SQL042_1 instance from:
5/11/2013 9:14:18 PM: T:\MSSQL\TLogs\DW_Log201305111456.trn
5/11/2013 9:14:19 PM: ALTER DATABASE [DW] SET ONLINE RESTORE LOG [DW] FROM
VIRTUAL_DEVICE = 'SQLBACKUP_F758110E-23D1-46DC-B4A7-6422BCBC8113',
VIRTUAL_DEVICE = 'SQLBACKUP_F758110E-23D1-46DC-B4A7-6422BCBC811301',
VIRTUAL_DEVICE = 'SQLBACKUP_F758110E-23D1-46DC-B4A7-6422BCBC811302',
VIRTUAL_DEVICE = 'SQLBACKUP_F758110E-23D1-46DC-B4A7-6422BCBC811303'
WITH BUFFERCOUNT = 24, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , STANDBY = N'T:\MSSQL\TLogs\DW_undoLog.log'
5/11/2013 9:36:59 PM: Processed 0 pages for database 'DW', file 'DW' on file 1.
5/11/2013 9:36:59 PM: Processed 0 pages for database 'DW', file 'DW2' on file 1.
5/11/2013 9:36:59 PM: Processed 0 pages for database 'DW', file 'DW3' on file 1.
5/11/2013 9:36:59 PM: Processed 22082859 pages for database 'DW', file 'DW_log' on file 1.
5/11/2013 9:36:59 PM: RESTORE LOG successfully processed 22082859 pages in 313.414 seconds (550.461 MB/sec).
5/11/2013 9:36:59 PM: SQL Backup process ended. / comments
I prefer writing my own reports - thanks for the tip. This gives me all the information I need. I need a clarification though. The following is the output from a log file. As you can see, the resto...
How to generate report on database restore durations
I want to generate a report of the log restores of a database. The GUI has an option to generate the backup history but not the restore history. I am trying to troubleshoot something and this is ne...
How to restore encrypted backups?
I posted this question few minutes ago in a discontinued forum and then realized my mistake. So, posting in the correct forum now.
I am having some issues with the restore command when trying to re...
I am having some issues with the restore command when trying to restore encrypted backup. This is what I ran to take a backup:
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE AjayDB TO DISK = ''Z:\WrkFolder\AjayDB_201304011011_full.bak'' WITH COMPRESSION = 1, THREADCOUNT = 4, KEYSIZE = 256, PASSWORD = ''<ENCRYPTEDPASSWORD>abc12defLHG</ENCRYPTEDPASSWORD>''"'
Now, when I try to restore it via GUI, I supply the same password as above: abcd12defLHG. But it says: bad password. I tried to do it thru script as well, but did not help. I tried both of the following commands - none helped.
EXECUTE master.dbo.sqlbackup N'-SQL "RESTORE DATABASE [AjayDB] FROM DISK = ''Z:\WrkFolder\AjayDB_201304011011_full.bak'' WITH DISCONNECT_EXISTING, PASSWORD = ''abc12defLHG'', REPLACE, MOVE ''AjayDB'' TO ''Z:\WrkFolder\AjayDB.mdf'', MOVE ''AjayDB_log'' TO ''Z:\WrkFolder\AjayDB.ldf'', RECOVERY"'
EXECUTE master.dbo.sqlbackup N'-SQL "RESTORE DATABASE [AjayDB] FROM DISK = ''Z:\WrkFolder\AjayDB_201304011011_full.bak'' WITH DISCONNECT_EXISTING, PASSWORD = ''<ENCRYPTEDPASSWORD>abc12defLHG</ENCRYPTEDPASSWORD>'', REPLACE, MOVE ''AjayDB'' TO ''Z:\WrkFolder\AjayDB.mdf'', MOVE ''AjayDB_log'' TO ''Z:\WrkFolder\AjayDB.ldf'', RECOVERY"'
This is the msg generated:
SQL Backup v7.0.6.2
Restoring AjayDB (database) from:
Z:\WrkFolder\AjayDB_201304011011_full.bak
Error 710: Wrong password entered.
SQL Backup exit code: 710
(6 row(s) affected)
name value
exitcode 710
sqlerrorcode 0
filename001 Z:\WrkFolder\AjayDB_201304011011_full.bak
(3 row(s) affected)
Can you help?
Thanks.
Ajay / comments
I am having some issues with the restore command when trying to restore encrypted backup. This is what I ran to take a backup:
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE AjayDB TO DISK = ''Z...