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 Wednesday, August 30, 2006 2:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 23, 2007 2:31 PM
Points: 16, Visits: 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?

Post #305172
Posted Tuesday, February 13, 2007 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2008 7:57 AM
Points: 1, Visits: 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

Post #344539
Posted Monday, February 19, 2007 4:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 23, 2008 11:10 PM
Points: 2, Visits: 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.
 

Post #345729
Posted Tuesday, February 20, 2007 6:37 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: 2 days ago @ 2:10 PM
Points: 716, Visits: 466
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



Post #346010
Posted Thursday, March 27, 2008 5:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 2:30 AM
Points: 13, Visits: 764
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
Post #475326
Posted Thursday, March 27, 2008 6:01 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: 2 days ago @ 2:10 PM
Points: 716, Visits: 466
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



Post #475331
Posted Thursday, March 27, 2008 6:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 2:30 AM
Points: 13, Visits: 764
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
Post #475352
Posted Thursday, March 27, 2008 7:12 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: 2 days ago @ 2:10 PM
Points: 716, Visits: 466
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



Post #475372
Posted Thursday, March 27, 2008 8:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 2:30 AM
Points: 13, Visits: 764
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 #475427
Posted Thursday, March 27, 2008 8:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 2:30 AM
Points: 13, Visits: 764
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!
Post #475472
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse