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

Processing your response...

Discuss This Question: 12 &nbspReplies

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

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 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

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.

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.

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?

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

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.

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.

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!!!

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

Share this item with your network:

To follow this tag...

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