SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More Intelligent Backup and Restore


More Intelligent Backup and Restore

Author
Message
vince.iacoboni@db.com
vince.iacoboni@db.com
SSC Eights!
SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)

Group: General Forum Members
Points: 939 Visits: 552
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




Paul Moore
Paul Moore
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 764
yes sorry the missing backslash was just a typo in the post.
Jason Crider
Jason Crider
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 2232
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
sqlhack
sqlhack
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 232
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.
sqlhack
sqlhack
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 232
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',
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search