Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
R
Constraint Violating Yak Guru
328 Posts |
 Posted - 2010-09-01 : 05:33:42
|
I need to create a snapshot of my live database, and copy it to a development machine on a different network/server. I have been advised to use the Backup/Restore method with COPY ONLY so that my live database can stay online. Before I start figuring out the code for this, could I please ask for a quick sanity check on the code? I need to know whether I'm approaching this correctly or not. All the actions ideally need to be within a stored procedure so that I can execute them from an ASP.NET application... 1. Create a new backup file 2. Execute backup of live database WITH COPY ONLY to file created in step 1 3. Delete the backup file within SQL (to release it from SS's control File should still hopefully be present within operating system) 4. Download backup file to development machine and restore 5. Delete backup file from live server operating system |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-09-01 : 06:24:28
|
| Step 1 and 2 can be done in one go and I don't really understand the point of number 3, can you please elaborate? This is basically all you need:BACKUP DATABASE MyDB TO DISK = 'c:\folder\MyDB_20100901.bak' WITH CHECKSUM, COPY_ONLYThen copy this file to your dev-server and restore it there.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-09-01 : 09:48:56
|
| HiStep 3 is to try and ensure that the .BAK can be downloaded without any errors (e.g. file is in use etc). Also, it was to ensure that next time I run the code to create the backup, it isn't appended to the backup file.For security, I wanted to completely remove all traces of the backup file after it was copied to my destination machine.Is there a better way??Thanks! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-01 : 10:18:01
|
| WITH INIT will cause it to overwrite previous backup, rather than append to it. |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-09-01 : 11:15:07
|
| Thanks Russell. Is it okay to use INIT with COPY ONLY? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-01 : 11:33:39
|
| Welcome. Yes, it's fine to do that |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-09-01 : 11:55:38
|
| I was just reading BOL for the BACKUP command and saw that there were two PASSWORD options for the command (backup and media passwords). These however are being removed in a future version because they are not secure. Does SS2005 have any methods to encrypt a backup file at all? |
|
|
|
|
|
|
Creating a database snapshot, is this method okay?