If you were a hacker with SA access on a SQL Server where someone managed to completely lock out xp_cmdshell, wouldn't it just be as efficient to load some unsafe...

What's the used space of each file?

How did you move the data between partition?

Using xp_cmdshell is like having another process accessing your table, which is locked by your open transaction.

That shouldn't work with a regular table neither, unless you didn't use an explicit...

To be honest, if the user knows enough SQL to write down the select statement, they are probably good enough to understand the original error message.

Short of trying to parse...

I prefer BCP over the BULK_INSERT.

It doesn't require server role grant, SQL Server doesn't have to be able to see the file and you won't run into account delegation issue.

ETL...

"Device not ready" usually means the server lost a disk (if your disk is a SAN device), check with your SAN admin to see what happened.

You can confirm it by...

If you know powershell you can use a simple powershell script to do that for you, no need for the sqlps.exe.

You can use this to get you started for the...

Date based cleanup like this are usually very good candidate for partitioning (if you have an enterprise edition), you would have a one time downtime to copy the data to...

Yes, as long as the database is in full recovery mode.

See here for more info: http://msdn.microsoft.com/en-us/library/ms186865(v=sql.105).aspx

First of all, check if the statement is not spending most of it's time waiting for a lock on the table to be freed.

If i's not the case then BCP...

That just mean you had bad indexes before, partition just helped you by chance.

To improve performance, partitioning need to be done with good indexing strategy as Paul told you.

Most of...

Partitioning does not disable your table integrity.

What you want is a cleaning/merge procedure that gets rids of old account number and insert new one, or even better, a real ETL...

You can also test the if the bottleneck is coming from the backup disk or your data disk.

Backup to nul to test backup read speed, if it doesn't change (or...

Either copy the data to a new table created in the other FG or create a clustered index on the new FG then drop it.

1. New objects will be created in the default filegroup if it's not specified in the create statement you can change the default filegroup if you want, the PRIMAY filegroup...