backup in SINGLE_USER MODE

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

backup in SINGLE_USER MODE

Postby epetro » Wed Feb 22, 2012 9:26 pm

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:
Code: Select all
"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"
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby petey » Thu Feb 23, 2012 5:34 pm

Open the command prompt using a Windows account that is a SQL Server sysadmin.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm

error 880 and error 924

Postby epetro » Thu Feb 23, 2012 7:23 pm

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:
Code: Select all
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
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby ChrisAVWood » Thu Feb 23, 2012 7:33 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Found the issue

Postby epetro » Thu Feb 23, 2012 7:34 pm

I think I got it.
I was adjusting the database access from a query window and then changing my connection to Master.
Code: Select all
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
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

SQL versions in question

Postby epetro » Thu Feb 23, 2012 7:38 pm

Chris,
both servers are running the same version of SQL 2005 SP4.
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby ChrisAVWood » Thu Feb 23, 2012 7:47 pm

Is this the only database of many on the old server moving to the new one?
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

One of many

Postby epetro » Thu Feb 23, 2012 7:53 pm

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.
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby ChrisAVWood » Thu Feb 23, 2012 8:03 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

details

Postby epetro » Thu Feb 23, 2012 8:16 pm

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.
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby ChrisAVWood » Thu Feb 23, 2012 8:22 pm

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

Chris
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

the long and short

Postby epetro » Thu Feb 23, 2012 8:37 pm

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
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby ChrisAVWood » Thu Feb 23, 2012 9:10 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby ChrisAVWood » Thu Feb 23, 2012 10:51 pm

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

Chris
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 2 guests