Red Gate forums: SQL Backup 7

RE: Error 880: BACKUP DATABASE permission denied

petey — Thu, 23 May 2013 02:27:52 GMT

Could you please post the last SQL command, as logged by Profiler, when you run the backup via the sys_Backup procedure?

Best guess would be that the SETUSER function is raising that error. What happens if you run the

Code:

SETUSER 'fditbackup'

when logged on using the same account that the SQL Backup Agent service uses?

RE: SQL2012 AlwaysOn Availability Groups and SQLBackup

Tony Barnsley — Wed, 22 May 2013 12:47:19 GMT

What I've done on our Always On Availability groups is to modify our 'standard' Backup stored procedure to determine if the database is a member of the Availability group and if it is on the replica do a COPY Only Backup. (We don't back up on the 'live' replica . . . one benefit of the Always On Feature is to be able to take backups from one of the replicas). Our databases are of a reasonable size (~150GB) and backup times are very reasonable.

Of course actually creating the replica in the first place takes a bit of time.

What's good for us is that we have 3 SQL servers with several availability groups, and independent Databases, and this approach ensures that all the databases are backed up. The only issue is collecting together all the Full DIff and TLOG backups in the backup chain if you have to do a restore . . . Hence a COPY_TO to a common network location.

Writing your own Backup Stored procedure gives you a lot more flexibility to use SQL Backup as a tool to ensure your data is protected, rather than relying on the required functionality being added into the GUI

Error 880: BACKUP DATABASE permission denied

Tony Barnsley — Wed, 22 May 2013 12:03:08 GMT

Sorry to dig this old chestnut up again, but I have just had the need to allow a domain user to back up one database on one of our server, and am getting this error message

Code:

Error 880: BACKUP DATABASE permission denied in database: (AIMS_AdviceLink)

SQL error 4604: There is no such user or group fditbackup'.

SQL Backup exit code: 880
SQL error code: 4604

The user fditbackup has been granted a login and a user created in the relevant database, and is a member of the db_backupoperator role. I tested that the set up was working by running a standard SQL Server Backup command, with the results as below

Code:

BACKUP DATABASE AIMS_AdviceLink TO DISK = 'D:\Backup\AIMS_AdviceLink\FULL_AMIS_AdviceLink.BAK

Processed 12168 pages for database 'AIMS_AdviceLink', file 'AIMS_AdviceLink' on file 1.
Processed 1 pages for database 'AIMS_AdviceLink', file 'AIMS_AdviceLink_log' on file 1.
BACKUP DATABASE successfully processed 12169 pages in 7.671 seconds (12.994 MB/sec).

So as far as SQL Server is concerned the user is set up correctly to backup the database, the issue comes when I try and execute my standard database backup stored procedure, which generates the following SQL Backup Statement and then executes it

Code:

-SQL "BACKUP DATABASE [AIMS_AdviceLink] TO DISK = 'D:\Backup\AIMS_AdviceLink\FULL_AIMS_AdviceLink_201305220935.SQB' WITH INIT , THREADCOUNT = 3 , COMPRESSION = 3 , ERASEFILES = 2"

This stored procedure works fine when executed in Query analyser logged in as the fditbackup user, so far everything is going well, all We have to do now is put it into a command file so that our Tech team can execute it whenever they Upgrade the system rather than bother me. . . The statement is

Code:

ECHO OFF
CLS

SET RunDate=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%
SET RunTime=%TIME:~0,2%%TIME:~3,2%

ECHO AIMS Advice Link Backup Started On %RUNDATE% AT %RunTime%
CD C:\AIMS.Backup

isql -S tcp:SQLAPPS -e -w 2000 -d AIMS_AdviceLink -E -Q "EXECUTE Admin.dbo.sys_Backup @Path = 'D:\Backup\' , @JustDB = 'AIMS_AdviceLink', @Retention = 3, @ForceFull = 1, @MSBackup = 0, @Encrypt = 0, @ThreadCount = 3, @Debug=0"

CD C:\AIMS.Backup

Unfortunately this is where the

Code:

Error 880: BACKUP DATABASE permission denied in database: (AIMS_AdviceLink)

SQL error 4604: There is no such user or group fditbackup'.

SQL Backup exit code: 880
SQL error code: 4604

Occurs. It must be a permissions issue somewhere along the line. I did initially think that it was a problem with isql , but running the Standard SQL Bacuk command above in ISQL worked fine.

I have extracted the Script used to Check Permissions using SQL Profiler, but when I run that in Query Analyser (As the User fditbackup) it returns HasPermissions = 1

One last thought I had was that it could be a database owner problem so I made fditbackup owner of the database but that changed nothing.

I can't run a GUI based backup to test it,as that requires membership of Sysadmin server role which is precisely what I am trying to avoid.

Nothing in the SQL Server logs or event logs to indicate any issue.

Windows Version 2003, SQLServer Version 2000 SP4, SQL Backup Server COmponents 5.3.0.178, SQL Backup GUI 7.3.23

I suppose I could upgrade the Server Components to Version 7.2.3.12, but as we are decommissioning this server soon and moving everything to a SQL 2008 Server with 7.2.3.12 installed I'm trying to avoid that.

Any Further Ideas? I could always switch Permissions Check off, but I feel that that is a workaround of a bug rather than a fix . . .I need to know that it will be fixed when we move the databases to the new server.

Any light that can be shed on this would be a great help

RE: 7.3.0.383 Issue: Refreshing Connection for 20m & counting

mglenn — Fri, 17 May 2013 13:51:54 GMT

Has Red-Gate looked into performance tuning sqbdata? Activity History still takes forever to populate--GT 5 minutes for us on a server that contains 1 year of history.

Customers should not be forced to delete important backup history as a workaround for a poorly performing SQL Backup process.

RE: How to generate report on database restore durations

agarg — Thu, 16 May 2013 17:01:56 GMT

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

RE: How to generate report on database restore durations

petey — Thu, 16 May 2013 02:14:19 GMT

The longest process is this entry:

2013-05-15 11:06:02.21 spid61 Restore-Redo begins on database ODHR

and this post explains what is actually done during this process:

http://msdn.microsoft.com/en-us/library/ms191455(v=sql.105).aspx

The Restore-Redo process might just be taking a reasonable duration, or it might actually be slow, depending on your setup. If you think it should complete faster, one option may be to look at the virtual log files (VLFs) of your database (see http://adventuresinsql.com/?p=12).

RE: How to generate report on database restore durations

agarg — Wed, 15 May 2013 16:42:22 GMT

Don't understand how to upload an image. So, here is the log copy/pasted from ERRORLOG:

2013-05-15 11:06:01.90 spid61 RestoreLog: Database ODHR
2013-05-15 11:06:01.90 spid61 X-locking database: ODHR
2013-05-15 11:06:01.90 spid61 Opening backup set
2013-05-15 11:06:01.94 spid61 Restore: Configuration section loaded
2013-05-15 11:06:01.94 spid61 Restore: Backup set is open
2013-05-15 11:06:01.94 spid61 Restore: Planning begins
2013-05-15 11:06:01.94 spid61 Dismounting FullText catalogs
2013-05-15 11:06:01.94 spid61 Restore: Planning complete
2013-05-15 11:06:01.94 spid61 Restore: BeginRestore (offline) on ODHR
2013-05-15 11:06:02.00 spid61 Restore: PreparingContainers
2013-05-15 11:06:02.00 spid61 Restore: Containers are ready
2013-05-15 11:06:02.00 spid61 Restore: Restoring backup set
2013-05-15 11:06:02.00 spid61 Restore: Transferring data to ODHR
2013-05-15 11:06:02.00 spid61 Restore: Waiting for log zero on ODHR
2013-05-15 11:06:02.00 spid61 Restore: LogZero complete
2013-05-15 11:06:02.21 spid61 FileHandleCache: 0 files opened. CacheSize: 48
2013-05-15 11:06:02.21 spid61 Restore: Data transfer complete on ODHR
2013-05-15 11:06:02.21 spid61 Restore: Backup set restored
2013-05-15 11:06:02.21 spid61 Restore-Redo begins on database ODHR
2013-05-15 11:06:18.84 spid61 Rollforward complete on database ODHR
2013-05-15 11:06:18.84 spid61 Restore: Done with fixups
2013-05-15 11:06:18.84 spid61 Restore: Writing history records
2013-05-15 11:06:18.84 Backup Log was restored. Database: ODHR, creation date(time): 2013/02/28(19:47:47), first LSN: 1440831:20705:1, last LSN: 1440832:29672:1, number of dump devices: 4, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_40E17D6B-2515-4AA7-89DC-E17B6C50AC90', 'SQLBACKUP_40E17D6B-2515-4AA7-89DC-E17B6C50AC9001', 'SQLBACKUP_40E17D6B-2515-4AA7-89DC-E17B6C50AC9002', 'SQLBACKUP_40E17D6B-2515-4AA7-89DC-E17B6C50AC9003'}). This is an informational message. No user action is required.
2013-05-15 11:06:18.84 spid61 Writing backup history records
2013-05-15 11:06:18.86 spid61 Restore: Done with MSDB maintenance
2013-05-15 11:06:18.86 spid61 RestoreLog: Finished

RE: How to generate report on database restore durations

agarg — Wed, 15 May 2013 16:35:45 GMT

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]

RE: How to generate report on database restore durations

petey — Wed, 15 May 2013 00:39:44 GMT

There is a trace flag you can use to log the restore process in detail. Try this:

Code:

DBCC TRACEON (3004, 3605, -1)

SQL Server will then log the additional details to the error log. Do the details explain why the restore process is taking longer than the reported duration?

RE: How to generate report on database restore durations

agarg — Tue, 14 May 2013 18:07:08 GMT

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.

Restoring to a different SQL Server

maddave — Tue, 14 May 2013 14:41:27 GMT

Hi,
Everything I read seems to say this is possible through the GUI, but I cannot see the option to do it.
I am trying to restore a database taken on ServerA, via SQL Backup, to ServerB using SQL Backup Gui. Lets say to allow a test server to be created.

ServerA is taking regular daily backups created using the GUI Tool.
I have added ServerB to the GUI and installed the server components and licensed it.
I right click on ServerB, select restore. I then select "browse for backup files to restore" and select ServerA as the backup to restore.
I then click on Add files and browse to my central backup store and select the last full backup for ServerA.
I then click Next, but shown a screen "step 2 of 4", which shows the Destination Database is set to ServerA - the original SQL Server and I cannot change this. I only have the options to overwrite the existing database or create a new one. I tested this and created a new one and it does indeed write the backup to the original server.

This article http://www.red-gate.com/supportcenter/content/knowledgebase/SQL_Backup/KB200710000174
explains this process as I have, but no where does it say you can restore the database to a different sql server, despite it's title!

Any help would be great.

Thanks.

*******EDIT***
I've just realised my mistake with this and now have it working. It's not clear on the screens, but the first screen, I should leave set at Server B, and then simply add files from ServerA. On the "step 1 of 4" it says "Backups to restore SQL Server:..." And this drop down should be set to the destination SQL Server, not as it implies, the source SQL Server. I have this working now.

RE: How to generate report on database restore durations

petey — Tue, 14 May 2013 01:05:24 GMT

The 313.414 seconds is the time taken to read and restore the backup data. The rest of the time would be used to re-apply the uncommitted transactions from the redo log prior to the restore process, and also to undo any open transactions from the current restore process, and write them to the redo log.

If you want to confirm that this is indeed the case, try restoring a couple of trx logs with the NORECOVERY option. The total duration taken should be close to what SQL Server reports on the 'RESTORE LOG successfully processed...' line.

RE: How to generate report on database restore durations

agarg — Mon, 13 May 2013 20:46:54 GMT

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.

RE: Click on server name, green circle spins forever

reinisb — Mon, 13 May 2013 14:30:45 GMT

In case anybody else comes across this thread, the solution that actually worked for me was to select the Delete Old Backup History in the Server Options dialog (right-click the server name to access). Once I selected that and set a number of days, it took a lot less time for Redgate to show the timeline and activity history correctly.

RE: Centrally store all server information

eddie davis — Mon, 13 May 2013 07:53:32 GMT

Thank you for your forum post.

The GUI is configured per user on each machine.

The file that contains the information regarding the servers you have added to the GUI is located in this path:

C:\Users\<user name>\AppData\Roaming\Red Gate\SQL Backup
The file is called "Servers.dat". If you copy this file from your local PC to the central server and paste into each users profile, you and colleagues will be able to share the original setup.

If you or a colleague makes a change on the central server to their version of GUI, this change will not be replicated to the other users "Servers.dat" file.

Many Thanks
Eddie