Red Gate forums :: View topic - backup in SINGLE_USER MODE
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

backup in SINGLE_USER MODE

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Wed Feb 22, 2012 9:26 pm    Post subject: backup in SINGLE_USER MODE Reply with quote

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:
"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"
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Thu Feb 23, 2012 5:34 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Thu Feb 23, 2012 7:23 pm    Post subject: error 880 and error 924 Reply with quote

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:
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
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Thu Feb 23, 2012 7:33 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Thu Feb 23, 2012 7:34 pm    Post subject: Found the issue Reply with quote

I think I got it.
I was adjusting the database access from a query window and then changing my connection to Master.
Code:
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.
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Thu Feb 23, 2012 7:38 pm    Post subject: SQL versions in question Reply with quote

Chris,
both servers are running the same version of SQL 2005 SP4.
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Thu Feb 23, 2012 7:47 pm    Post subject: Reply with quote

Is this the only database of many on the old server moving to the new one?
_________________
English DBA living in CANADA
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Thu Feb 23, 2012 7:53 pm    Post subject: One of many Reply with quote

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.
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Thu Feb 23, 2012 8:03 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Thu Feb 23, 2012 8:16 pm    Post subject: details Reply with quote

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.
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Thu Feb 23, 2012 8:22 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Thu Feb 23, 2012 8:37 pm    Post subject: the long and short Reply with quote

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
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Thu Feb 23, 2012 9:10 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Thu Feb 23, 2012 10:51 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group