How to backup and shrink a database from a DTS?

#1

Julie and Jeff,

Thanks to both of you for the replies. For future, I will post all DTS
related queries ONLY to the DTS group :-)

Here's what I did:
I first made 2 separate tasks and scheduled both of them. Here's what I
get by doing a copy paste of the properties of the saved (scheduled)
jobs (the LAST LINE is the copy/paste of the Shrink job's property):

BACKUP DATABASE [MyDatabase] TO
DISK = N'C:\SQL Backups\MyDatabase' WITH INIT , NOUNLOAD , NAME =
N'MyDatabase', SKIP , STATS = 10, NOFORMAT
DECLARE @i INT
select @i = position from msdb..backupset where
database_name='MyDatabase'and type!='F' and
backup_set_id=(select max(backup_set_id) from msdb..backupset where
database_name='MyDatabase')
RESTORE VERIFY ONLY FROM
DISK = N'C:\SQL Backups\MyDatabase' WITH FILE = @i
DBCC SHRINKDATABASE (N'MyDatabase', 0,TRUNCATEONLY)

As I understand from yours and Jeff's reply is that in my DTS, all I
have to do is add a new SQL TASK and then in it's properties, just
copy/paste all of the above SQL. Right?

My last but important question is my concern regarding the following :

Quote:

RESTORE VERIFY ONLY FROM
DISK = N'C:\SQL Backups\Sales DataMart' WITH FILE = @i

Does the above mean that if I try to restore the backup on another
machine, I will not be able to do so unless the backed up MDF file does
not reside in a folder with the same name (i.e. C:\SQL Backups\) on the
other computer??? Or does it mean that I will be able to restore but not
verify???

Thanks.

Quote:

-------Julie Wrote---------------------------------
1. Use the Connection "Microsoft OLE DB Provider For SQL
Server" and point it to the database you wish to back up.

2. Depending on who is going to run this i.e. a user or an
administrator change the authentication from Windows to
SQL Server authentication (the reason is the person who
will eventially run it may not have rights to perform the
commands.

3.Under the task select 'Execute SQL Task'.

4. Right click and select properties, then type in the
following code

EXEC sp_addumpdevice 'disk', <Backup Name>, <Backup Name>;
Backup database <Database> to <Backup Name>, @FileName;
dbcc shrinkdatabase (<Database>, 10)

HOWEVER
DTS is not the best way of doing this, create a normal job
instead.

J

BTW there is a DTS newsgroup

-------Jeff Wrote---------------------------------
Answer 1
You can find the syntax for all you need in BOL.

Now that I have given you the blowoff answer let me give you the
shortcut answer also.

Answer2
Use the built in wizards to create scheduled tasks for you and take
the code from there.

example.
Right click the databases and select the Shrink. select the files
you want to shrink and select to shrink file later on a schedule.
When you are done selecting this then a scheduled task will be created.
Go into the scheduled task and just steal your own code. Put that in a
DTS package. You can do the same thing with doing a backup.

Hope this helps. After you do that then please look up that
same syntax in BOL and learn how to do this they "Right" way

Jeff
MCDBA, MCSE+I

#2

"Learner" <wantnospam (AT) email (DOT) com> wrote

Quote:

Julie and Jeff,

Thanks to both of you for the replies. For future, I will post all DTS
related queries ONLY to the DTS group :-)

Here's what I did:
I first made 2 separate tasks and scheduled both of them. Here's what I
get by doing a copy paste of the properties of the saved (scheduled)
jobs (the LAST LINE is the copy/paste of the Shrink job's property):

BACKUP DATABASE [MyDatabase] TO
DISK = N'C:\SQL Backups\MyDatabase' WITH INIT , NOUNLOAD , NAME =
N'MyDatabase', SKIP , STATS = 10, NOFORMAT
DECLARE @i INT
select @i = position from msdb..backupset where
database_name='MyDatabase'and type!='F' and
backup_set_id=(select max(backup_set_id) from msdb..backupset where
database_name='MyDatabase')
RESTORE VERIFY ONLY FROM
DISK = N'C:\SQL Backups\MyDatabase' WITH FILE = @i
DBCC SHRINKDATABASE (N'MyDatabase', 0,TRUNCATEONLY)

As I understand from yours and Jeff's reply is that in my DTS, all I
have to do is add a new SQL TASK and then in it's properties, just
copy/paste all of the above SQL. Right?

Yes.

Quote:

My last but important question is my concern regarding the following :

RESTORE VERIFY ONLY FROM
DISK = N'C:\SQL Backups\Sales DataMart' WITH FILE = @i

Does the above mean that if I try to restore the backup on another
machine, I will not be able to do so unless the backed up MDF file does
not reside in a folder with the same name (i.e. C:\SQL Backups\) on the
other computer??? Or does it mean that I will be able to restore but not
verify???

You can change the path for when you restore it on another server. For
example you can refernce the full path to the original server, e.g.

RESTORE ...
FROM DISK = N'\\OtherServer\Share\SQL Backups\Sales DataMart' WITH FILE =
@i

or copy the file to a suitable location on the target server

RESTORE ...
FROM DISK = N'C:\My Folder\Sales DataMart' WITH FILE = @i

or copy it to an exact match folder name on teh target server and just use
the same path as above.

Have a look at the RESTORE topic in Books Online for more options.

--
Darren Green
http://www.sqldts.com

#3

Darren,

Thanks for the reply and clearing things up.

Regards.

Quote:

"Learner" <wantnospam (AT) email (DOT) com> wrote in message
news:MPG.1af72fe5f632d19d9896fe (AT) msnews (DOT) microsoft.com...
Julie and Jeff,

Thanks to both of you for the replies. For future, I will post all DTS
related queries ONLY to the DTS group :-)

Here's what I did:
I first made 2 separate tasks and scheduled both of them. Here's what I
get by doing a copy paste of the properties of the saved (scheduled)
jobs (the LAST LINE is the copy/paste of the Shrink job's property):

BACKUP DATABASE [MyDatabase] TO
DISK = N'C:\SQL Backups\MyDatabase' WITH INIT , NOUNLOAD , NAME =
N'MyDatabase', SKIP , STATS = 10, NOFORMAT
DECLARE @i INT
select @i = position from msdb..backupset where
database_name='MyDatabase'and type!='F' and
backup_set_id=(select max(backup_set_id) from msdb..backupset where
database_name='MyDatabase')
RESTORE VERIFY ONLY FROM
DISK = N'C:\SQL Backups\MyDatabase' WITH FILE = @i
DBCC SHRINKDATABASE (N'MyDatabase', 0,TRUNCATEONLY)

As I understand from yours and Jeff's reply is that in my DTS, all I
have to do is add a new SQL TASK and then in it's properties, just
copy/paste all of the above SQL. Right?

Yes.

My last but important question is my concern regarding the following :

RESTORE VERIFY ONLY FROM
DISK = N'C:\SQL Backups\Sales DataMart' WITH FILE = @i

Does the above mean that if I try to restore the backup on another
machine, I will not be able to do so unless the backed up MDF file does
not reside in a folder with the same name (i.e. C:\SQL Backups\) on the
other computer??? Or does it mean that I will be able to restore but not
verify???

You can change the path for when you restore it on another server. For
example you can refernce the full path to the original server, e.g.

RESTORE ...
FROM DISK = N'\\OtherServer\Share\SQL Backups\Sales DataMart' WITH FILE =
@i

or copy the file to a suitable location on the target server

RESTORE ...
FROM DISK = N'C:\My Folder\Sales DataMart' WITH FILE = @i

or copy it to an exact match folder name on teh target server and just use
the same path as above.

Have a look at the RESTORE topic in Books Online for more options.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How to backup and shrink a database from a DTS?

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

How to backup and shrink a database from a DTS? - 04-26-2004 , 04:43 AM

Re: How to backup and shrink a database from a DTS? - 04-26-2004 , 08:01 AM

Re: How to backup and shrink a database from a DTS? - 04-26-2004 , 08:35 PM