Home > IT Answers > Database > A job to move database files from one server to different server
GIG
240 pts.
 A job to move database files from one server to different server
Database, LDF, MDF, SQL Server
Hi I have job which creates databases based on month in analysis services which contains cubes in the databases. I need to create a job to move the database ldf and mdf files of the previous months removing the database from the server if the month changes to next month. The database files should be copied on to a different location. Please help me on this and ready for suggestions

Software/Hardware used:
ASKED: October 5, 2008  5:15 PM
UPDATED: October 14, 2008  4:14 PM
RELATED QUESTIONS
Answer Wiki:
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.
Last Wiki Answer Submitted:  October 6, 2008  3:52 pm  by  Denny Cherry   64,520 pts.
All Answer Wiki Contributors:  Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

Denny Cherry
 64,520 pts.
 

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.

GIG
 240 pts.
 

No problem.

You can use code along these lines to move the files.

declare @cmd varchar(4000)

set @cmd = 'move d:\MSSQL\MSSQL.1\MSSQL\Data\file' + cast(datepart(yy, dateadd(mm, -1, getdate())) as varchar(4)) + cast(datepart(mm, dateadd(mm, -1, getdate())) + '.mdf \\RemoteServer\Share\Folder'
exec (@cmd)

We are dynamically setting the file name based on the month and year of the prior month.

Denny Cherry
 64,520 pts.
 

Thanks Mr denny.

GIG
 240 pts.