Database Backup Size is Limited

I am having a problem with a client database creating a backup. The client is on Windows 2000 Server, SQL Server 2000, running Navision 3.70. I need to restore a copy of their data at my office to research some problems and make adjusting entries. When I create a backup of the database, it errors our when I hit the 2097151kb limit on the second progress bar of the backup screen. The database is 13Gb in size and it takes about 3-4 hours to WinRAR the .bak file from the nightly SQL Server backup. It was 90% finished with the backup when it reached the limit and I would rather compress a 2Gb file than a 13Gb so it doesn’t take me all day to create a compressed backup and transfer it to our office. Does anyone know how I can create a larger Navision backup file? Is this a licensing issue I need to submit to Microsoft? Thanks. David

Use the SQL Server backup to backup the DB… You are using the Native CSide backup.

Are you backing up to a NTFS drive? Don’t use integers in the filename, use BU.fbk. when Navision hits the 2Gb limit it will create BU2.fbk

I tried using “bu” as the backup file name (as well as several other combinations without numbers) but Navision just froze once it hit the 2Gb limit. It never created the second backup file and after an hour with the filesize not changing, I had to do an end task on Navision. Also, I have the SQL backup and was able to restore that, but there is one huge problem with that scenario. I am the only person in my department that has sysadmin access to the SQL Server. The IT department will not give full access to anyone else in the department for security reasons. I function as one of the dbas along with my Navision Developer role; otherwise, I would not have sysadmin access either. Any other ideas?

Perhaps they could give you dbcreator and db_backupoperator server roles, rather than sysadmin? Does that allow a restore though - not sure.

Not knowing all the constraints you face do a SQL backup to disk (including ALL the required files) copy the backup file on disk to a REMOVABLE DISK, such as what many of the disk mfg have (external hard disk with a USB2/Firewire/cat5 interface). Using an external drive remove the size limitation, as these external drives are much larger than your db. at your office you can either copy the file to your computer then do a restore, or restore directly from the external drive. gud luk Gary

By the way, if IT won’t give you the required authorizations, then IT has to get involved and support you, or the whole thing won’t work. If you need to do this to support a client, then it is a business issue, and your boss needs to get involved. Being a security type…you just don’t want to get into the work-around-the-system issues. In this day and age, computer security issues are not something to be taken lightly. gud luk Gary

This issue is to support a client. The problem is I am viewed as a hybrid employee. My primary job responsibility is for our consulting department doing Navision support and development. My other unofficial role is backup IT staff. If there are huge projects and they need another person, I am the one called upon since I was a network admin before coming to work here. That is the reason I was made the resident dba and given full permissions to the SQL server. They aren’t going to let me grant full access to the other members in my department and when I restore the SQL backup, I have to create all the database login accounts on the SQL Server temporarilty to sign on to Navision and remove the users from the security table. I got a new updated 4.0 license from MBS yesterday, but it didn’t seem to help my problem. I would rather not have to mess with the SQL backup since that would leave the database restoration on my shoulders. I am trying to offload tasks like restoring databases to other people since it is such a simple process (under normal circumstances). I submitted an incident request to MBS to see if they had any solutions. If there was some way to override the 2Gb Navision backup limit, this wouldn’t be an issue. Fortunately, we only have 1 client with this problem. David

I got past the 2Gb limitation on the Navision backup file size. Navision will append a 2, 3, 4…to the end of the backup name when you do a backup…if you set the database to Single User Mode. MBS told me I was supposed to be putting the db in single user mode to do an internal bacukp. This is news to me since it was never mentioned in class that we had to do that. I thought one of the advantages of Navision (both native and SQL versions) was the ability to do a backup while others were in the database. Anyway, just wanted to update the topic in case anyone else ran across this issue in the future. [Sigh…]

Man if you have to set it to single user mode, you might as well do an offline backup. It’s been a while, so I don’t remember the details, but as I remember in SQL svr; - stop the db - you can copy the db files (don’t forget the master db file) - restart the db - copy the files to your server, - then use the attach command to tell SQL server to attach to the new file. I used this process to rebuild one of my servers, its simple and it worked. Well the only tricky part was the SQL commands to do the attach…since I’m not a SQL person. About the granting of access rights. The company, including IT, has to face up to the problem if they want to support their clients. If IT doesn’t want to grant access to others, maybe with very good reasons, then they have to step up to the plate and provide the support to support your clients. After all the client is the one that pays the bills. Funky work-arounds only make the process more inefficient. gud luk Gary