backupsql server
I'm trying to backup SQL Server database to upload it to my database server. I am using Microsoft SQL Server Management Studio, Database -> Tasks -> Back Up…
And I got the following error:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)
Any idea of what's happening?
I would try scripting out the operation instead of running whatever Management Studio is doing "for" you. You shouldn't need to use xp_fileexist (assuming you can validate that the files actually exist in the place the script says). Using 2012 SSMS, I am restoring to a point in time that does not coincide directly with a log backup:
The resulting script in my case was:
BACKUP LOG ... WITH NOFORMAT, NOINIT, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY
RESTORE DATABASE ... WITH FILE = 1, NORECOVERY, NOUNLOAD
RESTORE LOG ... WITH FILE = 1, NORECOVERY, NOUNLOAD
...
RESTORE LOG ... FROM DISK = [...path from line 1...] WITH NOUNLOAD, STOPAT = <time>
In general if you have a crashed server and need to bring up your databases on a new server you want to restore from backup not copy and reattach the mdf files. The crash could have caused corruption that will not exist in the backup.
You can restore your system databases as well although it isn't always recommended. If you are going to you need to make sure your new system is as close to the old as possible. Particularly the patching level for SQL Server.
If you do not have backups, and you absolutely have to have your system databases from the old server then you have two options. You can either attach as a different dbname and copy information over (msdb and model only since it won't work for master). Or you can tell your instance that the new location of the system database is actually the correct one.
So in your case you would copy the mdf & ldf msdb files to your new server. Again make sure your patching is the same as the old server. Then follow the instructions in this link to "move" your msdb files. In other words you are going to tell it that the files from the old server are actually the correct ones. Once done you will need to re-start your new instance. Then make sure you keep the original msdb files from the install just in case.
Of course the other way to "move" your msdb files is to shut down your instance, move/rename the installed msdb files and copy in your old msdb files making sure they are in the same location & have the same name. Then restart your instance. If anything goes wrong you can simply move the installed msdb files back into place (while the instance is shut down) and try a different method.
Best Answer
You should make sure that for the database you're trying to back up, you have at least the db_backupoperator database role assigned to the user that's trying to run the backup. That should be sufficient.