SQL Database

[Hawk]

I need to work on a vb project at home and on the weekend. What is the fastest and most correct way to bring a database home from work. And "Yes", I have permission to do so.

Please show me step by step on how to Backup and Restore a database. I done it a couple of time but am not sure if it is the correct way. We are using SQL 7.

Thanks

[McGenius]

It's not a simple thing to do at all. RDBMSs such Oracle, SQL Server are based on files which might be spreaded all over the hard drive. Entire DB may have thousands of objects in it including Tables, SP, Triggers, Indexes, etc, etc, etc ... The most efficient way would probably be to copy a backup file to a CD and try to "restore" your DB from it at home. Another way is to export ALL object to an ASCII file by generating scripts. Yhis may take a long time and destination file size could grow significantly. Data itself could be exported to an Access DB and then imported into your home DB, but this method won't include any constraints on the tables (indexes, etc ...) at all. I usually use First method. The only problem with it is - will the backup file fit onto 1 CD or not.

[McGenius]

Forgot to mention: those scripts that you may generate will need to be excuted inorder to have ALL objects created first and then you may import your actual data from Access (if you decide to do so).

[Hawk]

Hi McGenius,

Can't I just copy the MDF and LDF files under \MSSQL7\Data and take them home to restore? I think that is how I did it before but don't know if that is the correct way.

Thanks,

Hawk

[McGenius]

If they fit onto your media and after all it works yet - then what the heck.

[Hawk]

But how would you restore it?

I think it is better to backup into a BAK file.

[Static]

is there no way you can connect from home? Thats I I worked around it on a project before..using VPN.

[Hawk]

Nope! That is a production database and I don't want to mess around with it since my database admin skill is not that great. I just want to take it home to test against it.

Thanks [LGS]Static


McGenius, I think I found a way. If you do a backup to a bak file, it will backup everything into a backup file under \MSSQL7\Backup. All I need to do at that point is burn it on a cd and do a restore on that file.

Anyone object to that?

Thanks everyone

[McGenius]

1. Don't you have a test environment - same as production but much smaller in size ??? That's how things usually are: you develop in development environment which is entirely separated from production so you can do whatever the heck you want to do (well, almost).
2. That's exactly what I suggested in my first post.

[Hawk]

We do have a test environment or what we called it the "development" environment. However, the machine are slow and just simply crappy. Also, even if it is a test environment, I still need to know how to transfer the copy of the database over. If I am going to do that, than I rather just take it home with me so I can work on it at home and not come in on the weekend.

Thanks for your dedication to this thread McGenius!


Anyway, I am replying because I think I found something. The way how I did it was the correct way. You back the database up to a bak file and everything is store inside that bak file.

Here is a link just for anyone who ever run into this question.

http://www.databasejournal.com/featu...le.php/1439831


http://gethelp.devx.com/techtips/the...0min0501-2.asp