开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜