Put the backup file on a separate volume and set an exclusion for that volume within your AV program so it can't touch the file.

I have used it a lot for File ftp tasks, credit card charging, xml importing from flat files

Jeff Moden (7/23/2012)

---

SQLRNNR (7/23/2012)

---

I would just call the stored procedure from within SSIS if you must convert it to SSIS. I'm not sold that SSIS is the right way...

It seems that AV is locking that file. That would be my first bet on it.

Do you have antivirus running on that server? Is the backup file local to the mirror partner where you are trying to restore it?

I would run it up the chain as a security hole. Explain the problems and then work to remove all users that are not DBA from the sysadmin role....

Here is a good reference on the multiple data files thingy...

http://www.sqlskills.com/BLOGS/PAUL/post/Benchmarking-do-multiple-data-files-make-a-difference.aspx

Backing Jonathan on this - yes SQL Server can only use what the OS is constrained to as a maximum.

Nice series Gail.

Do you have a test environment to test the changes first with a table of the same size?

Which part do you need help with?

Are you running SSMS as an Administrator? It may be UAC interfering and causing the errors to crop up.

For the record, take the query changes with the timing comparisons and show them outright. They probably won't change anything but you will at least have documentation at the...

It's them.:cool:

I would just call the stored procedure from within SSIS if you must convert it to SSIS. I'm not sold that SSIS is the right way to do this...