More Intelligent Backup and Restore

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Vince Iacoboni » More Intelligent Backup and Restore

45 posts, Page 5 of 5

«««1 2 3 4 5

More Intelligent Backup and Restore

Rate Topic

Display Mode

Topic Options

Author

Message

viacoboni

Posted Thursday, March 27, 2008 8:50 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:04 AM
Points: 712, Visits: 411

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

Paul Moore

Posted Thursday, March 27, 2008 9:02 AM

Grasshopper

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

Jason Crider

Posted Friday, May 23, 2008 12:39 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:08 AM
Points: 282, Visits: 2,126

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

sqlhack

Posted Monday, September 15, 2008 4:30 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:22 PM
Points: 13, Visits: 224

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

sqlhack

Posted Monday, September 15, 2008 5:01 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:22 PM
Points: 13, Visits: 224

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 »

45 posts, Page 5 of 5

«««1 2 3 4 5

Permissions