Dear All,
I had created the backup for the exisiting datbase by writing the schedule which triggers at the particular time on a day. The backup format i had used is .bak. Now i want to restore these backup files into another database (fake database). When i am trying to restore the backup data, an error stating 'The backup set holds the backup of other than exsting <Database Name> database' is displayed and the restore operation is terminating abnormally. kindly give the excat soultion for this problem..
Regards,Try to rstore with the option "Force restore over existing database" (Enterprise Manager - All Tasks - Restore Database (option panel)
Originally posted by pardhu
Dear All,
I had created the backup for the exisiting datbase by writing the schedule which triggers at the particular time on a day. The backup format i had used is .bak. Now i want to restore these backup files into another database (fake database). When i am trying to restore the backup data, an error stating 'The backup set holds the backup of other than exsting <Database Name> database' is displayed and the restore operation is terminating abnormally. kindly give the excat soultion for this problem..
Regards,|||Hai dbadelphes,
I tried even by checking that option.
Originally posted by dbadelphes
Try to rstore with the option "Force restore over existing database" (Enterprise Manager - All Tasks - Restore Database (option panel)|||Make sure the database name is identical between these 2 servers.
BTW what command you're using to restore?|||Hai,
I am trying thru Enterprise Manager Wizard. I made the database names identical even.
Regards,
Originally posted by Satya
Make sure the database name is identical between these 2 servers.
BTW what command you're using to restore?|||From QA have you tried using WITH MOVE (no existing db1), or REPLACE (when you have a new db1)?
Try making sure that no db1 currently exists, then something like:
RESTORE DATABASE db1 FROM DISK='c:\db.doc'
WITH MOVE 'db1_Data' TO 'R:\Microsoft SQL Server\MSSQL\data\db1.mdf',
MOVE 'db1_Log' TO 'q:\Microsoft SQL Server\MSSQL\data\db1.ldf'
Refer to BOL for more information on BACKUP statement.sql
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
Restoring multiple transaction logs on another server
Dear knowledgeable people,
Is there a simple way to restore the latest full backup, plus all the transaction
log backups on to another server. I know you can on the same server, 'cause
all the backup history is there in MSDB. But how many people back msdb up
every 15 minutes?
Consider the case:
Full Backup of Database @. 0300 each day
T.log backups every 15 minutes
Crash at 19:00 takes out entire server, but not the backup files 'cause
you cunningly configured File Replication Service to copy them elsewhere.
When restoring, do you really have to type 60+ "restore log ... with no recovery"
commands (without typos) to get the database back?
i.e Should I polish up the following script ( error handling / differential backups ...)
and/or write my own .exe to intelligently parse a backup directory, or am I
totally missing the point? ( Me programmer not normally sysadmin ).
Regards
AJ
CREATE procedure spAJGRestoreAsFarAsYouCan
(
@.Path varchar(200) = 'c:\mssql\backup\',
@.DBName varchar(30) = 'ALC',
@.DoItOrJustPrint char(1) = 'Y'
)
as
set nocount on
-- Get list of files in directory
declare @.SqlCmd varchar(300)
set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
create table #DirList
(
RowID int identity,
FileOnly varchar(400)
)
insert #DirList ( FileOnly )
exec master..xp_cmdshell @.SqlCmd
-- Last full backup ( .bak )
declare @.FullBackup varchar(300)
declare @.CurrentID int
Select top 1
@.CurrentID = RowID,
@.FullBackup = @.Path + FileOnly
from #DirList
where patindex( '%.bak%', FileOnly) > 0
order by RowID desc
-- if @.FullBackup is NULL return -1
Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' + @.FullBackup + ''' with norecovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
Declare @.TransLog varchar(300)
Set @.TransLog = 'dummy'
While @.TransLog is Not NULL
Begin
Set @.TransLog = NULL
Select top 1
@.TransLog = @.Path + FileOnly,
@.CurrentID = RowID
from #DirList
where patindex( '%.trn%', FileOnly) > 0
and RowID > @.CurrentID
order by RowID
if @.TransLog is not NULL
Begin
Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' + @.TransLog + ''' with norecovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
End
End
Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
drop table #DirList
return (0)I'd just polish the script. I'd like to point out the following, though:
* You may want to change dir /t:c to dir /t:w
* If your directory may have files other than the database/log backup files,
you may want to check
each file with RESTORE HEADERONLY to make sure that it's a backup file
* Instead of relying on the file date/time to determine the order of the
restore, you can also use
RESTORE HEADERONLY to make sure that the order is correct.
* Using RESTORE HEADERONLY, you can even determine whether you have gaps in
your log
backups by inspecting the LSN's (log sequence numbers)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Andrew John" <aj@.DELETEmistrose.com> wrote in message
news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> Dear knowledgeable people,
> Is there a simple way to restore the latest full backup, plus all the
transaction
> log backups on to another server. I know you can on the same server,
'cause
> all the backup history is there in MSDB. But how many people back msdb up
> every 15 minutes?
> Consider the case:
> Full Backup of Database @. 0300 each day
> T.log backups every 15 minutes
> Crash at 19:00 takes out entire server, but not the backup files 'cause
> you cunningly configured File Replication Service to copy them elsewhere.
> When restoring, do you really have to type 60+ "restore log ... with no
recovery"
> commands (without typos) to get the database back?
> i.e Should I polish up the following script ( error handling /
differential backups ...)
> and/or write my own .exe to intelligently parse a backup directory, or am
I
> totally missing the point? ( Me programmer not normally sysadmin ).
> Regards
> AJ
> CREATE procedure spAJGRestoreAsFarAsYouCan
> (
> @.Path varchar(200) = 'c:\mssql\backup\',
> @.DBName varchar(30) = 'ALC',
> @.DoItOrJustPrint char(1) = 'Y'
> )
> as
> set nocount on
> -- Get list of files in directory
> declare @.SqlCmd varchar(300)
> set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> create table #DirList
> (
> RowID int identity,
> FileOnly varchar(400)
> )
> insert #DirList ( FileOnly )
> exec master..xp_cmdshell @.SqlCmd
> -- Last full backup ( .bak )
> declare @.FullBackup varchar(300)
> declare @.CurrentID int
> Select top 1
> @.CurrentID = RowID,
> @.FullBackup = @.Path + FileOnly
> from #DirList
> where patindex( '%.bak%', FileOnly) > 0
> order by RowID desc
> -- if @.FullBackup is NULL return -1
> Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
@.FullBackup + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> Declare @.TransLog varchar(300)
> Set @.TransLog = 'dummy'
> While @.TransLog is Not NULL
> Begin
> Set @.TransLog = NULL
> Select top 1
> @.TransLog = @.Path + FileOnly,
> @.CurrentID = RowID
> from #DirList
> where patindex( '%.trn%', FileOnly) > 0
> and RowID > @.CurrentID
> order by RowID
> if @.TransLog is not NULL
> Begin
> Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
@.TransLog + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> End
> End
>
> Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> drop table #DirList
> return (0)
>|||Andrew
Yeh, you need to be prepared for such situations.
I would write stored procedure that accept tree parameters
(dbname,from_hour,to_hour)
You need to keep transaction backups with appropriatte file name like
my_log1200 ,next my_log1215.
"Andrew John" <aj@.DELETEmistrose.com> wrote in message
news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> Dear knowledgeable people,
> Is there a simple way to restore the latest full backup, plus all the
transaction
> log backups on to another server. I know you can on the same server,
'cause
> all the backup history is there in MSDB. But how many people back msdb up
> every 15 minutes?
> Consider the case:
> Full Backup of Database @. 0300 each day
> T.log backups every 15 minutes
> Crash at 19:00 takes out entire server, but not the backup files 'cause
> you cunningly configured File Replication Service to copy them elsewhere.
> When restoring, do you really have to type 60+ "restore log ... with no
recovery"
> commands (without typos) to get the database back?
> i.e Should I polish up the following script ( error handling /
differential backups ...)
> and/or write my own .exe to intelligently parse a backup directory, or am
I
> totally missing the point? ( Me programmer not normally sysadmin ).
> Regards
> AJ
> CREATE procedure spAJGRestoreAsFarAsYouCan
> (
> @.Path varchar(200) = 'c:\mssql\backup\',
> @.DBName varchar(30) = 'ALC',
> @.DoItOrJustPrint char(1) = 'Y'
> )
> as
> set nocount on
> -- Get list of files in directory
> declare @.SqlCmd varchar(300)
> set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> create table #DirList
> (
> RowID int identity,
> FileOnly varchar(400)
> )
> insert #DirList ( FileOnly )
> exec master..xp_cmdshell @.SqlCmd
> -- Last full backup ( .bak )
> declare @.FullBackup varchar(300)
> declare @.CurrentID int
> Select top 1
> @.CurrentID = RowID,
> @.FullBackup = @.Path + FileOnly
> from #DirList
> where patindex( '%.bak%', FileOnly) > 0
> order by RowID desc
> -- if @.FullBackup is NULL return -1
> Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
@.FullBackup + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> Declare @.TransLog varchar(300)
> Set @.TransLog = 'dummy'
> While @.TransLog is Not NULL
> Begin
> Set @.TransLog = NULL
> Select top 1
> @.TransLog = @.Path + FileOnly,
> @.CurrentID = RowID
> from #DirList
> where patindex( '%.trn%', FileOnly) > 0
> and RowID > @.CurrentID
> order by RowID
> if @.TransLog is not NULL
> Begin
> Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
@.TransLog + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> End
> End
>
> Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> drop table #DirList
> return (0)
>|||Thank you all for your comments.
I'm glad to see I wasn't completely off track.
I am a bit surprised with t:c vs t:w. Wouldn't that
cause issues if the full backup took more than 15minutes ?
( It wont in my case - but I did think of that ).
What I am trying to write is something that can be run
in the wee hours of the morning, by some overstressed
service technician, without a microscopic examination of
file names and a touch typing course.
Pretty please Mr Microsoft - It wouldn't be too hard to put
some MSDB data into the T.Log backups would it?
Thanks Again
AJ
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message news:uygo6aAgDHA.1648@.TK2MSFTNGP09.phx.gbl...
> I'd just polish the script. I'd like to point out the following, though:
> * You may want to change dir /t:c to dir /t:w
> * If your directory may have files other than the database/log backup files,
> you may want to check
> each file with RESTORE HEADERONLY to make sure that it's a backup file
> * Instead of relying on the file date/time to determine the order of the
> restore, you can also use
> RESTORE HEADERONLY to make sure that the order is correct.
> * Using RESTORE HEADERONLY, you can even determine whether you have gaps in
> your log
> backups by inspecting the LSN's (log sequence numbers)
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > Dear knowledgeable people,
> >
> > Is there a simple way to restore the latest full backup, plus all the
> transaction
> > log backups on to another server. I know you can on the same server,
> 'cause
> > all the backup history is there in MSDB. But how many people back msdb up
> > every 15 minutes?
> >
> > Consider the case:
> >
> > Full Backup of Database @. 0300 each day
> > T.log backups every 15 minutes
> > Crash at 19:00 takes out entire server, but not the backup files 'cause
> > you cunningly configured File Replication Service to copy them elsewhere.
> >
> > When restoring, do you really have to type 60+ "restore log ... with no
> recovery"
> > commands (without typos) to get the database back?
> >
> > i.e Should I polish up the following script ( error handling /
> differential backups ...)
> > and/or write my own .exe to intelligently parse a backup directory, or am
> I
> > totally missing the point? ( Me programmer not normally sysadmin ).
> >
> > Regards
> > AJ
> >
> > CREATE procedure spAJGRestoreAsFarAsYouCan
> > (
> > @.Path varchar(200) = 'c:\mssql\backup\',
> > @.DBName varchar(30) = 'ALC',
> > @.DoItOrJustPrint char(1) = 'Y'
> > )
> > as
> >
> > set nocount on
> > -- Get list of files in directory
> > declare @.SqlCmd varchar(300)
> > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> >
> > create table #DirList
> > (
> > RowID int identity,
> > FileOnly varchar(400)
> > )
> >
> > insert #DirList ( FileOnly )
> > exec master..xp_cmdshell @.SqlCmd
> >
> > -- Last full backup ( .bak )
> > declare @.FullBackup varchar(300)
> > declare @.CurrentID int
> >
> > Select top 1
> > @.CurrentID = RowID,
> > @.FullBackup = @.Path + FileOnly
> > from #DirList
> > where patindex( '%.bak%', FileOnly) > 0
> > order by RowID desc
> >
> > -- if @.FullBackup is NULL return -1
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
> @.FullBackup + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > Declare @.TransLog varchar(300)
> > Set @.TransLog = 'dummy'
> > While @.TransLog is Not NULL
> > Begin
> >
> > Set @.TransLog = NULL
> > Select top 1
> > @.TransLog = @.Path + FileOnly,
> > @.CurrentID = RowID
> > from #DirList
> > where patindex( '%.trn%', FileOnly) > 0
> > and RowID > @.CurrentID
> > order by RowID
> >
> > if @.TransLog is not NULL
> > Begin
> > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
> @.TransLog + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> > End
> > End
> >
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > drop table #DirList
> >
> > return (0)
> >
> >
>|||> I always like using backup / restore for version control as it
> controls dev data versions along with schema objects etc.
IMHO, DDL (and selected DML) belongs in a version control system. This
facilitates new application installs as well as upgrades. Test data can
also be stored but its not as important to keep that under version
control.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eWKx7SAgDHA.1748@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew.
> I regularly need to do this on software dev projects for testing,
release
> etc.. I always like using backup / restore for version control as it
> controls dev data versions along with schema objects etc. So, it
sounds like
> my needs are similar to yours. (c:
> My approach is to use similar scripts / app logic, but there's a fair
bit of
> complexity as there's a full app with additional features not relevant
to
> your question, so I can't easily post scripts as such.
> The feature that I use though is to restore file headers, extract LSNs
&
> apply in order, which works whether the files are .bak, .diff or .trn.
I've
> found that use of .diffs mitigate your "60+ retore log statements"
problem
> significantly. Perhaps you could adjust your script to include .diff,
> restore headers & retore log / diff / db in LSN order?
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > Dear knowledgeable people,
> >
> > Is there a simple way to restore the latest full backup, plus all
the
> transaction
> > log backups on to another server. I know you can on the same
server,
> 'cause
> > all the backup history is there in MSDB. But how many people back
msdb up
> > every 15 minutes?
> >
> > Consider the case:
> >
> > Full Backup of Database @. 0300 each day
> > T.log backups every 15 minutes
> > Crash at 19:00 takes out entire server, but not the backup files
'cause
> > you cunningly configured File Replication Service to copy them
elsewhere.
> >
> > When restoring, do you really have to type 60+ "restore log ...
with no
> recovery"
> > commands (without typos) to get the database back?
> >
> > i.e Should I polish up the following script ( error handling /
> differential backups ...)
> > and/or write my own .exe to intelligently parse a backup directory,
or am
> I
> > totally missing the point? ( Me programmer not normally sysadmin ).
> >
> > Regards
> > AJ
> >
> > CREATE procedure spAJGRestoreAsFarAsYouCan
> > (
> > @.Path varchar(200) = 'c:\mssql\backup\',
> > @.DBName varchar(30) = 'ALC',
> > @.DoItOrJustPrint char(1) = 'Y'
> > )
> > as
> >
> > set nocount on
> > -- Get list of files in directory
> > declare @.SqlCmd varchar(300)
> > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> >
> > create table #DirList
> > (
> > RowID int identity,
> > FileOnly varchar(400)
> > )
> >
> > insert #DirList ( FileOnly )
> > exec master..xp_cmdshell @.SqlCmd
> >
> > -- Last full backup ( .bak )
> > declare @.FullBackup varchar(300)
> > declare @.CurrentID int
> >
> > Select top 1
> > @.CurrentID = RowID,
> > @.FullBackup = @.Path + FileOnly
> > from #DirList
> > where patindex( '%.bak%', FileOnly) > 0
> > order by RowID desc
> >
> > -- if @.FullBackup is NULL return -1
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
> @.FullBackup + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > Declare @.TransLog varchar(300)
> > Set @.TransLog = 'dummy'
> > While @.TransLog is Not NULL
> > Begin
> >
> > Set @.TransLog = NULL
> > Select top 1
> > @.TransLog = @.Path + FileOnly,
> > @.CurrentID = RowID
> > from #DirList
> > where patindex( '%.trn%', FileOnly) > 0
> > and RowID > @.CurrentID
> > order by RowID
> >
> > if @.TransLog is not NULL
> > Begin
> > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
> @.TransLog + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> > End
> > End
> >
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > drop table #DirList
> >
> > return (0)
> >
> >
>|||> I also advocate using VSS, this is really just supplemental dev team
> procedure.
I see what you mean now. Backup / restore certainly has its place for
taking snapshots to aid iterative testing. I'm not a big fan of VSS but
there are other better, albeit much more expensive, source control
products out there.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e3v3LvEgDHA.4024@.TK2MSFTNGP11.phx.gbl...
> Experience has made me (slightly) weary of VSS which is enough to
warrant
> extra protection! So, on my projects, I have been supplementing
version
> control of database objects by implementing fully logged backups (with
> transaction log marks in sql2k or timestamps in earlier versions).
With this
> approach you get bullet-proof backup & recoverability of all db source
/
> objects and data thrown in as well. This approach works on any
platform.
> Assuming the project doesn't have massive test / dev data
requirements, it's
> easy to store these on tape or ide media or just mix into a business's
> normal backup procedures.
> Being able to easily restore dev / test environments to marks or
specific
> points in time is a powerful technique for regression testing. As a
side
> benefit you also get early indications of capacity requirements for
backup /
> restore which a data tier architect will find useful when documenting
> capacity expectations.
> I also advocate using VSS, this is really just supplemental dev team
> procedure.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ez8u7kEgDHA.3896@.tk2msftngp13.phx.gbl...
> > > I always like using backup / restore for version control as it
> > > controls dev data versions along with schema objects etc.
> >
> > IMHO, DDL (and selected DML) belongs in a version control system.
This
> > facilitates new application installs as well as upgrades. Test data
can
> > also be stored but its not as important to keep that under version
> > control.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:eWKx7SAgDHA.1748@.TK2MSFTNGP10.phx.gbl...
> > > Hi Andrew.
> > >
> > > I regularly need to do this on software dev projects for testing,
> > release
> > > etc.. I always like using backup / restore for version control as
it
> > > controls dev data versions along with schema objects etc. So, it
> > sounds like
> > > my needs are similar to yours. (c:
> > >
> > > My approach is to use similar scripts / app logic, but there's a
fair
> > bit of
> > > complexity as there's a full app with additional features not
relevant
> > to
> > > your question, so I can't easily post scripts as such.
> > >
> > > The feature that I use though is to restore file headers, extract
LSNs
> > &
> > > apply in order, which works whether the files are .bak, .diff or
.trn.
> > I've
> > > found that use of .diffs mitigate your "60+ retore log statements"
> > problem
> > > significantly. Perhaps you could adjust your script to include
.diff,
> > > restore headers & retore log / diff / db in LSN order?
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> > > news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > Dear knowledgeable people,
> > > >
> > > > Is there a simple way to restore the latest full backup, plus
all
> > the
> > > transaction
> > > > log backups on to another server. I know you can on the same
> > server,
> > > 'cause
> > > > all the backup history is there in MSDB. But how many people
back
> > msdb up
> > > > every 15 minutes?
> > > >
> > > > Consider the case:
> > > >
> > > > Full Backup of Database @. 0300 each day
> > > > T.log backups every 15 minutes
> > > > Crash at 19:00 takes out entire server, but not the backup files
> > 'cause
> > > > you cunningly configured File Replication Service to copy them
> > elsewhere.
> > > >
> > > > When restoring, do you really have to type 60+ "restore log ...
> > with no
> > > recovery"
> > > > commands (without typos) to get the database back?
> > > >
> > > > i.e Should I polish up the following script ( error handling /
> > > differential backups ...)
> > > > and/or write my own .exe to intelligently parse a backup
directory,
> > or am
> > > I
> > > > totally missing the point? ( Me programmer not normally
sysadmin ).
> > > >
> > > > Regards
> > > > AJ
> > > >
> > > > CREATE procedure spAJGRestoreAsFarAsYouCan
> > > > (
> > > > @.Path varchar(200) = 'c:\mssql\backup\',
> > > > @.DBName varchar(30) = 'ALC',
> > > > @.DoItOrJustPrint char(1) = 'Y'
> > > > )
> > > > as
> > > >
> > > > set nocount on
> > > > -- Get list of files in directory
> > > > declare @.SqlCmd varchar(300)
> > > > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> > > >
> > > > create table #DirList
> > > > (
> > > > RowID int identity,
> > > > FileOnly varchar(400)
> > > > )
> > > >
> > > > insert #DirList ( FileOnly )
> > > > exec master..xp_cmdshell @.SqlCmd
> > > >
> > > > -- Last full backup ( .bak )
> > > > declare @.FullBackup varchar(300)
> > > > declare @.CurrentID int
> > > >
> > > > Select top 1
> > > > @.CurrentID = RowID,
> > > > @.FullBackup = @.Path + FileOnly
> > > > from #DirList
> > > > where patindex( '%.bak%', FileOnly) > 0
> > > > order by RowID desc
> > > >
> > > > -- if @.FullBackup is NULL return -1
> > > >
> > > > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk =''' +
> > > @.FullBackup + ''' with norecovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > >
> > > > Declare @.TransLog varchar(300)
> > > > Set @.TransLog = 'dummy'
> > > > While @.TransLog is Not NULL
> > > > Begin
> > > >
> > > > Set @.TransLog = NULL
> > > > Select top 1
> > > > @.TransLog = @.Path + FileOnly,
> > > > @.CurrentID = RowID
> > > > from #DirList
> > > > where patindex( '%.trn%', FileOnly) > 0
> > > > and RowID > @.CurrentID
> > > > order by RowID
> > > >
> > > > if @.TransLog is not NULL
> > > > Begin
> > > > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = '''
+
> > > @.TransLog + ''' with norecovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > > End
> > > > End
> > > >
> > > >
> > > > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > >
> > > > drop table #DirList
> > > >
> > > > return (0)
> > > >
> > > >
> > >
> > >
> >
> >
>
Is there a simple way to restore the latest full backup, plus all the transaction
log backups on to another server. I know you can on the same server, 'cause
all the backup history is there in MSDB. But how many people back msdb up
every 15 minutes?
Consider the case:
Full Backup of Database @. 0300 each day
T.log backups every 15 minutes
Crash at 19:00 takes out entire server, but not the backup files 'cause
you cunningly configured File Replication Service to copy them elsewhere.
When restoring, do you really have to type 60+ "restore log ... with no recovery"
commands (without typos) to get the database back?
i.e Should I polish up the following script ( error handling / differential backups ...)
and/or write my own .exe to intelligently parse a backup directory, or am I
totally missing the point? ( Me programmer not normally sysadmin ).
Regards
AJ
CREATE procedure spAJGRestoreAsFarAsYouCan
(
@.Path varchar(200) = 'c:\mssql\backup\',
@.DBName varchar(30) = 'ALC',
@.DoItOrJustPrint char(1) = 'Y'
)
as
set nocount on
-- Get list of files in directory
declare @.SqlCmd varchar(300)
set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
create table #DirList
(
RowID int identity,
FileOnly varchar(400)
)
insert #DirList ( FileOnly )
exec master..xp_cmdshell @.SqlCmd
-- Last full backup ( .bak )
declare @.FullBackup varchar(300)
declare @.CurrentID int
Select top 1
@.CurrentID = RowID,
@.FullBackup = @.Path + FileOnly
from #DirList
where patindex( '%.bak%', FileOnly) > 0
order by RowID desc
-- if @.FullBackup is NULL return -1
Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' + @.FullBackup + ''' with norecovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
Declare @.TransLog varchar(300)
Set @.TransLog = 'dummy'
While @.TransLog is Not NULL
Begin
Set @.TransLog = NULL
Select top 1
@.TransLog = @.Path + FileOnly,
@.CurrentID = RowID
from #DirList
where patindex( '%.trn%', FileOnly) > 0
and RowID > @.CurrentID
order by RowID
if @.TransLog is not NULL
Begin
Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' + @.TransLog + ''' with norecovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
End
End
Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
drop table #DirList
return (0)I'd just polish the script. I'd like to point out the following, though:
* You may want to change dir /t:c to dir /t:w
* If your directory may have files other than the database/log backup files,
you may want to check
each file with RESTORE HEADERONLY to make sure that it's a backup file
* Instead of relying on the file date/time to determine the order of the
restore, you can also use
RESTORE HEADERONLY to make sure that the order is correct.
* Using RESTORE HEADERONLY, you can even determine whether you have gaps in
your log
backups by inspecting the LSN's (log sequence numbers)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Andrew John" <aj@.DELETEmistrose.com> wrote in message
news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> Dear knowledgeable people,
> Is there a simple way to restore the latest full backup, plus all the
transaction
> log backups on to another server. I know you can on the same server,
'cause
> all the backup history is there in MSDB. But how many people back msdb up
> every 15 minutes?
> Consider the case:
> Full Backup of Database @. 0300 each day
> T.log backups every 15 minutes
> Crash at 19:00 takes out entire server, but not the backup files 'cause
> you cunningly configured File Replication Service to copy them elsewhere.
> When restoring, do you really have to type 60+ "restore log ... with no
recovery"
> commands (without typos) to get the database back?
> i.e Should I polish up the following script ( error handling /
differential backups ...)
> and/or write my own .exe to intelligently parse a backup directory, or am
I
> totally missing the point? ( Me programmer not normally sysadmin ).
> Regards
> AJ
> CREATE procedure spAJGRestoreAsFarAsYouCan
> (
> @.Path varchar(200) = 'c:\mssql\backup\',
> @.DBName varchar(30) = 'ALC',
> @.DoItOrJustPrint char(1) = 'Y'
> )
> as
> set nocount on
> -- Get list of files in directory
> declare @.SqlCmd varchar(300)
> set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> create table #DirList
> (
> RowID int identity,
> FileOnly varchar(400)
> )
> insert #DirList ( FileOnly )
> exec master..xp_cmdshell @.SqlCmd
> -- Last full backup ( .bak )
> declare @.FullBackup varchar(300)
> declare @.CurrentID int
> Select top 1
> @.CurrentID = RowID,
> @.FullBackup = @.Path + FileOnly
> from #DirList
> where patindex( '%.bak%', FileOnly) > 0
> order by RowID desc
> -- if @.FullBackup is NULL return -1
> Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
@.FullBackup + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> Declare @.TransLog varchar(300)
> Set @.TransLog = 'dummy'
> While @.TransLog is Not NULL
> Begin
> Set @.TransLog = NULL
> Select top 1
> @.TransLog = @.Path + FileOnly,
> @.CurrentID = RowID
> from #DirList
> where patindex( '%.trn%', FileOnly) > 0
> and RowID > @.CurrentID
> order by RowID
> if @.TransLog is not NULL
> Begin
> Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
@.TransLog + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> End
> End
>
> Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> drop table #DirList
> return (0)
>|||Andrew
Yeh, you need to be prepared for such situations.
I would write stored procedure that accept tree parameters
(dbname,from_hour,to_hour)
You need to keep transaction backups with appropriatte file name like
my_log1200 ,next my_log1215.
"Andrew John" <aj@.DELETEmistrose.com> wrote in message
news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> Dear knowledgeable people,
> Is there a simple way to restore the latest full backup, plus all the
transaction
> log backups on to another server. I know you can on the same server,
'cause
> all the backup history is there in MSDB. But how many people back msdb up
> every 15 minutes?
> Consider the case:
> Full Backup of Database @. 0300 each day
> T.log backups every 15 minutes
> Crash at 19:00 takes out entire server, but not the backup files 'cause
> you cunningly configured File Replication Service to copy them elsewhere.
> When restoring, do you really have to type 60+ "restore log ... with no
recovery"
> commands (without typos) to get the database back?
> i.e Should I polish up the following script ( error handling /
differential backups ...)
> and/or write my own .exe to intelligently parse a backup directory, or am
I
> totally missing the point? ( Me programmer not normally sysadmin ).
> Regards
> AJ
> CREATE procedure spAJGRestoreAsFarAsYouCan
> (
> @.Path varchar(200) = 'c:\mssql\backup\',
> @.DBName varchar(30) = 'ALC',
> @.DoItOrJustPrint char(1) = 'Y'
> )
> as
> set nocount on
> -- Get list of files in directory
> declare @.SqlCmd varchar(300)
> set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> create table #DirList
> (
> RowID int identity,
> FileOnly varchar(400)
> )
> insert #DirList ( FileOnly )
> exec master..xp_cmdshell @.SqlCmd
> -- Last full backup ( .bak )
> declare @.FullBackup varchar(300)
> declare @.CurrentID int
> Select top 1
> @.CurrentID = RowID,
> @.FullBackup = @.Path + FileOnly
> from #DirList
> where patindex( '%.bak%', FileOnly) > 0
> order by RowID desc
> -- if @.FullBackup is NULL return -1
> Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
@.FullBackup + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> Declare @.TransLog varchar(300)
> Set @.TransLog = 'dummy'
> While @.TransLog is Not NULL
> Begin
> Set @.TransLog = NULL
> Select top 1
> @.TransLog = @.Path + FileOnly,
> @.CurrentID = RowID
> from #DirList
> where patindex( '%.trn%', FileOnly) > 0
> and RowID > @.CurrentID
> order by RowID
> if @.TransLog is not NULL
> Begin
> Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
@.TransLog + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> End
> End
>
> Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> drop table #DirList
> return (0)
>|||Thank you all for your comments.
I'm glad to see I wasn't completely off track.
I am a bit surprised with t:c vs t:w. Wouldn't that
cause issues if the full backup took more than 15minutes ?
( It wont in my case - but I did think of that ).
What I am trying to write is something that can be run
in the wee hours of the morning, by some overstressed
service technician, without a microscopic examination of
file names and a touch typing course.
Pretty please Mr Microsoft - It wouldn't be too hard to put
some MSDB data into the T.Log backups would it?
Thanks Again
AJ
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message news:uygo6aAgDHA.1648@.TK2MSFTNGP09.phx.gbl...
> I'd just polish the script. I'd like to point out the following, though:
> * You may want to change dir /t:c to dir /t:w
> * If your directory may have files other than the database/log backup files,
> you may want to check
> each file with RESTORE HEADERONLY to make sure that it's a backup file
> * Instead of relying on the file date/time to determine the order of the
> restore, you can also use
> RESTORE HEADERONLY to make sure that the order is correct.
> * Using RESTORE HEADERONLY, you can even determine whether you have gaps in
> your log
> backups by inspecting the LSN's (log sequence numbers)
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > Dear knowledgeable people,
> >
> > Is there a simple way to restore the latest full backup, plus all the
> transaction
> > log backups on to another server. I know you can on the same server,
> 'cause
> > all the backup history is there in MSDB. But how many people back msdb up
> > every 15 minutes?
> >
> > Consider the case:
> >
> > Full Backup of Database @. 0300 each day
> > T.log backups every 15 minutes
> > Crash at 19:00 takes out entire server, but not the backup files 'cause
> > you cunningly configured File Replication Service to copy them elsewhere.
> >
> > When restoring, do you really have to type 60+ "restore log ... with no
> recovery"
> > commands (without typos) to get the database back?
> >
> > i.e Should I polish up the following script ( error handling /
> differential backups ...)
> > and/or write my own .exe to intelligently parse a backup directory, or am
> I
> > totally missing the point? ( Me programmer not normally sysadmin ).
> >
> > Regards
> > AJ
> >
> > CREATE procedure spAJGRestoreAsFarAsYouCan
> > (
> > @.Path varchar(200) = 'c:\mssql\backup\',
> > @.DBName varchar(30) = 'ALC',
> > @.DoItOrJustPrint char(1) = 'Y'
> > )
> > as
> >
> > set nocount on
> > -- Get list of files in directory
> > declare @.SqlCmd varchar(300)
> > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> >
> > create table #DirList
> > (
> > RowID int identity,
> > FileOnly varchar(400)
> > )
> >
> > insert #DirList ( FileOnly )
> > exec master..xp_cmdshell @.SqlCmd
> >
> > -- Last full backup ( .bak )
> > declare @.FullBackup varchar(300)
> > declare @.CurrentID int
> >
> > Select top 1
> > @.CurrentID = RowID,
> > @.FullBackup = @.Path + FileOnly
> > from #DirList
> > where patindex( '%.bak%', FileOnly) > 0
> > order by RowID desc
> >
> > -- if @.FullBackup is NULL return -1
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
> @.FullBackup + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > Declare @.TransLog varchar(300)
> > Set @.TransLog = 'dummy'
> > While @.TransLog is Not NULL
> > Begin
> >
> > Set @.TransLog = NULL
> > Select top 1
> > @.TransLog = @.Path + FileOnly,
> > @.CurrentID = RowID
> > from #DirList
> > where patindex( '%.trn%', FileOnly) > 0
> > and RowID > @.CurrentID
> > order by RowID
> >
> > if @.TransLog is not NULL
> > Begin
> > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
> @.TransLog + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> > End
> > End
> >
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > drop table #DirList
> >
> > return (0)
> >
> >
>|||> I always like using backup / restore for version control as it
> controls dev data versions along with schema objects etc.
IMHO, DDL (and selected DML) belongs in a version control system. This
facilitates new application installs as well as upgrades. Test data can
also be stored but its not as important to keep that under version
control.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eWKx7SAgDHA.1748@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew.
> I regularly need to do this on software dev projects for testing,
release
> etc.. I always like using backup / restore for version control as it
> controls dev data versions along with schema objects etc. So, it
sounds like
> my needs are similar to yours. (c:
> My approach is to use similar scripts / app logic, but there's a fair
bit of
> complexity as there's a full app with additional features not relevant
to
> your question, so I can't easily post scripts as such.
> The feature that I use though is to restore file headers, extract LSNs
&
> apply in order, which works whether the files are .bak, .diff or .trn.
I've
> found that use of .diffs mitigate your "60+ retore log statements"
problem
> significantly. Perhaps you could adjust your script to include .diff,
> restore headers & retore log / diff / db in LSN order?
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > Dear knowledgeable people,
> >
> > Is there a simple way to restore the latest full backup, plus all
the
> transaction
> > log backups on to another server. I know you can on the same
server,
> 'cause
> > all the backup history is there in MSDB. But how many people back
msdb up
> > every 15 minutes?
> >
> > Consider the case:
> >
> > Full Backup of Database @. 0300 each day
> > T.log backups every 15 minutes
> > Crash at 19:00 takes out entire server, but not the backup files
'cause
> > you cunningly configured File Replication Service to copy them
elsewhere.
> >
> > When restoring, do you really have to type 60+ "restore log ...
with no
> recovery"
> > commands (without typos) to get the database back?
> >
> > i.e Should I polish up the following script ( error handling /
> differential backups ...)
> > and/or write my own .exe to intelligently parse a backup directory,
or am
> I
> > totally missing the point? ( Me programmer not normally sysadmin ).
> >
> > Regards
> > AJ
> >
> > CREATE procedure spAJGRestoreAsFarAsYouCan
> > (
> > @.Path varchar(200) = 'c:\mssql\backup\',
> > @.DBName varchar(30) = 'ALC',
> > @.DoItOrJustPrint char(1) = 'Y'
> > )
> > as
> >
> > set nocount on
> > -- Get list of files in directory
> > declare @.SqlCmd varchar(300)
> > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> >
> > create table #DirList
> > (
> > RowID int identity,
> > FileOnly varchar(400)
> > )
> >
> > insert #DirList ( FileOnly )
> > exec master..xp_cmdshell @.SqlCmd
> >
> > -- Last full backup ( .bak )
> > declare @.FullBackup varchar(300)
> > declare @.CurrentID int
> >
> > Select top 1
> > @.CurrentID = RowID,
> > @.FullBackup = @.Path + FileOnly
> > from #DirList
> > where patindex( '%.bak%', FileOnly) > 0
> > order by RowID desc
> >
> > -- if @.FullBackup is NULL return -1
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
> @.FullBackup + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > Declare @.TransLog varchar(300)
> > Set @.TransLog = 'dummy'
> > While @.TransLog is Not NULL
> > Begin
> >
> > Set @.TransLog = NULL
> > Select top 1
> > @.TransLog = @.Path + FileOnly,
> > @.CurrentID = RowID
> > from #DirList
> > where patindex( '%.trn%', FileOnly) > 0
> > and RowID > @.CurrentID
> > order by RowID
> >
> > if @.TransLog is not NULL
> > Begin
> > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
> @.TransLog + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> > End
> > End
> >
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > drop table #DirList
> >
> > return (0)
> >
> >
>|||> I also advocate using VSS, this is really just supplemental dev team
> procedure.
I see what you mean now. Backup / restore certainly has its place for
taking snapshots to aid iterative testing. I'm not a big fan of VSS but
there are other better, albeit much more expensive, source control
products out there.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e3v3LvEgDHA.4024@.TK2MSFTNGP11.phx.gbl...
> Experience has made me (slightly) weary of VSS which is enough to
warrant
> extra protection! So, on my projects, I have been supplementing
version
> control of database objects by implementing fully logged backups (with
> transaction log marks in sql2k or timestamps in earlier versions).
With this
> approach you get bullet-proof backup & recoverability of all db source
/
> objects and data thrown in as well. This approach works on any
platform.
> Assuming the project doesn't have massive test / dev data
requirements, it's
> easy to store these on tape or ide media or just mix into a business's
> normal backup procedures.
> Being able to easily restore dev / test environments to marks or
specific
> points in time is a powerful technique for regression testing. As a
side
> benefit you also get early indications of capacity requirements for
backup /
> restore which a data tier architect will find useful when documenting
> capacity expectations.
> I also advocate using VSS, this is really just supplemental dev team
> procedure.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ez8u7kEgDHA.3896@.tk2msftngp13.phx.gbl...
> > > I always like using backup / restore for version control as it
> > > controls dev data versions along with schema objects etc.
> >
> > IMHO, DDL (and selected DML) belongs in a version control system.
This
> > facilitates new application installs as well as upgrades. Test data
can
> > also be stored but its not as important to keep that under version
> > control.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:eWKx7SAgDHA.1748@.TK2MSFTNGP10.phx.gbl...
> > > Hi Andrew.
> > >
> > > I regularly need to do this on software dev projects for testing,
> > release
> > > etc.. I always like using backup / restore for version control as
it
> > > controls dev data versions along with schema objects etc. So, it
> > sounds like
> > > my needs are similar to yours. (c:
> > >
> > > My approach is to use similar scripts / app logic, but there's a
fair
> > bit of
> > > complexity as there's a full app with additional features not
relevant
> > to
> > > your question, so I can't easily post scripts as such.
> > >
> > > The feature that I use though is to restore file headers, extract
LSNs
> > &
> > > apply in order, which works whether the files are .bak, .diff or
.trn.
> > I've
> > > found that use of .diffs mitigate your "60+ retore log statements"
> > problem
> > > significantly. Perhaps you could adjust your script to include
.diff,
> > > restore headers & retore log / diff / db in LSN order?
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> > > news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > Dear knowledgeable people,
> > > >
> > > > Is there a simple way to restore the latest full backup, plus
all
> > the
> > > transaction
> > > > log backups on to another server. I know you can on the same
> > server,
> > > 'cause
> > > > all the backup history is there in MSDB. But how many people
back
> > msdb up
> > > > every 15 minutes?
> > > >
> > > > Consider the case:
> > > >
> > > > Full Backup of Database @. 0300 each day
> > > > T.log backups every 15 minutes
> > > > Crash at 19:00 takes out entire server, but not the backup files
> > 'cause
> > > > you cunningly configured File Replication Service to copy them
> > elsewhere.
> > > >
> > > > When restoring, do you really have to type 60+ "restore log ...
> > with no
> > > recovery"
> > > > commands (without typos) to get the database back?
> > > >
> > > > i.e Should I polish up the following script ( error handling /
> > > differential backups ...)
> > > > and/or write my own .exe to intelligently parse a backup
directory,
> > or am
> > > I
> > > > totally missing the point? ( Me programmer not normally
sysadmin ).
> > > >
> > > > Regards
> > > > AJ
> > > >
> > > > CREATE procedure spAJGRestoreAsFarAsYouCan
> > > > (
> > > > @.Path varchar(200) = 'c:\mssql\backup\',
> > > > @.DBName varchar(30) = 'ALC',
> > > > @.DoItOrJustPrint char(1) = 'Y'
> > > > )
> > > > as
> > > >
> > > > set nocount on
> > > > -- Get list of files in directory
> > > > declare @.SqlCmd varchar(300)
> > > > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> > > >
> > > > create table #DirList
> > > > (
> > > > RowID int identity,
> > > > FileOnly varchar(400)
> > > > )
> > > >
> > > > insert #DirList ( FileOnly )
> > > > exec master..xp_cmdshell @.SqlCmd
> > > >
> > > > -- Last full backup ( .bak )
> > > > declare @.FullBackup varchar(300)
> > > > declare @.CurrentID int
> > > >
> > > > Select top 1
> > > > @.CurrentID = RowID,
> > > > @.FullBackup = @.Path + FileOnly
> > > > from #DirList
> > > > where patindex( '%.bak%', FileOnly) > 0
> > > > order by RowID desc
> > > >
> > > > -- if @.FullBackup is NULL return -1
> > > >
> > > > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk =''' +
> > > @.FullBackup + ''' with norecovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > >
> > > > Declare @.TransLog varchar(300)
> > > > Set @.TransLog = 'dummy'
> > > > While @.TransLog is Not NULL
> > > > Begin
> > > >
> > > > Set @.TransLog = NULL
> > > > Select top 1
> > > > @.TransLog = @.Path + FileOnly,
> > > > @.CurrentID = RowID
> > > > from #DirList
> > > > where patindex( '%.trn%', FileOnly) > 0
> > > > and RowID > @.CurrentID
> > > > order by RowID
> > > >
> > > > if @.TransLog is not NULL
> > > > Begin
> > > > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = '''
+
> > > @.TransLog + ''' with norecovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > > End
> > > > End
> > > >
> > > >
> > > > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > >
> > > > drop table #DirList
> > > >
> > > > return (0)
> > > >
> > > >
> > >
> > >
> >
> >
>
Monday, March 12, 2012
Restoring Master DB to a clustered server
Dear all,
Can anyone tell me the procedure of restoring the master database to a
clustered server? Do I need to do the same thing in both active and standby
servers?
Thanks for any help.
TedmondHello,
Please try the steps in test environment and do in prod env:-
a. Identify which active cluster node SQL Server is currently running on,
such as node1.
b. From Cluster Administrator - windows program groups, take the SQL Server
resources off-line.
c. Go to the node running SQL Server (see step 1) and start it from the
command prompt using:
sqlservr.exe -c -m
d. Restore the Master database .
e. shutdown SQL Server from the command prompt.
f. Do the same step in passive sql server [Step c and d only ]
g. Using Cluster Administrator, restart the SQL Server resources.
Thanks
Hari
"Tedmond" <Tedmond@.discussions.microsoft.com> wrote in message
news:3F306584-7CD4-49DE-A535-900B0560B6AF@.microsoft.com...
> Dear all,
> Can anyone tell me the procedure of restoring the master database to a
> clustered server? Do I need to do the same thing in both active and
> standby
> servers?
> Thanks for any help.
> Tedmond
Can anyone tell me the procedure of restoring the master database to a
clustered server? Do I need to do the same thing in both active and standby
servers?
Thanks for any help.
TedmondHello,
Please try the steps in test environment and do in prod env:-
a. Identify which active cluster node SQL Server is currently running on,
such as node1.
b. From Cluster Administrator - windows program groups, take the SQL Server
resources off-line.
c. Go to the node running SQL Server (see step 1) and start it from the
command prompt using:
sqlservr.exe -c -m
d. Restore the Master database .
e. shutdown SQL Server from the command prompt.
f. Do the same step in passive sql server [Step c and d only ]
g. Using Cluster Administrator, restart the SQL Server resources.
Thanks
Hari
"Tedmond" <Tedmond@.discussions.microsoft.com> wrote in message
news:3F306584-7CD4-49DE-A535-900B0560B6AF@.microsoft.com...
> Dear all,
> Can anyone tell me the procedure of restoring the master database to a
> clustered server? Do I need to do the same thing in both active and
> standby
> servers?
> Thanks for any help.
> Tedmond
Restoring Master DB to a clustered server
Dear all,
Can anyone tell me the procedure of restoring the master database to a
clustered server? Do I need to do the same thing in both active and standby
servers?
Thanks for any help.
Tedmond
Hello,
Please try the steps in test environment and do in prod env:-
a. Identify which active cluster node SQL Server is currently running on,
such as node1.
b. From Cluster Administrator - windows program groups, take the SQL Server
resources off-line.
c. Go to the node running SQL Server (see step 1) and start it from the
command prompt using:
sqlservr.exe -c -m
d. Restore the Master database .
e. shutdown SQL Server from the command prompt.
f. Do the same step in passive sql server [Step c and d only ]
g. Using Cluster Administrator, restart the SQL Server resources.
Thanks
Hari
"Tedmond" <Tedmond@.discussions.microsoft.com> wrote in message
news:3F306584-7CD4-49DE-A535-900B0560B6AF@.microsoft.com...
> Dear all,
> Can anyone tell me the procedure of restoring the master database to a
> clustered server? Do I need to do the same thing in both active and
> standby
> servers?
> Thanks for any help.
> Tedmond
Can anyone tell me the procedure of restoring the master database to a
clustered server? Do I need to do the same thing in both active and standby
servers?
Thanks for any help.
Tedmond
Hello,
Please try the steps in test environment and do in prod env:-
a. Identify which active cluster node SQL Server is currently running on,
such as node1.
b. From Cluster Administrator - windows program groups, take the SQL Server
resources off-line.
c. Go to the node running SQL Server (see step 1) and start it from the
command prompt using:
sqlservr.exe -c -m
d. Restore the Master database .
e. shutdown SQL Server from the command prompt.
f. Do the same step in passive sql server [Step c and d only ]
g. Using Cluster Administrator, restart the SQL Server resources.
Thanks
Hari
"Tedmond" <Tedmond@.discussions.microsoft.com> wrote in message
news:3F306584-7CD4-49DE-A535-900B0560B6AF@.microsoft.com...
> Dear all,
> Can anyone tell me the procedure of restoring the master database to a
> clustered server? Do I need to do the same thing in both active and
> standby
> servers?
> Thanks for any help.
> Tedmond
Restoring Master DB to a clustered server
Dear all,
Can anyone tell me the procedure of restoring the master database to a
clustered server? Do I need to do the same thing in both active and standby
servers?
Thanks for any help.
TedmondHello,
Please try the steps in test environment and do in prod env:-
a. Identify which active cluster node SQL Server is currently running on,
such as node1.
b. From Cluster Administrator - windows program groups, take the SQL Server
resources off-line.
c. Go to the node running SQL Server (see step 1) and start it from the
command prompt using:
sqlservr.exe -c -m
d. Restore the Master database .
e. shutdown SQL Server from the command prompt.
f. Do the same step in passive sql server [Step c and d only ]
g. Using Cluster Administrator, restart the SQL Server resources.
Thanks
Hari
"Tedmond" <Tedmond@.discussions.microsoft.com> wrote in message
news:3F306584-7CD4-49DE-A535-900B0560B6AF@.microsoft.com...
> Dear all,
> Can anyone tell me the procedure of restoring the master database to a
> clustered server? Do I need to do the same thing in both active and
> standby
> servers?
> Thanks for any help.
> Tedmond
Can anyone tell me the procedure of restoring the master database to a
clustered server? Do I need to do the same thing in both active and standby
servers?
Thanks for any help.
TedmondHello,
Please try the steps in test environment and do in prod env:-
a. Identify which active cluster node SQL Server is currently running on,
such as node1.
b. From Cluster Administrator - windows program groups, take the SQL Server
resources off-line.
c. Go to the node running SQL Server (see step 1) and start it from the
command prompt using:
sqlservr.exe -c -m
d. Restore the Master database .
e. shutdown SQL Server from the command prompt.
f. Do the same step in passive sql server [Step c and d only ]
g. Using Cluster Administrator, restart the SQL Server resources.
Thanks
Hari
"Tedmond" <Tedmond@.discussions.microsoft.com> wrote in message
news:3F306584-7CD4-49DE-A535-900B0560B6AF@.microsoft.com...
> Dear all,
> Can anyone tell me the procedure of restoring the master database to a
> clustered server? Do I need to do the same thing in both active and
> standby
> servers?
> Thanks for any help.
> Tedmond
Friday, March 9, 2012
Restoring from Backup and Fulltext Catelog
Dear Friends,
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
Sathian
Review the notes in
http://support.microsoft.com/default...b;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
Sathian
Review the notes in
http://support.microsoft.com/default...b;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>
Wednesday, March 7, 2012
Restoring from Backup and Fulltext Catelog
Dear Friends,
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
SathianReview the notes in
http://support.microsoft.com/defaul...kb;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
SathianReview the notes in
http://support.microsoft.com/defaul...kb;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>
Tuesday, February 21, 2012
Restoring Database to a different database on a different server
Dear All,
we have a database A backup on server Y that we would like
to restore as database B on server X.
Can anyone provide me with some guidence on how to do thisHave a look at HOW TO: Move Databases Between Computers That Are Running SQL
Server
http://support.microsoft.com/defaul...kb;en-us;314546
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
> Dear All,
> we have a database A backup on server Y that we would like
> to restore as database B on server X.
> Can anyone provide me with some guidence on how to do this|||Thanks Ana
>--Original Message--
>Have a look at HOW TO: Move Databases Between Computers
That Are Running SQL
>Server
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;314546
>
>"Mia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
like[vbcol=seagreen]
this[vbcol=seagreen]
>
>.
>|||Mia
Another option
RESTORE DATABASE MYNAME FROM DISK =
N'\\RemoteServer\backup\MYNAME_other.BAK'
This have to be an identical path on both servers.
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Ana
>
> That Are Running SQL
> us;314546
> message
> like
> this|||...or
RESTORE DATABASE NewName FROM DISK ='\\Remoteserver\...\a_backup.bak WITH
MOVE 'logicalfilename_data' TO 'path to database file',
MOVE 'logicalfilename_log' TO 'path to log file'
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Mia
> Another option
> RESTORE DATABASE MYNAME FROM DISK =
> N'\\RemoteServer\backup\MYNAME_other.BAK'
> This have to be an identical path on both servers.
>
>
> "Mia" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...
we have a database A backup on server Y that we would like
to restore as database B on server X.
Can anyone provide me with some guidence on how to do thisHave a look at HOW TO: Move Databases Between Computers That Are Running SQL
Server
http://support.microsoft.com/defaul...kb;en-us;314546
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
> Dear All,
> we have a database A backup on server Y that we would like
> to restore as database B on server X.
> Can anyone provide me with some guidence on how to do this|||Thanks Ana
>--Original Message--
>Have a look at HOW TO: Move Databases Between Computers
That Are Running SQL
>Server
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;314546
>
>"Mia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
like[vbcol=seagreen]
this[vbcol=seagreen]
>
>.
>|||Mia
Another option
RESTORE DATABASE MYNAME FROM DISK =
N'\\RemoteServer\backup\MYNAME_other.BAK'
This have to be an identical path on both servers.
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Ana
>
> That Are Running SQL
> us;314546
> message
> like
> this|||...or
RESTORE DATABASE NewName FROM DISK ='\\Remoteserver\...\a_backup.bak WITH
MOVE 'logicalfilename_data' TO 'path to database file',
MOVE 'logicalfilename_log' TO 'path to log file'
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Mia
> Another option
> RESTORE DATABASE MYNAME FROM DISK =
> N'\\RemoteServer\backup\MYNAME_other.BAK'
> This have to be an identical path on both servers.
>
>
> "Mia" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...
Restoring Database to a different database on a different server
Dear All,
we have a database A backup on server Y that we would like
to restore as database B on server X.
Can anyone provide me with some guidence on how to do this
Have a look at HOW TO: Move Databases Between Computers That Are Running SQL
Server
http://support.microsoft.com/default...b;en-us;314546
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
> Dear All,
> we have a database A backup on server Y that we would like
> to restore as database B on server X.
> Can anyone provide me with some guidence on how to do this
|||Thanks Ana
>--Original Message--
>Have a look at HOW TO: Move Databases Between Computers
That Are Running SQL
>Server
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;314546
>
>"Mia" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
like[vbcol=seagreen]
this
>
>.
>
|||Mia
Another option
RESTORE DATABASE MYNAME FROM DISK =
N'\\RemoteServer\backup\MYNAME_other.BAK'
This have to be an identical path on both servers.
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Ana
>
> That Are Running SQL
> us;314546
> message
> like
> this
|||...or
RESTORE DATABASE NewName FROM DISK ='\\Remoteserver\...\a_backup.bak WITH
MOVE 'logicalfilename_data' TO 'path to database file',
MOVE 'logicalfilename_log' TO 'path to log file'
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Mia
> Another option
> RESTORE DATABASE MYNAME FROM DISK =
> N'\\RemoteServer\backup\MYNAME_other.BAK'
> This have to be an identical path on both servers.
>
>
> "Mia" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...
we have a database A backup on server Y that we would like
to restore as database B on server X.
Can anyone provide me with some guidence on how to do this
Have a look at HOW TO: Move Databases Between Computers That Are Running SQL
Server
http://support.microsoft.com/default...b;en-us;314546
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
> Dear All,
> we have a database A backup on server Y that we would like
> to restore as database B on server X.
> Can anyone provide me with some guidence on how to do this
|||Thanks Ana
>--Original Message--
>Have a look at HOW TO: Move Databases Between Computers
That Are Running SQL
>Server
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;314546
>
>"Mia" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
like[vbcol=seagreen]
this
>
>.
>
|||Mia
Another option
RESTORE DATABASE MYNAME FROM DISK =
N'\\RemoteServer\backup\MYNAME_other.BAK'
This have to be an identical path on both servers.
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Ana
>
> That Are Running SQL
> us;314546
> message
> like
> this
|||...or
RESTORE DATABASE NewName FROM DISK ='\\Remoteserver\...\a_backup.bak WITH
MOVE 'logicalfilename_data' TO 'path to database file',
MOVE 'logicalfilename_log' TO 'path to log file'
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Mia
> Another option
> RESTORE DATABASE MYNAME FROM DISK =
> N'\\RemoteServer\backup\MYNAME_other.BAK'
> This have to be an identical path on both servers.
>
>
> "Mia" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...
Restoring Database to a different database on a different server
Dear All,
we have a database A backup on server Y that we would like
to restore as database B on server X.
Can anyone provide me with some guidence on how to do thisHave a look at HOW TO: Move Databases Between Computers That Are Running SQL
Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
> Dear All,
> we have a database A backup on server Y that we would like
> to restore as database B on server X.
> Can anyone provide me with some guidence on how to do this|||Thanks Ana
>--Original Message--
>Have a look at HOW TO: Move Databases Between Computers
That Are Running SQL
>Server
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;314546
>
>"Mia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
>> Dear All,
>> we have a database A backup on server Y that we would
like
>> to restore as database B on server X.
>> Can anyone provide me with some guidence on how to do
this
>
>.
>|||Mia
Another option
RESTORE DATABASE MYNAME FROM DISK = N'\\RemoteServer\backup\MYNAME_other.BAK'
This have to be an identical path on both servers.
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...
> Thanks Ana
>
> >--Original Message--
> >Have a look at HOW TO: Move Databases Between Computers
> That Are Running SQL
> >Server
> >http://support.microsoft.com/default.aspx?scid=kb;en-
> us;314546
> >
> >
> >"Mia" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
> >> Dear All,
> >>
> >> we have a database A backup on server Y that we would
> like
> >> to restore as database B on server X.
> >>
> >> Can anyone provide me with some guidence on how to do
> this
> >
> >
> >.
> >|||...or
RESTORE DATABASE NewName FROM DISK ='\\Remoteserver\...\a_backup.bak WITH
MOVE 'logicalfilename_data' TO 'path to database file',
MOVE 'logicalfilename_log' TO 'path to log file'
Regards
Steen
Uri Dimant wrote:
> Mia
> Another option
> RESTORE DATABASE MYNAME FROM DISK => N'\\RemoteServer\backup\MYNAME_other.BAK'
> This have to be an identical path on both servers.
>
>
> "Mia" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...
>> Thanks Ana
>>
>> --Original Message--
>> Have a look at HOW TO: Move Databases Between Computers That Are
>> Running SQL Server
>> http://support.microsoft.com/default.aspx?scid=kb;en- us;314546
>>
>> "Mia" <anonymous@.discussions.microsoft.com> wrote in message
>> news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
>> Dear All,
>> we have a database A backup on server Y that we would like
>> to restore as database B on server X.
>> Can anyone provide me with some guidence on how to do this
>>
>> .
we have a database A backup on server Y that we would like
to restore as database B on server X.
Can anyone provide me with some guidence on how to do thisHave a look at HOW TO: Move Databases Between Computers That Are Running SQL
Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
> Dear All,
> we have a database A backup on server Y that we would like
> to restore as database B on server X.
> Can anyone provide me with some guidence on how to do this|||Thanks Ana
>--Original Message--
>Have a look at HOW TO: Move Databases Between Computers
That Are Running SQL
>Server
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;314546
>
>"Mia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
>> Dear All,
>> we have a database A backup on server Y that we would
like
>> to restore as database B on server X.
>> Can anyone provide me with some guidence on how to do
this
>
>.
>|||Mia
Another option
RESTORE DATABASE MYNAME FROM DISK = N'\\RemoteServer\backup\MYNAME_other.BAK'
This have to be an identical path on both servers.
"Mia" <anonymous@.discussions.microsoft.com> wrote in message
news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...
> Thanks Ana
>
> >--Original Message--
> >Have a look at HOW TO: Move Databases Between Computers
> That Are Running SQL
> >Server
> >http://support.microsoft.com/default.aspx?scid=kb;en-
> us;314546
> >
> >
> >"Mia" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
> >> Dear All,
> >>
> >> we have a database A backup on server Y that we would
> like
> >> to restore as database B on server X.
> >>
> >> Can anyone provide me with some guidence on how to do
> this
> >
> >
> >.
> >|||...or
RESTORE DATABASE NewName FROM DISK ='\\Remoteserver\...\a_backup.bak WITH
MOVE 'logicalfilename_data' TO 'path to database file',
MOVE 'logicalfilename_log' TO 'path to log file'
Regards
Steen
Uri Dimant wrote:
> Mia
> Another option
> RESTORE DATABASE MYNAME FROM DISK => N'\\RemoteServer\backup\MYNAME_other.BAK'
> This have to be an identical path on both servers.
>
>
> "Mia" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b4801c509d8$21ba4a90$a501280a@.phx.gbl...
>> Thanks Ana
>>
>> --Original Message--
>> Have a look at HOW TO: Move Databases Between Computers That Are
>> Running SQL Server
>> http://support.microsoft.com/default.aspx?scid=kb;en- us;314546
>>
>> "Mia" <anonymous@.discussions.microsoft.com> wrote in message
>> news:2b2a01c509d2$ce2ffa00$a501280a@.phx.gbl...
>> Dear All,
>> we have a database A backup on server Y that we would like
>> to restore as database B on server X.
>> Can anyone provide me with some guidence on how to do this
>>
>> .
Subscribe to:
Posts (Atom)