First use sp_detach_db to detach the database files from the SQL Server Instance. You will then need to use xp_cmdshell to move the files from their current location to the new location by using the move command.
Hi MrDenny,
Thanks for your answer and prompt reply.
sp_detach_db helped me for detaching the database, but I have to schedule a job such that it runs every month first week. The job should detach the database and move the files from folder and copy them in to a different server.
I am giving a brief view of the job:
The file names will be in a source folder (2008 is year and 07 is the month in the file name)
file200807.ldf
file200808.ldf
file200809.ldf
file200810.ldf
file200807.mdf
file200808.mdf
file200809.mdf
file200810.mdf
The job should pick the below file names and copy them in to a different folder, if i run the job this month
file200809.mdf
file200809.ldf
and place these files in to a different folder on different server.
If i run the package in the next month (11th month) the package should pick only the below files
file200810.mdf
file200810.ldf
and move them in to a different folder.
synopsis: The job should detach the previous months database,it should pick the detached files only, place them in the destination folder
If i run the job in next month, it should pick the 10th month files..everything should be in a job, no manual work should be done each month.
Thanks in advance.
Discuss This Question: 4  Replies