June 4, 2014 at 3:26 pm
Hello experts,
I'm trying to restore several databases under the following conditions:
1. I have copied a full backup of each database from another server (*.bak files).
2. I want to restore each database to the new server, but the data and log paths are different.
3. I have tried the following script but am getting the error below.
Script reference (code attached as a text file below):
Error:
Msg 8114, Level 16, State 1, Line 1
Error converting data type bigint to int.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Does anyone know why this error would happen and how to fix it? I don't see bigint in the sql procedure anywhere so I'm not sure where the error is finding it.
Also, what is somewhat surprising is that there are not more examples of this kind of procedure. There are many helpful similar procedures, but they don't look simply for a folder of *.bak files - either they try to use a linked server to pull backup info from a remote msdb database, or they assume a restore of one database at a time, etc.
What I want to do is automatically restore all of the bak files in a given directory to the new server without interaction, or preferably generate that kind of script for me to review and then run as needed. I will take a stab at writing it myself if necessary, but it would be helpful, given my time frame, to find it elsewhere and learn from it.
Thanks in advance for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 4, 2014 at 3:40 pm
In your procedure, the temporary table that is created to hold the output from RESTORE FILELISTONLY creates the columns [FileID] and [BackupSizeInBytes] as an INT. It should be created as a BIGINT, (reference http://msdn.microsoft.com/en-us/library/ms173778(v=sql.105).aspx).
--Table to hold result from RESTORE FILELISTONLY. Need to
--generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles(
LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId bigint -- change to BIGINT
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes bigint -- change to BIGINT
,SourceBlockSize int
,FilegroupId int
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly int
,IsPresent int
,TDEThumbprint nvarchar(128)
)
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
June 4, 2014 at 4:43 pm
You may find this helpful:
(Just some extra code for your dev work)
Sql to get most recent full backup file for a specific database in sql server
June 4, 2014 at 9:44 pm
Here is a script to help automate your database restores.
I use it at several clients to restore anywhere from 1 to 50 databases on regular intervals.
http://jasonbrimhall.info/2014/01/14/t-sql-tuesday-050-automating-database-restores/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 12:23 am
Are you perfroming the restore on the server is having same version of SQL Server?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 5, 2014 at 9:25 am
Thanks to everyone for their replies!
Yes, I am restoring using the same version of SQL Server, just on a different server.
I will take some time to go through the suggestions and links offered.
Thanks again!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 5, 2014 at 11:59 am
SQLDCH (6/4/2014)
In your procedure, the temporary table that is created to hold the output from RESTORE FILELISTONLY creates the columns [FileID] and [BackupSizeInBytes] as an INT. It should be created as a BIGINT, (reference http://msdn.microsoft.com/en-us/library/ms173778(v=sql.105).aspx).
--Table to hold result from RESTORE FILELISTONLY. Need to--generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles(
LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId bigint -- change to BIGINT
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes bigint -- change to BIGINT
,SourceBlockSize int
,FilegroupId int
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly int
,IsPresent int
,TDEThumbprint nvarchar(128)
)
Thanks! I'm marking this response as the solution because it fixed this particular error that I was seeing in the script.
But thanks to the other respondents too! Those additional articles will be a great help to me.
Gratefully,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply