Solved

osql command to delete .mdf file on c:

Posted on 2006-11-07
15
599 Views
Last Modified: 2008-01-09
Hi,

    I am running an Osql script that backs up a mdf through a script. Is there a way in Osql that I could delete the mdf on the C: drive after x amount of days?
0
Comment
Question by:missymadi
  • 6
  • 6
  • 3
15 Comments
 
LVL 11

Expert Comment

by:regbes
ID: 17890963
Hi missymadi,

if you can do it in a batch file run the batch file with xp_cmdshell

0
 

Author Comment

by:missymadi
ID: 17894115
Does anyone have the code\syntax to delete a file on C: from osql?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17894727
You run OSQL from the command line. Therefore you could just detach the database within ISQL and then run a standard DEL dos command.

i.e. your batch file would look like this:

OSQL ............ your commands
OSQL  .......... a sp_detach_db command to detach the MDF
DEL your MDF file


Unless of course the MDF filename is known only within SQL.... then you need to use xp_cmdshell as mentioned.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:missymadi
ID: 17895116
What I looking for is a line of code that would run something like this

Delete database.mdf on C: after 7 days. It is the database.mdf file that I need to erase on the C: drive not within SQL.

What is the specific syntax for this command?

For example:
BACKUP DATABASE TEST to Disk=C:\TEST.bak

How do I delete TEST.bak on the C: drive every 7 days through and OSQL command?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17896358
The DOS command is this:

DEL Test.MDF



The OSQL command is this:


EXEC master.dbo.xp_cmdshell 'DEL Test.MDF'


The tricky part is working out how old the file before deciding to delete it. This can probably be done in T-SQL but before I start down that road, this is a very curious requirement.... why do you want to delete MDF files that are 7 days old?
0
 
LVL 11

Expert Comment

by:regbes
ID: 17896380
backing up the MDF files as a backup strategy is not recomended

why do you not set up a maintnance plan and set the plan only to keep the last 3 weeks of backups?
0
 

Author Comment

by:missymadi
ID: 17906781
Hi Nmcdermaid,

     Request from my boss to delete the .mdf from the C: drive after "x" amount of days. What he is looking for is something to run daily that looks for the oldest directories and deletes them. The OSQL commands will be added to an existing script.

If there is a better strategy, please advise.

Thanks for your help!
0
 
LVL 11

Expert Comment

by:regbes
ID: 17907020
missymadi,

why do you not set up a maintnance plan to back the database up and set the plan only to keep the last 3 weeks of backups?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17924637
What's the big picture? it is very unusual to delete databases after they get to a certain age. Won't someone be using the database?


0
 

Author Comment

by:missymadi
ID: 17931062
Hi,

    This is for an SQL instance of a database that cannot be backed up through Enterprise Manager.

What we hope to accomplish: Back up an instance of a db to a separate location, say c:, for 7 days. When the date of backup is older than 7 days, delete the backup copy of the db instance.

The line of code is added to an existing script we have

c:\>osql -s peach\fracascorp -u sa
password
1>backup database fracas to disk="c:\fracas.bak"
2>go

This is only to back this db up in case of failure. Everytime this runs, another full copy of fracas is copied to c:\fracas.bak. We wanted to delete the fracas.bak after  7 days.

Is there a better solution? I have looked into Enterprise Manager to back up the db but I'm guessing because it is an instance all options are not available.
Help, Thanks!
0
 
LVL 11

Expert Comment

by:regbes
ID: 17931933
> This is for an SQL instance of a database that cannot be backed up through Enterprise Manager.
not too sure such a thing exsists - if you set a maintnance plan for it up correctly it will be backed up and old backups cleaned up

or what about
c:\>osql -s peach\fracascorp -u sa
password
1>backup database fracas to disk="c:\fracas.bak" with RETAINDAYS = 7
2>go
0
 

Author Comment

by:missymadi
ID: 17933306
HI Regbes,

       When you say set up a maintenance plan, do you mean through Enterprise Manager or another tool?

Thanks!
0
 
LVL 11

Expert Comment

by:regbes
ID: 17936214
missymadi,

Through Enterprise Manager

you should be able to connect to any database and manage it with EM it does not matter if it is an instance or the default install
0
 

Author Comment

by:missymadi
ID: 17939029
Thanks for the info.

 I went through Database Maintenance through EM and set up a test backup. The maintenance plan will not hold the destination folder. Is there a white paper on how to set a maintenance plan?

Thanks.
0
 
LVL 11

Accepted Solution

by:
regbes earned 75 total points
ID: 17940120
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question