Tuesday, April 07, 2009

Ad Hoc Backups Without Breaking Backup Chain in SQL Server 2005

“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

 

Wednesday, April 01, 2009

Pasting RTF to HTML in blogs using Insert Formatted Clipboard plugin for Windows Live Writer

 

I’ve played with a few tools to post code to my blog and for sharing documentation.  I’m mostly interested in pasting T-SQL from SQL Server Management Studio(pasting RTF), but I also paste PowerShell scripts and batch files.

Now I’m trying the Insert Formatted Clipboard plugin from Noah Coad along with Windows Live Writer – both new to me with this post.  There was a painful install process(see comments by ITSOLVE) under Vista that involved renaming an .MSI file, executing msiexec from the command prompt, and manually copying a DLL file.  In XP I had no problems with the install.

On to usage.  I copied the T-SQL from SSMS

0054

to the clipboard, and then, in Windows Live Writer clicked
Insert >> Formatted Clipboard…
0055
Here’s what pasted:

SELECT '-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate()

Ahh.  Cleanly converted HTML from an RTF paste – with inline styles.  Hallelujah!  You may notice that the pasted text does not contain the proper monospaced typeface.  I’m ok with that, as I would normally wrap that text in a border with a monospaced font.  Read on.

Sometimes I just want to wrap the code with a border following the styles created at Format My Source Code for Blogging.

<pre style="font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%"><code> 



</code></pre>



So, using the above html I can paste inside the <code> </code> tag and it comes out like so:

SELECT '-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate() 

Nice, eh?


Additional notes:


If I’m careful within Windows Live Writer I can highlight & copy the text that was created from the Insert >> Formatted Clipboard… step, then double-click on the newly created empty border and paste right into the border style without using the Source Tab.


The other Windows Live Writer plugin used above to display the <pre> style from Format My Source Code for Blogging was Code Snippet by Leo Vildosola.

If you’d like to dive even further into RTF, HTML, and related macros for Visual Studio, see Steve Nyholm’s blog.