How to create copy of production SQL database?
I'm looking for best practices, efficient way. I need to copy once per month my production database to development. So I'm thi开发者_运维问答nking to automate this process if possible.
The size of database around 20GB with log file (full recovery mode).
Please let me know if I need to provide more details.
Hopefully you're making regular backups of your database anyway.
So you basically just need to take the newest backup and restore it on a different server (and maybe with a different database name).
At my workplace, we are using MS SQL Server and we are doing this as well:
Our main database is backed up every evening at 9 pm (full backup).
Every day at 11 pm, a SQL Server Agent job on another server takes the newest backup from the backup folder and restores it as OurMainDatabase_Yesterday
.
Here is an example script for MS SQL Server:
ALTER DATABASE OurMainDatabase_Yesterday SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
EXEC sp_detach_db 'OurMainDatabase_Yesterday', 'true'
-- use today's backup from the main server
declare @BackupPath as nvarchar(500)
set @BackupPath = '\\MainServer\backup\OurMainDatabase\OurMainDatabase_backup_'
+ convert(varchar(10),getdate(),112) + '2100.BAK'
RESTORE DATABASE OurMainDatabase_Yesterday
FROM DISK = @BackupPath
WITH MOVE 'OurMainDatabase_Data'
TO 'F:\Data\OurMainDatabase_Yesterday_Data.mdf',
MOVE 'OurMainDatabase_Log'
TO 'G:\Logs\OurMainDatabase_Yesterday_Log.ldf',
REPLACE
ALTER DATABASE OurMainDatabase_Yesterday SET MULTI_USER
精彩评论