Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I create a process that will:

  • backup Database A restore it as Database X (needs to be definable)
  • then run a stored procedure against Database X.
  • shrink and truncate its log file.

Anybody got ideas? Could it be a stored proc on the Master db?

The whole process takes hours and is taking me away from valuable time.

share|improve this question

migrated from Jan 22 '12 at 14:38

This question came from our site for professional and enthusiast programmers.

Fyi, don't include the log file in the back-up since you're going to axe it anyway :) – Kris Feb 10 '11 at 16:32
Yes its a sql server – Rico Feb 10 '11 at 16:42
@Kris how do i not include the log in the backup.. Mostly the log problem comes from all the transactions that happen after i restore in the stored procedure.. – Rico Feb 10 '11 at 16:43
alter database [database name] set recovery simple – DForck42 Feb 10 '11 at 21:16
up vote 4 down vote accepted
TO DISK = 'C:\Temp\DatabaseA.bak' WITH FORMAT
use master
FROM  DISK = N'C:\Temp\DatabaseA.bak'
,  MOVE N'DataFile' TO N'C:\Foo\DatabaseX.mdf'
,  MOVE N'LogFile' TO N'C:\Bar\DatabaseX.ldf'

use [Database X]
exec StoredProcedureName...
use master
DBCC ShrinkDatabase( [Database X] )
share|improve this answer
no joke its that easy? Anyway i can define where log and mdf files go? – Rico Feb 10 '11 at 16:37
@Rico - Of course. The paths I used were merely for demonstration purposes. On the restore command, you can add a MOVE directive that will tell it where to put the mdf and ldf files. Btw, given your post, I assumed this was SQL Server. – Thomas Feb 10 '11 at 16:39
@Thomas - Awesome thanks man.. – Rico Feb 10 '11 at 16:41
@Rico - I updated my answer to show you what it would look like to place the mdf and ldf files. Obviously, you will need to know the name of the files in the database (in my example I used DataFile and LogFile) which you can get by looking at the properties of the database in Management Studio. – Thomas Feb 10 '11 at 16:42
@Thomas Perfect thanks man.. ima get this to good use immediately – Rico Feb 10 '11 at 16:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.