Check out this script. Break it down and it will do the backup and copy/move for you. IF you want a new version that also zips the file...

you can use the xp_cmdshell to copy/move a database from one location to another...syntax is

exec master..xp_cmdshell

Jeff

The CTE is good enough, heck, to go from 6 to 7 hours down to two is a vast improvement. There is a mechanism to ID the dropped questions,...

Jeff

Actually, I was mocking up the query. I guess I missed that one. LD_SDT was changed to LD_ASSESSMENT but I guess my search and replace missed that one....

Jeff

I just did the same with a CTE instead of a call to a UDF. Here is the snippet.

WITH GETRDMCSCORE (SSID, GRADE, RD_MC_SCORE, organizer) AS

(SELECT b.SSID, a.GRADE, COUNT(b.RD_MC) AS...

Here is the assessment table

CREATE TABLE [dbo].[LD_ASSESSMENT](

[CODEOD] [varchar](50) NULL,

[CODEOGOD] [varchar](50) NULL,

[CODEPT] [varchar](50) NULL,

[CODEOGPT] [varchar](50) NULL,

[YEAR] [varchar](50) NULL,

[GRADE] [varchar](50) NULL,

[LITHO] [varchar](50) NULL,

[LNAME] [varchar](50) NULL,

[FNAME] [varchar](50) NULL,

[MI] [varchar](50) NULL,

[STUD_ID] [varchar](50) NULL,

[IDSOURCE] [varchar](50)...

Jeff

Now you made me think, why even use a UDF. I could just parse the string in a CTE and do a join there. That would eliminate the...

Jeff

What I have is a stored proc that passes a string of "+" and "-" which represents whether a student received a positive score on a particular question. ...

Antonio,

This saved about 25% of the time. The SP that uses the function was able to produce 7M rows in 4 Hours.

Thanks

Antonio

Thanks, I'll give it a whirl and compare the response time. I'll post back once I get the results

MD

Please post the layout of your tables. That information is needed to provide an accurate reply.

Now that the file has shrunk, perform a new full backup. This will be your starting point for any recovery after the shrinking of the file.

Also, make sure to take a full backup after you shrink the file. VERY IMPORTANT. You do this to maintain point in time recovery capabilities.

Jeff

I'm not sure if I can use this because it took so long to run. I mean, I can think of better things to do with my seven seconds...

I purchased this book in 2006 when first published as the company I was working for wanted to upgrade to SQL Server 2005 and as the Senior Database Developer/DBA, I...