A job to move database files from one server to different server

240 pts.
Tags:
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
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    68,835 pointsBadges:
    report
  • GIG
    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.
    240 pointsBadges:
    report
  • Denny Cherry
    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.
    68,835 pointsBadges:
    report
  • GIG
    Thanks Mr denny.
    240 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: