detach the .mdf files from sql server

55 pts.
Tags:
SQL Server
SQL Server backup
hello,

i am trying to detach the database from sql server. well the seniorio is i am try to do automate everymonth to detach all the previous month .mdf files.

so is it possible to automate it to detach the previous months database by getting the system current date.



Software/Hardware used:
software

Answer Wiki

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

Why are you trying to detach the database? Can’t you just take a backup of the database?

You can easily enough configure a job to run on the 1st of every month.

Discuss This Question: 12  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Helpmeout
    thanks for replying... i am in very much in need of help. well i am unable to make bachup without detaching it and i am detaching one by one. so want to detach previous month databases at single time and make backup. i have these kinds of files user_experience_20111001_Data.mdf ............................... ............................... .............................. user_experience_20111030_Data.mdf user_experience_20111101_Data.mdf ......................... ....................... user_experience_20111120_Data.mdf it will be appreciated if you help me out soon. thanks
    55 pointsBadges:
    report
  • Denny Cherry
    Why can't you make a backup? How are you trying to do the backups? Are you using the BACKUP DATABASE command within SQL Server to make the backups?
    66,290 pointsBadges:
    report
  • Djj55
    You do not need to back up the .mdf files. Use the BACKUP DATABASE command (as Mrdenny suggests) and backup the resulting file. If you must back up a "live" database there are several third party software solutions.
    20 pointsBadges:
    report
  • Helpmeout
    thanks for replying .. but my problem is not solved yet.. 1st i am moving those files to different server and i am unable to do without detaching it. and also the database are still showing up in sql server management studio.. so to not show up the databases in sql sever studio i have to detach the files. so if you have solutions of my above problem please help me soon.
    55 pointsBadges:
    report
  • Helpmeout
    or anyone has the soln to solve my problem
    55 pointsBadges:
    report
  • Denny Cherry
    I'm not sure where the problem is. You can easily enough take a database backup to another server and restore it. Can you be more clear about what the end goal here is. Are you trying to just copy the data to another server daily, or are you trying to actually move the database from one server to another?
    66,290 pointsBadges:
    report
  • Helpmeout
    yes, i am moving the database from one server to another. so 1st i am detaching the previous month databases and then move to different server. i am doing this once in a month. like current month is November then i will detach all the October month databases and then move to different place. actually i want to do this automated so come up with some script to move the files automatically every month, but getting problem to detach the mdf files every month automatically. i have some script which detach all the mdf files at a time of of any specified month but i want to make it automation , like some kind of store procedure or something that can run once a month and detach the previous month mdf files. if it happen then other moving files will be done easily by my other script. here is the code that i am using to detach. DECLARE @strObjName VARCHAR(1000) DECLARE @strCmd VARCHAR(1000) DECLARE selobj CURSOR LOCAL FOR SELECT name FROM sys.databases WHERE name like('user_experience_201111%') OPEN selObj FETCH FROM selObj INTO @strObjName -- PRINT 'Object Name = ' + @strObjName WHILE @@FETCH_STATUS = 0 BEGIN SET @strCmd = 'EXEC master.dbo.sp_detach_db ' + @strObjName --PRINT @strCmd --PRINT '' EXEC(@strCmd) FETCH NEXT FROM selObj INTO @strObjName END CLOSE selObj DEALLOCATE selObj
    55 pointsBadges:
    report
  • Colin Smith
    I have to say that I also do not understand why you need to do the detach when a backup database should be sufficient. But if you need to detach then you can do that with t-sql or powershell. If you need to detach, copy the file to another location, and then re-attach the file I would do this in Powershell and use SMO to complete the detach and attach process.
    885 pointsBadges:
    report
  • Denny Cherry
    Instead of detaching and moving the files, which can get messy as there are multiple files I would backup the databases, move the backup (as there's only one file per database now), then restore the backup to the other server. Then once you have verified that the backup has been restored drop the database from the first server. This entire process can be automated so that you never need to look at it again. You'll need a job on the first SQL Server with a few steps. The first to backup the databases. The code you showed will do the trick, just change it from using sp_detach_db to BACKUP DATABASE. The second job step would copy the files to the other server (using something like robocopy would be easiest). The third would restore the databases based on the files in the folder. The output from xp_dirtree is the easiest way to see all the files in a folder then use the result set to build a cursor to restore the databases.
    66,290 pointsBadges:
    report
  • Helpmeout
    thanks guys for your informations. well i solved my problems to detach the files automatically everymonth. my problem was i need to move mdf files to unix server for backup every month and then delete the files from sql sever. and i was getting problem with detach the previous month files everymonth automatically. because in my server withous i was unable to move or copy or either delete those files. so i need to first detach those files.... anyway thanks for sharing your ideas to me thank you!!!
    55 pointsBadges:
    report
  • Helpmeout
    [...] Helpmeout causes some confusion when they want to detach a database to move it, but the community’s SQL pros tell them moving a backup does the same with less [...]
    0 pointsBadges:
    report
  • stevejefferson
    Make use of next software http://www.recoverytoolbox.com/sql.html

    Application is able to restore system tables and user tables, stored procedures, views, user defined data types and functions
    20 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following