restore Back file to new server database

I've been reading articles on "how to restore a database to a new location",
"how to restore a database with a new name", etc.
What I need is a step by step on how to:
Using a preexisting database backup,
point it to a different server,
create that database on that different server,
apply the full back up,
do a "point in time" restore from the original database transaction log.
I thought I could do most ofthis but I am having NO luck in getting the
database to restore to a new database with the same name on a different
server.
How is this done?It is easier if you post the TSQL commands you are executing. Basically, you:
On the originating server:
BACKUP DATABASE dbname
BACKUP LOG dbname
BACKUP LOG dbname
On the destination server:
RESTORE DATABASE dbname ... WITH NORECOVERY...
If the database exists and the database file structure doesn't match with the original, you need to
use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
RESTORE LOG dbname... WITH NORECOVERY...
RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
> I've been reading articles on "how to restore a database to a new location",
> "how to restore a database with a new name", etc.
> What I need is a step by step on how to:
> Using a preexisting database backup,
> point it to a different server,
> create that database on that different server,
> apply the full back up,
> do a "point in time" restore from the original database transaction log.
> I thought I could do most ofthis but I am having NO luck in getting the
> database to restore to a new database with the same name on a different
> server.
> How is this done?|||There seems to be a couple of things going on that I'm not sure about;
The following is the way I have things typed, I'm confused with the "From"
peremeter. If this is the device I'm restoring from, is this than going to
be the literal name of the back up file?
I also get the following error:
"One or more of the options (stopat) are not supported for this statement"
my comments typed in the Query Analyzer;
RECOVER DATABASE Market
FROM ?
WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
"Tibor Karaszi" wrote:
> It is easier if you post the TSQL commands you are executing. Basically, you:
> On the originating server:
> BACKUP DATABASE dbname
> BACKUP LOG dbname
> BACKUP LOG dbname
> On the destination server:
> RESTORE DATABASE dbname ... WITH NORECOVERY...
> If the database exists and the database file structure doesn't match with the original, you need to
> use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
> RESTORE LOG dbname... WITH NORECOVERY...
> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
> > I've been reading articles on "how to restore a database to a new location",
> > "how to restore a database with a new name", etc.
> > What I need is a step by step on how to:
> > Using a preexisting database backup,
> > point it to a different server,
> > create that database on that different server,
> > apply the full back up,
> > do a "point in time" restore from the original database transaction log.
> >
> > I thought I could do most ofthis but I am having NO luck in getting the
> > database to restore to a new database with the same name on a different
> > server.
> >
> > How is this done?
>|||FROM is either a predefined backupdevice (read about sp_addumpdevice):
RESTORE DATABASE Market
FROM myBackupDevice
WITH RECOVERY, STOPAT = '20051014 20:00'
Or a physical file name:
RESTORE DATABASE Market
FROM DISK = 'C:\myBackupDevice.BAK'
WITH RECOVERY, STOPAT = '20051014 20:00'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
> There seems to be a couple of things going on that I'm not sure about;
> The following is the way I have things typed, I'm confused with the "From"
> peremeter. If this is the device I'm restoring from, is this than going to
> be the literal name of the back up file?
> I also get the following error:
> "One or more of the options (stopat) are not supported for this statement"
> my comments typed in the Query Analyzer;
> RECOVER DATABASE Market
> FROM ?
> WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
> "Tibor Karaszi" wrote:
>> It is easier if you post the TSQL commands you are executing. Basically, you:
>> On the originating server:
>> BACKUP DATABASE dbname
>> BACKUP LOG dbname
>> BACKUP LOG dbname
>> On the destination server:
>> RESTORE DATABASE dbname ... WITH NORECOVERY...
>> If the database exists and the database file structure doesn't match with the original, you need
>> to
>> use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
>> RESTORE LOG dbname... WITH NORECOVERY...
>> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
>> > I've been reading articles on "how to restore a database to a new location",
>> > "how to restore a database with a new name", etc.
>> > What I need is a step by step on how to:
>> > Using a preexisting database backup,
>> > point it to a different server,
>> > create that database on that different server,
>> > apply the full back up,
>> > do a "point in time" restore from the original database transaction log.
>> >
>> > I thought I could do most ofthis but I am having NO luck in getting the
>> > database to restore to a new database with the same name on a different
>> > server.
>> >
>> > How is this done?
>>|||And if I wanted to restore from the transaction log, the following being the
appopriate syntex?
RESTORE LOG market
FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
WITH RECOVERY, STOPAT = '20050914 20:00'
"Tibor Karaszi" wrote:
> FROM is either a predefined backupdevice (read about sp_addumpdevice):
> RESTORE DATABASE Market
> FROM myBackupDevice
> WITH RECOVERY, STOPAT = '20051014 20:00'
>
> Or a physical file name:
> RESTORE DATABASE Market
> FROM DISK = 'C:\myBackupDevice.BAK'
> WITH RECOVERY, STOPAT = '20051014 20:00'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
> > There seems to be a couple of things going on that I'm not sure about;
> > The following is the way I have things typed, I'm confused with the "From"
> > peremeter. If this is the device I'm restoring from, is this than going to
> > be the literal name of the back up file?
> >
> > I also get the following error:
> > "One or more of the options (stopat) are not supported for this statement"
> >
> > my comments typed in the Query Analyzer;
> >
> > RECOVER DATABASE Market
> > FROM ?
> > WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
> >
> > "Tibor Karaszi" wrote:
> >
> >> It is easier if you post the TSQL commands you are executing. Basically, you:
> >>
> >> On the originating server:
> >> BACKUP DATABASE dbname
> >> BACKUP LOG dbname
> >> BACKUP LOG dbname
> >>
> >> On the destination server:
> >> RESTORE DATABASE dbname ... WITH NORECOVERY...
> >> If the database exists and the database file structure doesn't match with the original, you need
> >> to
> >> use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
> >> RESTORE LOG dbname... WITH NORECOVERY...
> >> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> >> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
> >> > I've been reading articles on "how to restore a database to a new location",
> >> > "how to restore a database with a new name", etc.
> >> > What I need is a step by step on how to:
> >> > Using a preexisting database backup,
> >> > point it to a different server,
> >> > create that database on that different server,
> >> > apply the full back up,
> >> > do a "point in time" restore from the original database transaction log.
> >> >
> >> > I thought I could do most ofthis but I am having NO luck in getting the
> >> > database to restore to a new database with the same name on a different
> >> > server.
> >> >
> >> > How is this done?
> >>
> >>
>|||Correct. I missed that in my prior post where I specified RESTORE DATABASE with STOPAT, which isn't
possible. STOPAT is only possible for LOG restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:8CCDAB6D-9C3D-4FCA-89B1-0D46A12983A7@.microsoft.com...
> And if I wanted to restore from the transaction log, the following being the
> appopriate syntex?
> RESTORE LOG market
> FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
> WITH RECOVERY, STOPAT = '20050914 20:00'
> "Tibor Karaszi" wrote:
>> FROM is either a predefined backupdevice (read about sp_addumpdevice):
>> RESTORE DATABASE Market
>> FROM myBackupDevice
>> WITH RECOVERY, STOPAT = '20051014 20:00'
>>
>> Or a physical file name:
>> RESTORE DATABASE Market
>> FROM DISK = 'C:\myBackupDevice.BAK'
>> WITH RECOVERY, STOPAT = '20051014 20:00'
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
>> > There seems to be a couple of things going on that I'm not sure about;
>> > The following is the way I have things typed, I'm confused with the "From"
>> > peremeter. If this is the device I'm restoring from, is this than going to
>> > be the literal name of the back up file?
>> >
>> > I also get the following error:
>> > "One or more of the options (stopat) are not supported for this statement"
>> >
>> > my comments typed in the Query Analyzer;
>> >
>> > RECOVER DATABASE Market
>> > FROM ?
>> > WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> It is easier if you post the TSQL commands you are executing. Basically, you:
>> >>
>> >> On the originating server:
>> >> BACKUP DATABASE dbname
>> >> BACKUP LOG dbname
>> >> BACKUP LOG dbname
>> >>
>> >> On the destination server:
>> >> RESTORE DATABASE dbname ... WITH NORECOVERY...
>> >> If the database exists and the database file structure doesn't match with the original, you
>> >> need
>> >> to
>> >> use the REPLACE option. If you need to move files, you need to use the MOVE option for each
>> >> file.
>> >> RESTORE LOG dbname... WITH NORECOVERY...
>> >> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> >> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
>> >> > I've been reading articles on "how to restore a database to a new location",
>> >> > "how to restore a database with a new name", etc.
>> >> > What I need is a step by step on how to:
>> >> > Using a preexisting database backup,
>> >> > point it to a different server,
>> >> > create that database on that different server,
>> >> > apply the full back up,
>> >> > do a "point in time" restore from the original database transaction log.
>> >> >
>> >> > I thought I could do most ofthis but I am having NO luck in getting the
>> >> > database to restore to a new database with the same name on a different
>> >> > server.
>> >> >
>> >> > How is this done?
>> >>
>> >>
>>|||I may have bigger problems than what I thought. The file I was looking at is
evidently a copy of the original database from a few months ago, the
extensions was not visible and it was offering itself as a back up file was
why I thought it was a BAK.
It does not appear that a full backup has been done on this database prior
to a few days ago.
The problem is some time in the past 4 weeks some data has evaporated, the
thought was to do a STOPAT recovery with the transaction logs but if we don't
have a full back up prior to 4 weeks ago is this going to be possible?
"Tibor Karaszi" wrote:
> Correct. I missed that in my prior post where I specified RESTORE DATABASE with STOPAT, which isn't
> possible. STOPAT is only possible for LOG restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:8CCDAB6D-9C3D-4FCA-89B1-0D46A12983A7@.microsoft.com...
> > And if I wanted to restore from the transaction log, the following being the
> > appopriate syntex?
> >
> > RESTORE LOG market
> > FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
> > WITH RECOVERY, STOPAT = '20050914 20:00'
> >
> > "Tibor Karaszi" wrote:
> >
> >> FROM is either a predefined backupdevice (read about sp_addumpdevice):
> >>
> >> RESTORE DATABASE Market
> >> FROM myBackupDevice
> >> WITH RECOVERY, STOPAT = '20051014 20:00'
> >>
> >>
> >>
> >> Or a physical file name:
> >>
> >> RESTORE DATABASE Market
> >> FROM DISK = 'C:\myBackupDevice.BAK'
> >> WITH RECOVERY, STOPAT = '20051014 20:00'
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> >> news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
> >> > There seems to be a couple of things going on that I'm not sure about;
> >> > The following is the way I have things typed, I'm confused with the "From"
> >> > peremeter. If this is the device I'm restoring from, is this than going to
> >> > be the literal name of the back up file?
> >> >
> >> > I also get the following error:
> >> > "One or more of the options (stopat) are not supported for this statement"
> >> >
> >> > my comments typed in the Query Analyzer;
> >> >
> >> > RECOVER DATABASE Market
> >> > FROM ?
> >> > WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> It is easier if you post the TSQL commands you are executing. Basically, you:
> >> >>
> >> >> On the originating server:
> >> >> BACKUP DATABASE dbname
> >> >> BACKUP LOG dbname
> >> >> BACKUP LOG dbname
> >> >>
> >> >> On the destination server:
> >> >> RESTORE DATABASE dbname ... WITH NORECOVERY...
> >> >> If the database exists and the database file structure doesn't match with the original, you
> >> >> need
> >> >> to
> >> >> use the REPLACE option. If you need to move files, you need to use the MOVE option for each
> >> >> file.
> >> >> RESTORE LOG dbname... WITH NORECOVERY...
> >> >> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> >> >> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
> >> >> > I've been reading articles on "how to restore a database to a new location",
> >> >> > "how to restore a database with a new name", etc.
> >> >> > What I need is a step by step on how to:
> >> >> > Using a preexisting database backup,
> >> >> > point it to a different server,
> >> >> > create that database on that different server,
> >> >> > apply the full back up,
> >> >> > do a "point in time" restore from the original database transaction log.
> >> >> >
> >> >> > I thought I could do most ofthis but I am having NO luck in getting the
> >> >> > database to restore to a new database with the same name on a different
> >> >> > server.
> >> >> >
> >> >> > How is this done?
> >> >>
> >> >>
> >>
> >>
>|||Are you saying that the earlier database backup if from a few days ago, and you want to do point in
time restore to an earlier time? If so, you can't. A log backup can only be applied on a database
backup. First restore database backup, then each log backup taken after that database backup,
optionally using STOPAT for the last log restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:B894C0C8-D6EB-4DE2-827F-9E9871A75C35@.microsoft.com...
>I may have bigger problems than what I thought. The file I was looking at is
> evidently a copy of the original database from a few months ago, the
> extensions was not visible and it was offering itself as a back up file was
> why I thought it was a BAK.
> It does not appear that a full backup has been done on this database prior
> to a few days ago.
> The problem is some time in the past 4 weeks some data has evaporated, the
> thought was to do a STOPAT recovery with the transaction logs but if we don't
> have a full back up prior to 4 weeks ago is this going to be possible?
> "Tibor Karaszi" wrote:
>> Correct. I missed that in my prior post where I specified RESTORE DATABASE with STOPAT, which
>> isn't
>> possible. STOPAT is only possible for LOG restore.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> news:8CCDAB6D-9C3D-4FCA-89B1-0D46A12983A7@.microsoft.com...
>> > And if I wanted to restore from the transaction log, the following being the
>> > appopriate syntex?
>> >
>> > RESTORE LOG market
>> > FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
>> > WITH RECOVERY, STOPAT = '20050914 20:00'
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> FROM is either a predefined backupdevice (read about sp_addumpdevice):
>> >>
>> >> RESTORE DATABASE Market
>> >> FROM myBackupDevice
>> >> WITH RECOVERY, STOPAT = '20051014 20:00'
>> >>
>> >>
>> >>
>> >> Or a physical file name:
>> >>
>> >> RESTORE DATABASE Market
>> >> FROM DISK = 'C:\myBackupDevice.BAK'
>> >> WITH RECOVERY, STOPAT = '20051014 20:00'
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> >> news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
>> >> > There seems to be a couple of things going on that I'm not sure about;
>> >> > The following is the way I have things typed, I'm confused with the "From"
>> >> > peremeter. If this is the device I'm restoring from, is this than going to
>> >> > be the literal name of the back up file?
>> >> >
>> >> > I also get the following error:
>> >> > "One or more of the options (stopat) are not supported for this statement"
>> >> >
>> >> > my comments typed in the Query Analyzer;
>> >> >
>> >> > RECOVER DATABASE Market
>> >> > FROM ?
>> >> > WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> It is easier if you post the TSQL commands you are executing. Basically, you:
>> >> >>
>> >> >> On the originating server:
>> >> >> BACKUP DATABASE dbname
>> >> >> BACKUP LOG dbname
>> >> >> BACKUP LOG dbname
>> >> >>
>> >> >> On the destination server:
>> >> >> RESTORE DATABASE dbname ... WITH NORECOVERY...
>> >> >> If the database exists and the database file structure doesn't match with the original, you
>> >> >> need
>> >> >> to
>> >> >> use the REPLACE option. If you need to move files, you need to use the MOVE option for each
>> >> >> file.
>> >> >> RESTORE LOG dbname... WITH NORECOVERY...
>> >> >> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >> >>
>> >> >>
>> >> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> >> >> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
>> >> >> > I've been reading articles on "how to restore a database to a new location",
>> >> >> > "how to restore a database with a new name", etc.
>> >> >> > What I need is a step by step on how to:
>> >> >> > Using a preexisting database backup,
>> >> >> > point it to a different server,
>> >> >> > create that database on that different server,
>> >> >> > apply the full back up,
>> >> >> > do a "point in time" restore from the original database transaction log.
>> >> >> >
>> >> >> > I thought I could do most ofthis but I am having NO luck in getting the
>> >> >> > database to restore to a new database with the same name on a different
>> >> >> > server.
>> >> >> >
>> >> >> > How is this done?
>> >> >>
>> >> >>
>> >>
>> >>
>>