New answers tagged

2

I've always had such an aversion to cursors, that I wrote this as I can better understand it. It's totally based off of AA.SC's answer (thank you by the way), just put in a way that I think. If this jives with what others think, then great. Note, I didn't put it back into Full recovery mode afterward though. SELECT '--', d.name dbName, d.recovery_model, ...


3

I think you will find that if you use an SSIS package to move your data between multiple servers @RobFarley's suggestion will work just fine. You are transmitting information across the network either way. In one case a backup file and in the other the data. However if you are absolutely against the idea you can use a combination of the two ideas. ...


4

As this database will only be used for reporting, we are thinking of shrinking the database. A one time sensible shrink is OK. Disk space is cheap ! Increases the index fragmentation is the side effect of doing a shrink operation. Its advisable to shrink using DBCC SHRINKFILE during maintenance window or when there is minimal activity. Shrink database ...


4

Instead of doing a backup/restore, consider moving data across into a new, presized database, and creating new indexes on this data. This should keep your file size small and your indexes unfragmented. Also make sure you have compression turned on.


1

AMtwo is completely right, and in addition at a 500 GB data size your shrink may take days to complete. The easier way to do this is to just move all of the data from your original database into a new appropriately sized database either using SSIS or INSERT INTO ... SELECT statements. The only thing SHRINKFILE is good for, is if you created a data file that ...


5

Step 0) Shrink disclaimer I'm not even going to say it. I'll let Brent Ozar tell you to Stop Shrinking Your Database Files. Seriously. Now. In your comments, you mention you deleted 50% of the data in the database, so you very likely have a good use case for a one-time database shrink. (Assuming that the deleted data isn't going to come back.) I'm not ...



Top 50 recent answers are included