“Hey, can you get me a copy of the production database on the dev server?”
Aside from the fact that this request can be a really bad idea if sensitive data in a database is available on an unsecured dev server, there are scenarios where this is just fine - _IF_ the backup chain is not broken. Enter the COPY_ONLY flag available in SQL Server 2005. The sp_BackupNow stored proc uses the COPY_ONLY flag, and was pieced together from bits and pieces I found around the web, such as the timestamp used in the backup filename. Customizations followed to suit my needs. I added a self-documenting feature so that when run without parameters, some brief usage and examples are displayed. In addition, you’ll need a server share on the target server that allows the source server’s SQL Server Service user account write rights, since the proc will save the backup to a network location.
Go ahead and poke holes, or add this to your DBA toolbox.
The docs for sp_BackupNow follow, and the sp_BackupNow T-SQL is here.
Backing up production databases for restoring to development/test environments
Requirements/Context:
SQL Server 2005 (source and destination servers)
Writable Share named BackupWrite$ on Target Development Servers
sp_BackupNow stored proc on source server
Appropriate Database and Share Permissions
Description:
Source server creates a backup using the COPY_ONLY flag that is written to the target development server on the BackupWrite$ share. By using the COPY_ONLY flag(available in SQL Server 2005 and newer) the production backup chain is unaffected by the Ad hoc backup operation.
When executing the stored procedure sp_BackupNow, provide parameters for the following:
@dbname the database being backed up
@DestinationServer the target server where the backup will be saved to share BackupWrite$
@backuptype 'USERBACKUP' or 'ARCHIVE'. Backups of type ARCHIVE are saved to a separate folder to facilitate long-term retention in TSM or other backup software if desired.
@UserBackDir The first name of the user requesting the backup is used to save the file to a folder of the same name under the share BackupWrite$ – this folder must already be created.
USERBACKUP Usage Example:
EXEC sp_BackupNow @dbname='MyDatabase', @DestinationServer='DEVSERVER', @backuptype='USERBACKUP', @UserBackDir='Fred'
The above example will save a backup of database MyDatabase to directory \\DEVSERVER\BackupWrite$\Fred\
The filename of the backup contains the databasename with a timestamp appended, such as
\\DEVSERVER\BackupWrite$\Fred\MyDatabase__20090130154713.BAK
The output displayed by the above example will be similar the following:
26 percent processed.
52 percent processed.
78 percent processed.
85 percent processed.
90 percent processed.
Processed 488 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
100 percent processed.
Processed 1 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
BACKUP DATABASE successfully processed 489 pages in 0.439 seconds (9.108 MB/sec).
@dbname=MyDatabase
@DestinationServer=DEVSERVER
@backuptype=USERBACKUP
@filename=\\DEVSERVER\BackupWrite$\Fred\MyDatabase__20090130154713.BAK
@backupname=MyDatabase AdHoc Backup
ARCHIVE Usage Example:
EXEC sp_BackupNow @dbname='MyDatabase', @DestinationServer='DEVSERVER', @backuptype='ARCHIVE'
The above example will save a backup of database MyDatabase to directory \\DEVSERVER\BackupWrite$\Archive\
The filename of the backup contains the databasename with a timestamp appended, such as
\\DEVSERVER\BackupWrite$\Archive\MyDatabase__20090130155825.BAK
The output displayed by the above example will be similar the following:
26 percent processed.
52 percent processed.
78 percent processed.
85 percent processed.
90 percent processed.
Processed 488 pages for database 'MyDatabase', file 'MyDatabase' on file 1.
100 percent processed.
Processed 1 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
BACKUP DATABASE successfully processed 489 pages in 0.360 seconds (11.107 MB/sec).
@dbname=MyDatabase
@DestinationServer=DEVSERVER
@backuptype=ARCHIVE
@filename=\\DEVSERVER\BackupWrite$\Archive\MyDatabase__20090130155825.BAK
@backupname=MyDatabase AdHoc Backup
