backup in SINGLE_USER MODE

epetro

I am trying to reduce the down time for a database move.
I am considering the following steps:
1. Perform scheduled FULL backup.
2. Restore FULL backup with NORECOVERY.
3. Perform DIFF backup in SINGLE_USER mode just prior to tear down.
4. Restore DIFF backup with RECOVERY.

We have applications touching the database and the easiest way to stop them from changing things at 'tear down' time, is to work in SINGLE_USER mode.

The issue I am having is finding a method of calling SQL Backup 6 while in a SINGLE_USER session. From what I have found, the stored procedure uses 2 connections and the command line uses 1. However, when I attempted to utilize the command line i still get error 880.

Here is code:

"C:\Program Files (x86)\Red Gate\SQL Backup\(LOCAL)\SQLBackupC.exe" -SQL "BACKUP DATABASE [CompressTest] TO DISK = 'L:\CompressRestores20120222\<database>_FULL.sqb' WITH INIT, THREADCOUNT = 7"

petey

Open the command prompt using a Windows account that is a SQL Server sysadmin.

epetro

I adjusted my role to sysadmin, but the error continues. I also tried using the RUN AS to modify the credentials. No dice.
I quickly toggled the database OFFLINE/ONLINE in case some phantom connection is in the way. Still no go.

When I look in activity monitor with no filter, there are no processes for my database.

I would note that there are 2 errors returned inside the command prompt.
The exact message is this:

Backing up CompressTest (full database) to:
  L:\CompressRestore20120222\CompressTest_FULL.sqb

Error 880: BACKUP DATABASE permission denied in database:  (CompressTest)
SQL error 924: Database 'CompressTest' is already open and can only have one user at a time.
SQL Backup exit code: 880
Last SQL error code: 924

ChrisAVWood

You mention this as a database move. Is that from one server to another running the same build of SQL or an upgrade to another server running a different version/build of SQL?

Are you running on a SAN?

Chris

epetro

I think I got it.
I was adjusting the database access from a query window and then changing my connection to Master.

USE [Master]

I thought this freed the single connection, but it does not. I had to 'DISCONNECT' my query session manually.
Now looking for a way to achieve this result without leaving the query window.

epetro

Chris,
both servers are running the same version of SQL 2005 SP4.

ChrisAVWood

Is this the only database of many on the old server moving to the new one?

epetro

This database is the only one that will migrate. The others need to remain online.

Due to the nature of the connection remaining open, I still have the option of running a native DIFF, but I prefer your utility.
I have a number of these projects to complete and would like to automate as much as possible. Previous solutions required detach/rename/reattach/FULLBackup/restore. This is too time consuming.

If I can code a solution from one query window, that would be preferred.

ChrisAVWood

A solution I saw at PASS I believe in 2009 or 2010 in which a server was being upgraded from SQL2000 to a SQL2008 Cluster used the Full backup being recovered with NORECOVERY followed by Differentials with NORECOVERY followed by Logs with the last one with RECOVERY. It wasn't until the last log with RECOVERY that the database was upgraded. The new server had been created and setup previously so that jobs, users etc matched what was on the old server.

Now if this was a SAN then I believe that a detach followed by a UNPRESENT (hope this is the right term) from the old server followed by a PRESENT to the new server followed by an attach could work. Again this would need the new server to have the infrastructure to run the app already there.

We now separate our system databases from our application databases so this could work.

Just a thought.

Chris

epetro

My research is really 2 fold.
We are migrating production databases to a new SAN(and your note of UNPRESENT may yield assistance) and I am trying to convert a series of databases to SQL Storage Compress with minimal downtime. If you are not familiar with SQL Storage Compress, I recommend looking at it for TEST and QA environments with large databases.

I think I will code for using a native DIFF backup. In production we run nightly FULLs and 15 min logs. If the DIFF is time consuming, I will revisit my options.

Thanks for the chatter Chris.

ChrisAVWood

Please explain SQL Storage Compress. I know its not database compression because you are not talking about SQL2008 on onwards.

Chris

epetro

SQL Storage Compress is a tool available from Redgate which lowers the disk footprint of a database (different from availablity in 2008 Ent). In our case, by an average of 82%.
I have some databases which require 730 GB in native form, but under this tool (and the preferred extensions mdfx, ndfx) will fit on 155GB.

The tool requires a background service(HyperBacSrv.exe) be installed between sqlservr.exe and the OS. Read and write operations are intercepted and translated for the compressed file using index files(extensions .index, .index2)

I don't want to trust it yet in production, but our QA and TEST departments are reporting no issues. The GUI estimates I can save over 3TB on our QA server (which will likely be used by additional restores )
Here is a link to the main page. SQL STORAGE COMPRESS

ChrisAVWood

Looks interesting. I had seen HyperBac at PASS a few years ago but had just recommended SQLBackup, which we use exclusively for SQL2005, and may still use for SQL2008R2 for split backups as a means to save on backups.

The technology looked good at that time with a compressed drive containing the SQL database.

Good luck with your tests and the move.

Chris

ChrisAVWood

From the forum posts you must be the only one using SQL Storage Compress V 6 that has issues.

Chris