Tell me more ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

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
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

migrated from stackoverflow.com Jan 22 '12 at 14:38

1 Answer

up vote 4 down vote accepted 
BACKUP DATABASE [Database A]
TO DISK = 'C:\Temp\DatabaseA.bak' WITH FORMAT
GO
use master
GO
RESTORE DATABASE [Database X]
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'
,  NOUNLOAD,  REPLACE,  STATS = 10

GO
use [Database X]
GO
exec StoredProcedureName...
GO
use master
GO
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
show 1 more comment

Your Answer

 
discard

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