Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

More Intelligent Backup and Restore Expand / Collapse
Author
Message
Posted Thursday, March 27, 2008 8:50 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 22, 2014 6:34 AM
Points: 716, Visits: 463
Did you mean for the first backslash to be doubled? Both your example and the error produced show a single leading backslash.

{UserDbs} will move through the user databases on the DESTINATION server. If they aren't there yet on the destination server, you will need to specify the names directly. Since they are new databases, they will be created on the destination and the files placed in the default data and log locations defined on the destination server.

An alternative is to create empty databases on the destination with the filenames in the locations you wish. Then you could use {UserDbs} to cycle through them.

Hope that helps.

Vince

Paul Moore (3/27/2008)
But it doesn't restore anything? as the backups are for databases that do not exist on the server, they only exist on the old server?

use master
EXEC sp_ABRestoreDb '{UserDbs}', '\ewcpnt12\restore\Databases\&\&_db_*'
GO

outputs this

Server: Msg 50000, Level 16, State 1, Procedure sp_ABRestoreDb, Line 341
Filespec \ewcpnt12\restore\Databases\AeroConf\AeroConf_db_* did not match any files

aeroconf is the only user db that exists on the new server.

This was my point - how does it know it is a userdb from a .bak file?

sorry if I am a pain in the behind - very grateful for your help



Post #475476
Posted Thursday, March 27, 2008 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 2:30 AM
Points: 13, Visits: 764
yes sorry the missing backslash was just a typo in the post.

Post #475489
Posted Friday, May 23, 2008 12:39 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:41 AM
Points: 282, Visits: 2,210
Anyone get anything like this? It still restores but it fails on this part. I've tried CURSOR READ_ONLY FOR but it didn't help.

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:A cursor with the name 'orphanuser_cur' does not exist. (Microsoft OLE DB Provider for SQL Server (80040e14): A cursor with the name 'orphanuser_cur' does not exist.) (Microsoft OLE DB Provider for SQL Server (80040e14): A cursor with the name 'orphanuser_cur' does not exist.) (Microsoft OLE DB Provider for SQL Server (80040e14): A cursor with the name 'orphanuser_cur' does not exist.) (Microsoft OLE DB Provider for SQL Server (80040e14): Could not complete cursor operation because the set options have changed since the cursor was declared.) (Microsoft OLE DB Provider for SQL Server (80040e14): Database database1 restored in 08:40) (Microsoft OLE DB Provider for SQL Server (80040e14): RESTORE DATABASE successfully processed 1377268 pages in 518.346 seconds (21.766 MB/sec).) (Microsoft OLE DB Provider for SQL Server (0): RESTORE DATABASE database1 FROM DISK='F:\MSSQL\BACKUP\database1_db_200805221910.BAK' WITH STAT


MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Post #506102
Posted Monday, September 15, 2008 4:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, August 17, 2013 4:41 PM
Points: 14, Visits: 230
This is a great script. It's actually too smart for me and what I'm trying to do. I want to modify the restore script so you can restore the database with a different name (and object owners, although I can do this with another script). I have a scenario where I need the same database restored twice in the same SQL instance, once with it's original name for DR purposes and once with a different name for reporting purposes. I modded it to rename after it's done but it renames the existing database and I want to keep it. So I did away with that concept and created a @NewName parameter and after a certain point I set the @database = @NewName but it fails because the physical files already exist, so now I'm going down the path of trying to make the new physical files have the @NewName. Any help would be appreciated.
Post #569864
Posted Monday, September 15, 2008 5:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, August 17, 2013 4:41 PM
Points: 14, Visits: 230
I added the following highlighted parts to make this work. It probably could use some cleanup but it's working so far. I did get some kind of regexquery() file not found error the first time I ran it, even though it created the database. But the second time I ran it there were no errors. I think maybe it just throws that error because the database didn't exist.

--Declared This
@NewName varchar(255) = NULL) --New Database Name

....


INSERT #FileListOutput
EXEC (@Sql)
END


-- Rename Database to New Name
SET @Database = @NewName


-- Does the database to restore exist and is it online? Then read the file structure...
IF convert(varchar(255), DATABASEPROPERTYEX(@Database, 'Status')) LIKE '%ONLINE%'
BEGIN
-- Get the file list on the existing database we will restore to
SELECT @Sql = 'USE ' + @Database + ' EXEC sp_helpfile',
Post #569868
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse