More Intelligent Backup and Restore

Question

More Intelligent Backup and Restore

Viewing 15 posts - 31 through 44 (of 44 total)

You must be logged in to reply to this topic. Login to reply

Luis Cabrera-257608 Mr or Mrs. 500 Points: 526 More actions · Answer 1

Vince,

I tried to send you an email as you requested but it came back undelivered. I am sure I have either your first name or last name wrong - or maybe both

If you don't mind, can you send it to luis @ kreko dot com

Thanks in advance.

By the way, what kind of issues should I expect with this update, if Any?

jo woods Newbie Points: 7 More actions · Answer 2

Hi Vince,

I've just used your 'sp_ABBackupRestore' facility to copy a live dotnetnuke website and restore it on my local server and it worked perfectly!

Since then I've tried the same procedures with another ddn website and when I try to run the EXEC sp_ABRestoreDb command i get the following error:

Server: Msg 3205, Level 16, State 2, Line 1

Too many backup devices specified for backup or restore; only 64 are allowed.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

Server: Msg 50000, Level 16, State 1, Procedure sp_ABRestoreDb, Line 457

Couldn't restore database A3250233 from file C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\A3250233.bak since the number/type of logical devices do not match.

I've tried the whole process a few times and just keep getting stuck at this point. I'm running SQL Server 2000 on Windows Server 2003.

Any ideas would be gratefully received!

thanks alot,

Jo

Herman Hurtado SSC Rookie Points: 46 More actions · Answer 3

Hi Luis,

I am also getting an errror similar to yours when restoring after moving from SQL2000 to 2005 (see below). Did you resolve this?

RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'

Msg 22001, Level 1, State 1

Msg 213, Level 16, State 7, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 4

Herman and Luis,

Sorry guys, I'm a little busy at work and haven't been able to support the utility as I'd like to. I have an updated copy I can send to you. Please email me at vince.iacoboni@db.com.

Vince

Paul Moore Valued Member Points: 73 More actions · Answer 5

Hi Vince I am brand new to this topic, I know you have been busy from reading the posts so I hope you can help with what is hopefully an easy question for you.

I tried the restore procedure as a test for an upcoming server move. Am I right in thinking that it will only restore by what has been backed up on the server and is in the backup history. I tried moving a backup.bak file from another server into the same folder and it didn't pick it up.

I really want to use it to move alll user databases of an old server onto a new server, using the WITH MOVE to clause to end up with the data and logfiles in a new (server default) drive\folder.

Thanks

Paul

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 6

Paul,

Backup History doesn't affect things at all, which sounds like what you want. I frequently use the routines to restore production to qa or development environments.

I'm not sure what you mean by "I tried moving a backup.bak file from another server into the same folder and it didn't pick it up." If you fully specify the filename to use, it should find the file in question.

Can you post the command you used to do the restore?

Vince

Paul Moore Valued Member Points: 73 More actions · Answer 7

Thanks for the fast response Vince.

I am hoping to be be able to use the '{UserDBs}' option to create new databases from final backups so i think I used

USE master

EXEC sp_ABRestoreDb '{UserDbs}

With the relevant database backup files myDatabase.bak already in the default backup folder

ie \\SLQSERVER\e$\Databases\MSSQL\BACKUP\myDatabase_db.BAK

This did not work for the imported .bak files but did for one db that I had already backed up.

I have just used with some success

EXEC sp_ABRestoreDb 'myDatabase', '\\remoteserver\backups\Databases\myDatabase\mydatabase_db_*'

but this would be a real pain to do for our 60+ databases(yes I did say 60).

Apart from and issue regarding the logins which I hope to fix by using sp_help_revlogin before the restores take place.

Apoligies if this is unclear - my head is spinning.

Thanks

Paul

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 8

I would suggest the following:

EXEC sp_ABRestoreDb '{UserDbs}', '\\remoteserver\backups\Databases\&\&_db_*'

& in the filename parameter is replaced by the current database name. You can use it as many times as you need to in the filename parameter.

If you really want to leave off the filename parameter altogether, you need to ensure the following registry key is set to your backup location (e.g. \\remoteserver\backups\Databases\):

HKLM/SOFTWARE/Microsoft/Microsoft SQL Server/MSSQLSERVER/BackupDirectory

This key is for default instance, SQL 2000. Search for the BackupDirectory key in the registry if you don't find it there.

Vince

Paul Moore (3/27/2008)
Thanks for the fast response Vince.
I am hoping to be be able to use the '{UserDBs}' option to create new databases from final backups so i think I used
USE master
EXEC sp_ABRestoreDb '{UserDbs}
With the relevant database backup files myDatabase.bak already in the default backup folder
ie \\SLQSERVER\e$\Databases\MSSQL\BACKUP\myDatabase_db.BAK
This did not work for the imported .bak files but did for one db that I had already backed up.
I have just used with some success
EXEC sp_ABRestoreDb 'myDatabase', '\\remoteserver\backups\Databases\myDatabase\mydatabase_db_*'
but this would be a real pain to do for our 60+ databases(yes I did say 60).
Apart from and issue regarding the logins which I hope to fix by using sp_help_revlogin before the restores take place.
Apoligies if this is unclear - my head is spinning.
Thanks
Paul

Paul Moore Valued Member Points: 73 More actions · Answer 9

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 Valued Member Points: 73 More actions · Answer 10

okay a breakthrough for you freindly local pretend dba

I used

EXEC sp_ABRestoreDb 'PHPBB_Test,TESTBERAN,AEROCONF,PROCEDURESTEST', '\ewcpnt12\restore\Databases\&\&_db_*'

so it creates new db's using the correct name and finds corresponding .bak file in the folder specified.

that is a good result.

I did get a few errors, but they are mostly to do with the reg key for the default backup location I guess

Msg 22001, Level 1, State 22001

RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'

and also

Server: Msg 446, Level 16, State 9, Procedure sp_abFixUserLogins, Line 21

but in essence it works and will do what I need, just means I need to use the comma separted list for database names.

You are the SQL king thank you!

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 11

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 Valued Member Points: 73 More actions · Answer 12

yes sorry the missing backslash was just a typo in the post.

Jason Crider SSCertifiable Points: 6019 More actions · Answer 13

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 SSC Veteran Points: 216 More actions · Answer 14

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 SSC Veteran Points: 216 More actions · Answer 15

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',