restore master database: script

Showing posts with label script. Show all posts

Monday, March 26, 2012

Restore to new server

I restored a backup from our main server to a new server. First I copied
that backup file, then executed a script basically Restore with move,
replace (I replaced an empty database I created to test the connection).
It ran for a minute or two, then said successful, but here it is 2 hours
later and the new database is still unavailable and says it's restoring.
The backup file was just shy of 1 GB.
Is this the kind of time frame I should be expecting?
Probably not. What happens if you do a restore databaseName with Recovery?
Does that make the database accessible.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Usarian" <noemail@.nojunk.com> wrote in message
news:elcLpnv7GHA.4620@.TK2MSFTNGP02.phx.gbl...
>I restored a backup from our main server to a new server. First I copied
>that backup file, then executed a script basically Restore with move,
>replace (I replaced an empty database I created to test the connection).
> It ran for a minute or two, then said successful, but here it is 2 hours
> later and the new database is still unavailable and says it's restoring.
> The backup file was just shy of 1 GB.
> Is this the kind of time frame I should be expecting?
>

Friday, March 23, 2012

Restore SQL2000 database using vbscript

Hello,
I am trying to use a VBscript thru installshield to restore a sql2000 database. the script (below) worked fine for default instance of database. but with a specific named instance I get error 3201. any ideas? thx - Prasanna
-----------
code:

dim sql
dim sqlRest

on error resume next
Set sql = CreateObject("SQLDMO.SQLServer")
sql.LoginSecure = True
sql.Connect ".\TEST2000"

Set sqlRest = CreateObject("SQLDMO.Restore")
sqlRest.Files = "c:\testdb.bak"
sqlRest.Database = "TESTDB"
sqlRest.Action = SQLDMORestore_Database
sqlRest.ReplaceDatabase = True
sqlRest.SQLRestore sql
msgbox err.numberFound a solution:

The backup was created using default instance of SQL2000 and once I used a backup created using the 'TEST2000' instance the script worked. I think you can use the RelocateFiles property of the SQLDMO Restore object to set the location of the data and log files but due to time constraints I could not try that solution.

-Prasanna

Restore SQL Server to default SQL data directory

Hi,
I need to prepare an Installshield project that restore a database on
my target machine.
No problem with script
RESTORE DATABASE BIGStudio FROM DISK=MyDir\MyBackup.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10, MOVE 'DB_dat' TO
'\Data\DB_Data.mdf',
MOVE 'DB__log' TO '\Data\DB_log.ldf'.
But in my target machine I can have SQL Server 2000, SQL Server 2005,
MSDE 2000...
I've tried to restore DB on my INSTALL dir but I receive an access
denied error. How can I restore my DB to the default SQL Server data
directory on my target machine?
Thank you
Gianluca DhoYou may need to obtain this info dynamically?
Check out xp_instance_regread
Immy
"Gianluca Dho" <gianluca.dho@.gmail.com> wrote in message
news:1159535209.992156.163070@.b28g2000cwb.googlegroups.com...
> Hi,
> I need to prepare an Installshield project that restore a database on
> my target machine.
> No problem with script
> RESTORE DATABASE BIGStudio FROM DISK=MyDir\MyBackup.bak'
> WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10, MOVE 'DB_dat' TO
> '\Data\DB_Data.mdf',
> MOVE 'DB__log' TO '\Data\DB_log.ldf'.
> But in my target machine I can have SQL Server 2000, SQL Server 2005,
> MSDE 2000...
> I've tried to restore DB on my INSTALL dir but I receive an access
> denied error. How can I restore my DB to the default SQL Server data
> directory on my target machine?
> Thank you
> Gianluca Dho
>

Wednesday, March 21, 2012

Restore SQL DB with correct logical file names

Hi,

I am planning to automate a nighty restore of a DB on another server
can someone point me in the right direction with the SQL script to
modify the logical file names to the correct path and not the ones
carried over with the DB??

i.e the database is to be renamed on the new server

any help much appreciated

Many thanks in advanceblueboy wrote:

Quote:

Originally Posted by

Hi,
>
I am planning to automate a nighty restore of a DB on another server
can someone point me in the right direction with the SQL script to
modify the logical file names to the correct path and not the ones
carried over with the DB??
>
i.e the database is to be renamed on the new server
>
any help much appreciated
>
Many thanks in advance
>

This should point you in the right direction:

http://support.microsoft.com/defaul...kb;en-us;314546
If you restore the database to a different file location than the source
database, you must specify the WITH MOVE option. For example, on the
source server the database is in the D:\Mssql\Data folder. The
destination server does not have a D drive, and you want to restore the
database to the C:\Mssql\Data folder.

Good luck|||Many thanks for that it seems to be what i was after however i keep
getting an error -

The job failed. The Job was invoked by User domainname\user. The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).

here is the scripting i have;

kill connections -
ALTER DATABASE {db name} SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Restore -

RESTORE DATABASE {db name}
FROM DISK =
'E:\folde\{db name} .bak'
WITH MOVE 'Logical_Name_Data' TO 'G:\SQLDATA\MSSQL\data\{db name}
_Data.MDF',
MOVE 'Logical_Data_Log' TO 'G:\SQLDATA\MSSQL\data\{db name}
_Log.LDF',
STATS = 1, REPLACE
GO

It stops at step 2 i also notice when i go back into the steps they
are defaulting back to the master database??

Any help much appreciated|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

Many thanks for that it seems to be what i was after however i keep
getting an error -
>
The job failed. The Job was invoked by User domainname\user. The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).

Did you look under Job history to see what failed? Up to the right
(in Enterprise Manager in SQL 2000), there is a checkbox which says "View
step history". There should be an error message.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||yes the error is

Quote:

Originally Posted by

The job failed. The Job was invoked by User domainname\user. The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).

cheers|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

yes the error is
>

Quote:

Originally Posted by

>The job failed. The Job was invoked by User domainname\user. The
>last step to run was step 2 (Restore). The job was requested to start
>at step 1 (Kill connections).

That's the error for the job as such. That's not the output from the job
step. Please check "Show step details".

here is the info

Executed as user: User domainname\user. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error
3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed.

Step one runs fine which disconnects users so not sure what the prob
is? any ideas??

Many thanks|||"blueboy" <matt_meech@.hotmail.comwrote in message
news:1172237955.311495.294700@.q2g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

Arrr Apologies
>
here is the info
>
Executed as user: User domainname\user. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error
3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed.
>
Step one runs fine which disconnects users so not sure what the prob
is? any ideas??
>

Are you sure the job isn't trying to run while in that DB?

Quote:

Originally Posted by

Many thanks
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Sorry what do you mean by

Are you sure the job isn't trying to run while in that DB?

appologies for sounding daft its been a long day!!

cheers|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

here is the info
>
Executed as user: User domainname\user. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error
3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed.
>
Step one runs fine which disconnects users so not sure what the prob
is? any ideas??

Seems like you set the database for that job step to be the database
you want to restore. Change to master, and you should be fine.

Or someone manages to sneak in betnween the job steps. Make it one
single step to avoid this risk.

(But put SET MULTI_USER in step 2, and on the Advanced tab for step 1,
configure the job to continue with step 2, even if step 1 fails.)

Executed as user: domain\user. Logical file 'UKReports _Data.MDF' is
not part of database 'ukreports'. Use RESTORE FILELISTONLY to list the
logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
failed.

RESTORE DATABASE rentsmartukreports
FROM DISK =
'E:\Nightly backups server\UK.bak'
WITH MOVE 'G:\SQLDATA\MSSQL\data\UK _Data' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Data.MDF',
MOVE 'G:\SQLDATA\MSSQL\data\UK _Log' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Log.LDF',
STATS = 1, REPLACE
GO

Where uk is old DB name and UKReports is new DB name

Any ideas??|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

Hi still having probs get the following error
>
Executed as user: domain\user. Logical file 'UKReports _Data.MDF' is
not part of database 'ukreports'. Use RESTORE FILELISTONLY to list the
logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
failed.
>
RESTORE DATABASE rentsmartukreports
FROM DISK =
'E:\Nightly backups server\UK.bak'
WITH MOVE 'G:\SQLDATA\MSSQL\data\UK _Data' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Data.MDF',
MOVE 'G:\SQLDATA\MSSQL\data\UK _Log' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Log.LDF',
STATS = 1, REPLACE
GO

Indeed, 'G:\SQLDATA\MSSQL\data\UK _Data' looks like a very unusual
logical name. Usually the logical file name of the data file is the
same as the database name, and the log file has "_log" tacked on it.
Sometimes the MDF has "_Data" in the logical name.

You can use sp_helpdb to find out the logical names of a dataase.
Or, if all you have is a backup, RESTORE FILELISTONLY.

RESTORE DATABASE [new DB name]
FROM DISK =
'E:\Nightly backups Man1s\[Old DB name].bak'
WITH MOVE '[Old DB name] _Data' TO 'G:\SQLDATA\MSSQL\data\[new DB
name] _Data.MDF',
MOVE '[Old DB name]_Log' TO 'G:\SQLDATA\MSSQL\data\[new DB
name]_Log.LDF',
STATS = 1, REPLACE
GO

i get above error as stated, any help much appreciated does anyone
actaully have this working?? if so can i see your script??

cheers|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

This is so frustrating, i have tried numerous variations and always
get the same error can any one help??
>
RESTORE DATABASE [new DB name]
FROM DISK =
'E:\Nightly backups Man1s\[Old DB name].bak'
WITH MOVE '[Old DB name] _Data' TO 'G:\SQLDATA\MSSQL\data\[new DB
name] _Data.MDF',
MOVE '[Old DB name]_Log' TO 'G:\SQLDATA\MSSQL\data\[new DB
name]_Log.LDF',
STATS = 1, REPLACE
GO
>
i get above error as stated, any help much appreciated does anyone
actaully have this working?? if so can i see your script??

You have the syntax right, but how could I write a script for you
when I don't know the logical names of your database files? All I can
say is that

'[Old DB name] _Data'

looks funny. There are brackets in the name, and there is a blank in
the middle.

This will remain frustrating, if you just take chances on the name.
You need to find out what the names are, and there are two ways to
do it:

1) sp_helpdb on the source database. It's the first column in the
second result set, just copy and paste into the single quotes.
2) RESTIRE FILELISTONLY on the back-up file. Again, it's the first
column.

I finally got this working so thanks for anyone else here is the
script i used

databasename should be the Database Name

step 1 kill conections

ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE

step 2 restore DB

RESTORE DATABASE datbasename
FROM DISK =
'F:\backup\databasename.bak'
WITH MOVE 'databasename_Data' TO 'F:\MSSQL\MSSQL\Data\databasename
MDF',
MOVE 'databasename_Log' TO 'F:\MSSQL\MSSQL\databasename.LDF',
STATS = 1, REPLACE
GO

step 3 allow connections

ALTER DATABASE databasename SET MULTI_USER

Tuesday, March 20, 2012

Restore script restoring to old version of the database

A month ago I set up a small program to backup my databases – it ran the
following script against the database:
BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
NOINIT, PASSWORD='xxx', RETAINDAYS=90
This was run every day and saves the backup to a removable USB drive which
is taken off-site at night. The backed up file grew every time the script
was run – so I assumed it was working fine and left it to it.
Then yesterday I had a main drive failure and spent the day rebuilding my
machine. Then I went to restore my databases with the following script (in
query analyzer) (note the MOVE was needed as I am storing the data files in
a
different location now):
RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE 'MyDatabase_Log'
TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
But to my horror it has only restored the database to the point it was in a
month ago when I first started these backups – I appear to have lost a mon
ths
worth of data.
The .Bak file has a modified datetime equal to the last time the script was
run (the night before last) – so the backups were happening. What am I do
ing
wrong here? Note that the recovery model was set to FULL.
Please help.Hi
Looks like you appended each new backup into the same file. With your
restore script, you restored the 1st one.
Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
that the file contains. Then use that information to do a RESTORE DATABASE.
If you do the restore though enterprise manager, it will the backups too and
then you can select which one you want to restore.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>A month ago I set up a small program to backup my databases - it ran the
> following script against the database:
> BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> NOINIT, PASSWORD='xxx', RETAINDAYS=90
> This was run every day and saves the backup to a removable USB drive which
> is taken off-site at night. The backed up file grew every time the script
> was run - so I assumed it was working fine and left it to it.
> Then yesterday I had a main drive failure and spent the day rebuilding my
> machine. Then I went to restore my databases with the following script
> (in
> query analyzer) (note the MOVE was needed as I am storing the data files
> in a
> different location now):
> RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> 'MyDatabase_Log'
> TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> But to my horror it has only restored the database to the point it was in
> a
> month ago when I first started these backups - I appear to have lost a
> months
> worth of data.
> The .Bak file has a modified datetime equal to the last time the script
> was
> run (the night before last) - so the backups were happening. What am I
> doing
> wrong here? Note that the recovery model was set to FULL.
> Please help.|||Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
BackupDescription fields are all Null. So how do I specify that I want to
restore the last one?
Also – I can’t use Enterprise Manager because it doesn’t have the opti
on to
specify a password – so I have to use a script.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Looks like you appended each new backup into the same file. With your
> restore script, you restored the 1st one.
> Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> that the file contains. Then use that information to do a RESTORE DATABASE
.
> If you do the restore though enterprise manager, it will the backups too a
nd
> then you can select which one you want to restore.
>
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>
>|||Ahhhh - Problem solved. I needed to add FILE=13 to the WITH parameters:
RESTORE DATABASE MyDatabase
FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH FILE=13,
MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'D:\SQLData\Mydatabase_Log.ldf',
Password='xxx'
Many thanks Mike for pointing me in the right direction!
"RodneyL" wrote:
[vbcol=seagreen]
> Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
> BackupDescription fields are all Null. So how do I specify that I want to
> restore the last one?
> Also – I can’t use Enterprise Manager because it doesn’t have the op
tion to
> specify a password – so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:
>

Restore script restoring to old version of the database

A month ago I set up a small program to backup my databases – it ran the
following script against the database:
BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
NOINIT, PASSWORD='xxx', RETAINDAYS=90
This was run every day and saves the backup to a removable USB drive which
is taken off-site at night. The backed up file grew every time the script
was run – so I assumed it was working fine and left it to it.
Then yesterday I had a main drive failure and spent the day rebuilding my
machine. Then I went to restore my databases with the following script (in
query analyzer) (note the MOVE was needed as I am storing the data files in a
different location now):
RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE 'MyDatabase_Log'
TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
But to my horror it has only restored the database to the point it was in a
month ago when I first started these backups – I appear to have lost a months
worth of data.
The .Bak file has a modified datetime equal to the last time the script was
run (the night before last) – so the backups were happening. What am I doing
wrong here? Note that the recovery model was set to FULL.
Please help.
Hi
Looks like you appended each new backup into the same file. With your
restore script, you restored the 1st one.
Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
that the file contains. Then use that information to do a RESTORE DATABASE.
If you do the restore though enterprise manager, it will the backups too and
then you can select which one you want to restore.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>A month ago I set up a small program to backup my databases - it ran the
> following script against the database:
> BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> NOINIT, PASSWORD='xxx', RETAINDAYS=90
> This was run every day and saves the backup to a removable USB drive which
> is taken off-site at night. The backed up file grew every time the script
> was run - so I assumed it was working fine and left it to it.
> Then yesterday I had a main drive failure and spent the day rebuilding my
> machine. Then I went to restore my databases with the following script
> (in
> query analyzer) (note the MOVE was needed as I am storing the data files
> in a
> different location now):
> RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> 'MyDatabase_Log'
> TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> But to my horror it has only restored the database to the point it was in
> a
> month ago when I first started these backups - I appear to have lost a
> months
> worth of data.
> The .Bak file has a modified datetime equal to the last time the script
> was
> run (the night before last) - so the backups were happening. What am I
> doing
> wrong here? Note that the recovery model was set to FULL.
> Please help.
|||Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
BackupDescription fields are all Null. So how do I specify that I want to
restore the last one?
Also – I can’t use Enterprise Manager because it doesn’t have the option to
specify a password – so I have to use a script.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Looks like you appended each new backup into the same file. With your
> restore script, you restored the 1st one.
> Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> that the file contains. Then use that information to do a RESTORE DATABASE.
> If you do the restore though enterprise manager, it will the backups too and
> then you can select which one you want to restore.
>
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>
>
|||Ahhhh - Problem solved. I needed to add FILE=13 to the WITH parameters:
RESTORE DATABASE MyDatabase
FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH FILE=13,
MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'D:\SQLData\Mydatabase_Log.ldf',
Password='xxx'
Many thanks Mike for pointing me in the right direction!
"RodneyL" wrote:
[vbcol=seagreen]
> Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
> BackupDescription fields are all Null. So how do I specify that I want to
> restore the last one?
> Also – I can’t use Enterprise Manager because it doesn’t have the option to
> specify a password – so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:

Restore script restoring to old version of the database

A month ago I set up a small program to backup my databases â' it ran the
following script against the database:
BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
NOINIT, PASSWORD='xxx', RETAINDAYS=90
This was run every day and saves the backup to a removable USB drive which
is taken off-site at night. The backed up file grew every time the script
was run â' so I assumed it was working fine and left it to it.
Then yesterday I had a main drive failure and spent the day rebuilding my
machine. Then I went to restore my databases with the following script (in
query analyzer) (note the MOVE was needed as I am storing the data files in a
different location now):
RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE 'MyDatabase_Log'
TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
But to my horror it has only restored the database to the point it was in a
month ago when I first started these backups â' I appear to have lost a months
worth of data.
The .Bak file has a modified datetime equal to the last time the script was
run (the night before last) â' so the backups were happening. What am I doing
wrong here? Note that the recovery model was set to FULL.
Please help.Hi
Looks like you appended each new backup into the same file. With your
restore script, you restored the 1st one.
Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
that the file contains. Then use that information to do a RESTORE DATABASE.
If you do the restore though enterprise manager, it will the backups too and
then you can select which one you want to restore.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>A month ago I set up a small program to backup my databases - it ran the
> following script against the database:
> BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> NOINIT, PASSWORD='xxx', RETAINDAYS=90
> This was run every day and saves the backup to a removable USB drive which
> is taken off-site at night. The backed up file grew every time the script
> was run - so I assumed it was working fine and left it to it.
> Then yesterday I had a main drive failure and spent the day rebuilding my
> machine. Then I went to restore my databases with the following script
> (in
> query analyzer) (note the MOVE was needed as I am storing the data files
> in a
> different location now):
> RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> 'MyDatabase_Log'
> TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> But to my horror it has only restored the database to the point it was in
> a
> month ago when I first started these backups - I appear to have lost a
> months
> worth of data.
> The .Bak file has a modified datetime equal to the last time the script
> was
> run (the night before last) - so the backups were happening. What am I
> doing
> wrong here? Note that the recovery model was set to FULL.
> Please help.|||Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
BackupDescription fields are all Null. So how do I specify that I want to
restore the last one?
Also â' I canâ't use Enterprise Manager because it doesnâ't have the option to
specify a password â' so I have to use a script.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Looks like you appended each new backup into the same file. With your
> restore script, you restored the 1st one.
> Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> that the file contains. Then use that information to do a RESTORE DATABASE.
> If you do the restore though enterprise manager, it will the backups too and
> then you can select which one you want to restore.
>
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
> >A month ago I set up a small program to backup my databases - it ran the
> > following script against the database:
> >
> > BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> > NOINIT, PASSWORD='xxx', RETAINDAYS=90
> >
> > This was run every day and saves the backup to a removable USB drive which
> > is taken off-site at night. The backed up file grew every time the script
> > was run - so I assumed it was working fine and left it to it.
> >
> > Then yesterday I had a main drive failure and spent the day rebuilding my
> > machine. Then I went to restore my databases with the following script
> > (in
> > query analyzer) (note the MOVE was needed as I am storing the data files
> > in a
> > different location now):
> >
> > RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> > WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> > 'MyDatabase_Log'
> > TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> >
> > But to my horror it has only restored the database to the point it was in
> > a
> > month ago when I first started these backups - I appear to have lost a
> > months
> > worth of data.
> >
> > The .Bak file has a modified datetime equal to the last time the script
> > was
> > run (the night before last) - so the backups were happening. What am I
> > doing
> > wrong here? Note that the recovery model was set to FULL.
> >
> > Please help.
>
>|||Ahhhh - Problem solved. I needed to add FILE=13 to the WITH parameters:
RESTORE DATABASE MyDatabase
FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH FILE=13,
MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'D:\SQLData\Mydatabase_Log.ldf',
Password='xxx'
Many thanks Mike for pointing me in the right direction!
"RodneyL" wrote:
> Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
> BackupDescription fields are all Null. So how do I specify that I want to
> restore the last one?
> Also â' I canâ't use Enterprise Manager because it doesnâ't have the option to
> specify a password â' so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Looks like you appended each new backup into the same file. With your
> > restore script, you restored the 1st one.
> >
> > Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> > that the file contains. Then use that information to do a RESTORE DATABASE.
> >
> > If you do the restore though enterprise manager, it will the backups too and
> > then you can select which one you want to restore.
> >
> >
> >
> > --
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> > news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
> > >A month ago I set up a small program to backup my databases - it ran the
> > > following script against the database:
> > >
> > > BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> > > NOINIT, PASSWORD='xxx', RETAINDAYS=90
> > >
> > > This was run every day and saves the backup to a removable USB drive which
> > > is taken off-site at night. The backed up file grew every time the script
> > > was run - so I assumed it was working fine and left it to it.
> > >
> > > Then yesterday I had a main drive failure and spent the day rebuilding my
> > > machine. Then I went to restore my databases with the following script
> > > (in
> > > query analyzer) (note the MOVE was needed as I am storing the data files
> > > in a
> > > different location now):
> > >
> > > RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> > > WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> > > 'MyDatabase_Log'
> > > TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> > >
> > > But to my horror it has only restored the database to the point it was in
> > > a
> > > month ago when I first started these backups - I appear to have lost a
> > > months
> > > worth of data.
> > >
> > > The .Bak file has a modified datetime equal to the last time the script
> > > was
> > > run (the night before last) - so the backups were happening. What am I
> > > doing
> > > wrong here? Note that the recovery model was set to FULL.
> > >
> > > Please help.
> >
> >
> >

Restore script does not seem to work

Hello,
I am trying to restore a 1GB database by using the script below:
alter database [auto] set single_user
go
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
with norecovery,
move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
stats = percentage
go
I added the stats line because it ran for over 2 hours.
I let this run for about half an hour, but it never showed any statistics.
The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
going up to 2 % every few seconds.
The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
Standard, and there is 20 GB of space on F.
I have restored the same DB by using the Enterprise Manager and I think it
took 5 minutes.
I want to write a script to do this to simplify the process, since this is a
backup server and I am trying to make the job easier for whoever end up with
the job of restoring.
Any ideas why this is happening? I have triple checked the folder structures
and the names.
Any help would be much appreciated.
Ragnar
The problem is the stats thing ie
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = 5
> go
THis means that SQL should show stats after each 5% has completed...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to restore a 1GB database by using the script below:
> alter database [auto] set single_user
> go
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = percentage
> go
> I added the stats line because it ran for over 2 hours.
> I let this run for about half an hour, but it never showed any statistics.
> The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
> going up to 2 % every few seconds.
> The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
> Standard, and there is 20 GB of space on F.
> I have restored the same DB by using the Enterprise Manager and I think it
> took 5 minutes.
> I want to write a script to do this to simplify the process, since this is
> a backup server and I am trying to make the job easier for whoever end up
> with the job of restoring.
> Any ideas why this is happening? I have triple checked the folder
> structures and the names.
> Any help would be much appreciated.
> Ragnar
>
|||Thank you Wayne,
It turns out that there was something wrong with the server, our IS guys
eventually had to recycle it.
Once I fixed the STATS statement it ran in 202 seconds.
But I was puzzled about the stats display, nothing showed up until the job
finished!
I had used this before, I just copied some sample code, and as far as I can
remember it would print a line whenever a certain percentage had been
processed.
Ragnar
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23ZzzIlqdFHA.2288@.TK2MSFTNGP14.phx.gbl...
> The problem is the stats thing ie
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> THis means that SQL should show stats after each 5% has completed...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
>

Restore script does not seem to work

Hello,
I am trying to restore a 1GB database by using the script below:
alter database [auto] set single_user
go
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
with norecovery,
move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
stats = percentage
go
I added the stats line because it ran for over 2 hours.
I let this run for about half an hour, but it never showed any statistics.
The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
going up to 2 % every few seconds.
The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
Standard, and there is 20 GB of space on F.
I have restored the same DB by using the Enterprise Manager and I think it
took 5 minutes.
I want to write a script to do this to simplify the process, since this is a
backup server and I am trying to make the job easier for whoever end up with
the job of restoring.
Any ideas why this is happening? I have triple checked the folder structures
and the names.
Any help would be much appreciated.
RagnarThe problem is the stats thing ie
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = 5
> go
THis means that SQL should show stats after each 5% has completed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to restore a 1GB database by using the script below:
> alter database [auto] set single_user
> go
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = percentage
> go
> I added the stats line because it ran for over 2 hours.
> I let this run for about half an hour, but it never showed any statistics.
> The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
> going up to 2 % every few seconds.
> The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
> Standard, and there is 20 GB of space on F.
> I have restored the same DB by using the Enterprise Manager and I think it
> took 5 minutes.
> I want to write a script to do this to simplify the process, since this is
> a backup server and I am trying to make the job easier for whoever end up
> with the job of restoring.
> Any ideas why this is happening? I have triple checked the folder
> structures and the names.
> Any help would be much appreciated.
> Ragnar
>|||Thank you Wayne,
It turns out that there was something wrong with the server, our IS guys
eventually had to recycle it.
Once I fixed the STATS statement it ran in 202 seconds.
But I was puzzled about the stats display, nothing showed up until the job
finished!
I had used this before, I just copied some sample code, and as far as I can
remember it would print a line whenever a certain percentage had been
processed.
Ragnar
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23ZzzIlqdFHA.2288@.TK2MSFTNGP14.phx.gbl...
> The problem is the stats thing ie
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
>> with norecovery,
>> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
>> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
>> stats = 5
>> go
> THis means that SQL should show stats after each 5% has completed...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
>> Hello,
>> I am trying to restore a 1GB database by using the script below:
>> alter database [auto] set single_user
>> go
>> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
>> with norecovery,
>> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
>> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
>> stats = percentage
>> go
>> I added the stats line because it ran for over 2 hours.
>> I let this run for about half an hour, but it never showed any
>> statistics.
>> The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
>> going up to 2 % every few seconds.
>> The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
>> Standard, and there is 20 GB of space on F.
>> I have restored the same DB by using the Enterprise Manager and I think
>> it took 5 minutes.
>> I want to write a script to do this to simplify the process, since this
>> is a backup server and I am trying to make the job easier for whoever end
>> up with the job of restoring.
>> Any ideas why this is happening? I have triple checked the folder
>> structures and the names.
>> Any help would be much appreciated.
>> Ragnar
>

Restore script does not seem to work

Hello,
I am trying to restore a 1GB database by using the script below:
alter database [auto] set single_user
go
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
with norecovery,
move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
stats = percentage
go
I added the stats line because it ran for over 2 hours.
I let this run for about half an hour, but it never showed any statistics.
The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
going up to 2 % every few seconds.
The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
Standard, and there is 20 GB of space on F.
I have restored the same DB by using the Enterprise Manager and I think it
took 5 minutes.
I want to write a script to do this to simplify the process, since this is a
backup server and I am trying to make the job easier for whoever end up with
the job of restoring.
Any ideas why this is happening? I have triple checked the folder structures
and the names.
Any help would be much appreciated.
RagnarThe problem is the stats thing ie
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'">
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = 5
> go
THis means that SQL should show stats after each 5% has completed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to restore a 1GB database by using the script below:
> alter database [auto] set single_user
> go
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = percentage
> go
> I added the stats line because it ran for over 2 hours.
> I let this run for about half an hour, but it never showed any statistics.
> The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
> going up to 2 % every few seconds.
> The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
> Standard, and there is 20 GB of space on F.
> I have restored the same DB by using the Enterprise Manager and I think it
> took 5 minutes.
> I want to write a script to do this to simplify the process, since this is
> a backup server and I am trying to make the job easier for whoever end up
> with the job of restoring.
> Any ideas why this is happening? I have triple checked the folder
> structures and the names.
> Any help would be much appreciated.
> Ragnar
>|||Thank you Wayne,
It turns out that there was something wrong with the server, our IS guys
eventually had to recycle it.
Once I fixed the STATS statement it ran in 202 seconds.
But I was puzzled about the stats display, nothing showed up until the job
finished!
I had used this before, I just copied some sample code, and as far as I can
remember it would print a line whenever a certain percentage had been
processed.
Ragnar
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23ZzzIlqdFHA.2288@.TK2MSFTNGP14.phx.gbl...
> The problem is the stats thing ie
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> THis means that SQL should show stats after each 5% has completed...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
>

Restore script

" <hari_pra

_k@.hotmail.com> wrote in message
news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Probably you can switch the database to single user before restore
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE Command
> GO
> ALTER DATABASE <dbname> SET MULTI_USER
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||Could you post the exact Restore statement you are issuing. The one you
posted earlier is to restore in MYDB1 database.
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems
> it
> is trying to restore to MYDB1.
> Thanks.
>
> "Hari Pra

" <hari_pra

_k@.hotmail.com> wrote in message
> news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
>|||**********************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB2
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
REPLACE
**********************
"Hari Pra

" <hari_pra

_k@.hotmail.com> wrote in message
news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Could you post the exact Restore statement you are issuing. The one you
> posted earlier is to restore in MYDB1 database.
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
seems
still
use.
>|||Hi Hari,
I already found out the problem I did not change restore database to MYDB2
it is still MYDB1 in my original script.
Thanks.
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:%235C6Z%23nZFHA.3840@.tk2msftngp13.phx.gbl...
> **********************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB2
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
> REPLACE
> **********************
> "Hari Pra

" <hari_pra

_k@.hotmail.com> wrote in message
> news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> seems
restore
> still
> use.
>

Restore script

Hi,
I have the script below and it is working find if I tried to restore the
backup on one database only i.e. MYDB1 but if I execute the script below and
change MYDB1 to MYDB2 I got the error below. It seems that it is still
trying to restore to MYDB1. Any idea why?
Msg 3101, Level 16, State 2, Server SERVER1, Line 35
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Server SERVER1, Line 35
RESTORE DATABASE is terminating abnormally.
**********************************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB1
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
REPLACE
**********************************Hi,
Probably you can switch the database to single user before restore
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE Command
GO
ALTER DATABASE <dbname> SET MULTI_USER
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the script below and it is working find if I tried to restore the
> backup on one database only i.e. MYDB1 but if I execute the script below
> and
> change MYDB1 to MYDB2 I got the error below. It seems that it is still
> trying to restore to MYDB1. Any idea why?
> Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> RESTORE DATABASE is terminating abnormally.
>
> **********************************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB1
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> REPLACE
> **********************************
>|||Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems it
is trying to restore to MYDB1.
Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Probably you can switch the database to single user before restore
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE Command
> GO
> ALTER DATABASE <dbname> SET MULTI_USER
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have the script below and it is working find if I tried to restore the
> > backup on one database only i.e. MYDB1 but if I execute the script below
> > and
> > change MYDB1 to MYDB2 I got the error below. It seems that it is still
> > trying to restore to MYDB1. Any idea why?
> >
> > Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> > Exclusive access could not be obtained because the database is in use.
> > Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> > RESTORE DATABASE is terminating abnormally.
> >
> >
> > **********************************
> > USE TEMPDB
> >
> > DECLARE @.DNAME AS VARCHAR(20)
> > DECLARE @.LNAME AS VARCHAR(20)
> >
> > CREATE TABLE #LIST
> > (
> > LogicalName sysname NOT NULL,
> > PhysicalName varchar(255) NOT NULL,
> > Type char(1) NOT NULL,
> > FileGroupName sysname NULL,
> > Size bigint NOT NULL,
> > MaxSize bigint NOT NULL
> > )
> > INSERT INTO #LIST
> > EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> >
> > SELECT @.DNAME =(
> > SELECT LogicalName
> > FROM #LIST
> > WHERE type = 'D')
> > SELECT @.LNAME =(
> > SELECT LogicalName
> > FROM #LIST
> > WHERE type = 'L')
> >
> > RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> >
> > USE MASTER
> >
> > SELECT @.LNAME
> > SELECT @.DNAME
> >
> > RESTORE DATABASE MYDB1
> > FROM DISK = 'E:\backup.bak'
> > WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> > MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> > REPLACE
> > **********************************
> >
> >
>|||Could you post the exact Restore statement you are issuing. The one you
posted earlier is to restore in MYDB1 database.
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems
> it
> is trying to restore to MYDB1.
> Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> Probably you can switch the database to single user before restore
>> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>> GO
>> RESTORE DATABASE Command
>> GO
>> ALTER DATABASE <dbname> SET MULTI_USER
>> Thanks
>> Hari
>> SQL Server MVP
>> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
>> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have the script below and it is working find if I tried to restore
>> > the
>> > backup on one database only i.e. MYDB1 but if I execute the script
>> > below
>> > and
>> > change MYDB1 to MYDB2 I got the error below. It seems that it is still
>> > trying to restore to MYDB1. Any idea why?
>> >
>> > Msg 3101, Level 16, State 2, Server SERVER1, Line 35
>> > Exclusive access could not be obtained because the database is in use.
>> > Msg 3013, Level 16, State 1, Server SERVER1, Line 35
>> > RESTORE DATABASE is terminating abnormally.
>> >
>> >
>> > **********************************
>> > USE TEMPDB
>> >
>> > DECLARE @.DNAME AS VARCHAR(20)
>> > DECLARE @.LNAME AS VARCHAR(20)
>> >
>> > CREATE TABLE #LIST
>> > (
>> > LogicalName sysname NOT NULL,
>> > PhysicalName varchar(255) NOT NULL,
>> > Type char(1) NOT NULL,
>> > FileGroupName sysname NULL,
>> > Size bigint NOT NULL,
>> > MaxSize bigint NOT NULL
>> > )
>> > INSERT INTO #LIST
>> > EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
>> >
>> > SELECT @.DNAME =(
>> > SELECT LogicalName
>> > FROM #LIST
>> > WHERE type = 'D')
>> > SELECT @.LNAME =(
>> > SELECT LogicalName
>> > FROM #LIST
>> > WHERE type = 'L')
>> >
>> > RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
>> >
>> > USE MASTER
>> >
>> > SELECT @.LNAME
>> > SELECT @.DNAME
>> >
>> > RESTORE DATABASE MYDB1
>> > FROM DISK = 'E:\backup.bak'
>> > WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
>> > MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
>> > REPLACE
>> > **********************************
>> >
>> >
>>
>|||**********************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB2
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
REPLACE
**********************
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Could you post the exact Restore statement you are issuing. The one you
> posted earlier is to restore in MYDB1 database.
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> > Nobody is connected to MYDB2 but on MYDB1 one user is connected. It
seems
> > it
> > is trying to restore to MYDB1.
> >
> > Thanks.
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> >> Hi,
> >>
> >> Probably you can switch the database to single user before restore
> >>
> >> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> >> GO
> >>
> >> RESTORE DATABASE Command
> >>
> >> GO
> >> ALTER DATABASE <dbname> SET MULTI_USER
> >>
> >> Thanks
> >> Hari
> >> SQL Server MVP
> >>
> >> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> >> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> >
> >> > I have the script below and it is working find if I tried to restore
> >> > the
> >> > backup on one database only i.e. MYDB1 but if I execute the script
> >> > below
> >> > and
> >> > change MYDB1 to MYDB2 I got the error below. It seems that it is
still
> >> > trying to restore to MYDB1. Any idea why?
> >> >
> >> > Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> >> > Exclusive access could not be obtained because the database is in
use.
> >> > Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> >
> >> > **********************************
> >> > USE TEMPDB
> >> >
> >> > DECLARE @.DNAME AS VARCHAR(20)
> >> > DECLARE @.LNAME AS VARCHAR(20)
> >> >
> >> > CREATE TABLE #LIST
> >> > (
> >> > LogicalName sysname NOT NULL,
> >> > PhysicalName varchar(255) NOT NULL,
> >> > Type char(1) NOT NULL,
> >> > FileGroupName sysname NULL,
> >> > Size bigint NOT NULL,
> >> > MaxSize bigint NOT NULL
> >> > )
> >> > INSERT INTO #LIST
> >> > EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> >> >
> >> > SELECT @.DNAME =(
> >> > SELECT LogicalName
> >> > FROM #LIST
> >> > WHERE type = 'D')
> >> > SELECT @.LNAME =(
> >> > SELECT LogicalName
> >> > FROM #LIST
> >> > WHERE type = 'L')
> >> >
> >> > RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> >> >
> >> > USE MASTER
> >> >
> >> > SELECT @.LNAME
> >> > SELECT @.DNAME
> >> >
> >> > RESTORE DATABASE MYDB1
> >> > FROM DISK = 'E:\backup.bak'
> >> > WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> >> > MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> >> > REPLACE
> >> > **********************************
> >> >
> >> >
> >>
> >>
> >
> >
>|||Hi Hari,
I already found out the problem I did not change restore database to MYDB2
it is still MYDB1 in my original script.
Thanks.
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:%235C6Z%23nZFHA.3840@.tk2msftngp13.phx.gbl...
> **********************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB2
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
> REPLACE
> **********************
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> > Could you post the exact Restore statement you are issuing. The one you
> > posted earlier is to restore in MYDB1 database.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> > news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> > > Nobody is connected to MYDB2 but on MYDB1 one user is connected. It
> seems
> > > it
> > > is trying to restore to MYDB1.
> > >
> > > Thanks.
> > >
> > >
> > > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > > news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> > >> Hi,
> > >>
> > >> Probably you can switch the database to single user before restore
> > >>
> > >> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > >> GO
> > >>
> > >> RESTORE DATABASE Command
> > >>
> > >> GO
> > >> ALTER DATABASE <dbname> SET MULTI_USER
> > >>
> > >> Thanks
> > >> Hari
> > >> SQL Server MVP
> > >>
> > >> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> > >> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> > >> > Hi,
> > >> >
> > >> > I have the script below and it is working find if I tried to
restore
> > >> > the
> > >> > backup on one database only i.e. MYDB1 but if I execute the script
> > >> > below
> > >> > and
> > >> > change MYDB1 to MYDB2 I got the error below. It seems that it is
> still
> > >> > trying to restore to MYDB1. Any idea why?
> > >> >
> > >> > Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> > >> > Exclusive access could not be obtained because the database is in
> use.
> > >> > Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> > >> > RESTORE DATABASE is terminating abnormally.
> > >> >
> > >> >
> > >> > **********************************
> > >> > USE TEMPDB
> > >> >
> > >> > DECLARE @.DNAME AS VARCHAR(20)
> > >> > DECLARE @.LNAME AS VARCHAR(20)
> > >> >
> > >> > CREATE TABLE #LIST
> > >> > (
> > >> > LogicalName sysname NOT NULL,
> > >> > PhysicalName varchar(255) NOT NULL,
> > >> > Type char(1) NOT NULL,
> > >> > FileGroupName sysname NULL,
> > >> > Size bigint NOT NULL,
> > >> > MaxSize bigint NOT NULL
> > >> > )
> > >> > INSERT INTO #LIST
> > >> > EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> > >> >
> > >> > SELECT @.DNAME =(
> > >> > SELECT LogicalName
> > >> > FROM #LIST
> > >> > WHERE type = 'D')
> > >> > SELECT @.LNAME =(
> > >> > SELECT LogicalName
> > >> > FROM #LIST
> > >> > WHERE type = 'L')
> > >> >
> > >> > RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> > >> >
> > >> > USE MASTER
> > >> >
> > >> > SELECT @.LNAME
> > >> > SELECT @.DNAME
> > >> >
> > >> > RESTORE DATABASE MYDB1
> > >> > FROM DISK = 'E:\backup.bak'
> > >> > WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> > >> > MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> > >> > REPLACE
> > >> > **********************************
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

Monday, March 26, 2012

Restore to new server

Friday, March 23, 2012

Restore SQL2000 database using vbscript

Restore SQL Server to default SQL data directory

Wednesday, March 21, 2012

Restore SQL DB with correct logical file names

Tuesday, March 20, 2012

Restore script restoring to old version of the database

Restore script restoring to old version of the database

Restore script restoring to old version of the database

Restore script does not seem to work

Restore script does not seem to work

Restore script does not seem to work

Restore script

Restore script

Restore script

Restore script

restore master database

Blog Archive

About Me