Hi All,
I'm sure there's a simple answer for this but I haven't found anything so far that gives me the info I'm needing, so if anyone could help, I'd be grateful!
I've been given mdf and ldf files from an existing off-site database to be "restored" locally (sqlserver 7). Usually I restore from a backup file, so I haven't had any experience with this scenario.
I've found info regarding sp_attach...if I create a new database locally and run sp_attach to point to the mdf and ldf files, will this work?
Many thanks in advance for any suggestions!
Cheers,
Megan:rolleyes:
Have answered my own question and for anyone who might have the same issue, yes it can be done by sp_attach_db very easily.
This will create a new database, and you just need to configure your users (sp_change_users_login), etc, again when it's attached.
Easy as!
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Friday, March 30, 2012
RESTORE with RECOVERY and REPLACE?
Thanks to those who responded earlier. If someone would check my commands
below, I would appreciate it.
Again, what I tried to do is create a new database from an existing template
database. To do this, I tried (in Enterprise Manager) to restore from the
template to a new database name. However, something went wrong and at the
end of the restore process I got an error about "log begins at 30000 and is
too late to apply to database." The new database is stuck with a (loading)
next to it.
So I am going to try to do it in Query AnalyzeR with the RECOVERY option.
After trying to decipher the books online, this is what I came up with:
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH RECOVERY, REPLACE
Thank youJust to be clear, I am not restoring to a different machine. Just trying to
create a new database (which is not hanging from my first attempt) from an
existing database.
Tahnks|||Actually, it seems that RECOVERY is the default, so maybe I don't need to
specify it.
A better command might be
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH REPLACE|||Well, that did not work. It says no entry in sysdevices for
'c:\mybackups\template.bak'|||Tried adding DISK and putting a single quote around the path. Seems to have
worked.
Thanks!
> RESTORE DATABASE stuckdb
> FROM DISK = 'c:\mybackups\template.bak'
> WITH REPLACE|||"mike" <mike@.commmcasssttt.com> wrote in message
news:12a7td2rqcpoo7d@.corp.supernews.com...
> Well, that did not work. It says no entry in sysdevices for
> 'c:\mybackups\template.bak'
>
below is a script that you can adapt for your own purposes. You really need
to read BOL for the commands involved to make sure you understand exactly
what happens. BOL also has many useful examples. To restore to a new
database from a backup of an existing database (the template in your
description), just use a new database name in the restore command ("test_db"
in this example) and be sure to specify the files you want to use for the
database (the move options). The 2nd command is useful to identify the
logical names (used by the database in the backup) that need to be moved.
use master
go
exec xp_cmdshell 'dir C:\sql2k\MSSQL\BACKUP\ /o-d'
go
RESTORE FILELISTONLY
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
GO
RESTORE DATABASE test_db
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
WITH RECOVERY, STATS, REPLACE,
MOVE 'main_Data' TO 'C:\sql2k\MSSQL\DATA\test_db_DATA.mdf',
MOVE 'main_Log' TO 'C:\sql2k\MSSQL\DATA\test_db_Log.ldf'
GOsql
below, I would appreciate it.
Again, what I tried to do is create a new database from an existing template
database. To do this, I tried (in Enterprise Manager) to restore from the
template to a new database name. However, something went wrong and at the
end of the restore process I got an error about "log begins at 30000 and is
too late to apply to database." The new database is stuck with a (loading)
next to it.
So I am going to try to do it in Query AnalyzeR with the RECOVERY option.
After trying to decipher the books online, this is what I came up with:
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH RECOVERY, REPLACE
Thank youJust to be clear, I am not restoring to a different machine. Just trying to
create a new database (which is not hanging from my first attempt) from an
existing database.
Tahnks|||Actually, it seems that RECOVERY is the default, so maybe I don't need to
specify it.
A better command might be
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH REPLACE|||Well, that did not work. It says no entry in sysdevices for
'c:\mybackups\template.bak'|||Tried adding DISK and putting a single quote around the path. Seems to have
worked.
Thanks!
> RESTORE DATABASE stuckdb
> FROM DISK = 'c:\mybackups\template.bak'
> WITH REPLACE|||"mike" <mike@.commmcasssttt.com> wrote in message
news:12a7td2rqcpoo7d@.corp.supernews.com...
> Well, that did not work. It says no entry in sysdevices for
> 'c:\mybackups\template.bak'
>
below is a script that you can adapt for your own purposes. You really need
to read BOL for the commands involved to make sure you understand exactly
what happens. BOL also has many useful examples. To restore to a new
database from a backup of an existing database (the template in your
description), just use a new database name in the restore command ("test_db"
in this example) and be sure to specify the files you want to use for the
database (the move options). The 2nd command is useful to identify the
logical names (used by the database in the backup) that need to be moved.
use master
go
exec xp_cmdshell 'dir C:\sql2k\MSSQL\BACKUP\ /o-d'
go
RESTORE FILELISTONLY
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
GO
RESTORE DATABASE test_db
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
WITH RECOVERY, STATS, REPLACE,
MOVE 'main_Data' TO 'C:\sql2k\MSSQL\DATA\test_db_DATA.mdf',
MOVE 'main_Log' TO 'C:\sql2k\MSSQL\DATA\test_db_Log.ldf'
GOsql
Wednesday, March 28, 2012
Restore transaction log?????
Hi all,
I had been made full backup, differential backup, transaction log backup. I want to create new database from these file backup.
I did it follow:
1. Create new database from full backup file, it is ok. then
2. restore this database use transaction log backup file, an message raise :
"The proceding restore operation did not specify WITH NORECOVERY or WITH STANBY. Restart the restore sequence, specfying WITH NORECOVERY or WITH SATNBY for all but the final step... "
thank for reading.if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say :)|||Originally posted by ms_sql_dba
if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say :)
thank you,
i do follow your guide, but it does not work. please show me again
best regard.|||You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.|||Originally posted by rhigdon
You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.
Thanks, but i can not understan what you mean, please show me.
Best regard|||Do you have BOL installed? This is from BOL - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
Try this script to illustrate:
--Add devices
USE master
EXEC sp_addumpdevice 'disk', 'MyFull',
'c:\myfull.dat'
EXEC sp_addumpdevice 'disk', 'MyDiff',
'c:\MyDiff.dat'
EXEC sp_addumpdevice 'disk', 'MyLog',
'c:\MyLog.dat'
--create database
create database blah
--set recovery model
alter database blah set recovery full
--do our backups
backup database blah to MyFull
use blah
create table afterfull(ident int identity(1,1))
BACKUP DATABASE blah TO MyDiff WITH DIFFERENTIAL
use blah
drop table afterfull
create table afterdiff (ident int identity(1,1))
backup log blah to MyLog
--Now start our restores
use master
RESTORE DATABASE blah
FROM MyFull
--The above works, this was the full backup
RESTORE DATABASE blah
FROM MyDiff
--The above does not work as you did the full restore without specifying "with recovery"
RESTORE DATABASE blah
FROM MyFull
with norecovery
RESTORE DATABASE blah
FROM MyDiff
--the above works as we specified norecovery with the full restore
--now try the tran log
RESTORE log blah
FROM MyLog
--Look familiar? We need to use the NORECOVERY with the full and DIFF so we can apply the log backup
RESTORE DATABASE blah
FROM MyFull
with norecovery
RESTORE DATABASE blah
FROM MyDiff
with norecovery
RESTORE log blah
FROM MyLog
--Voila!|||1. Create New Database
2. right click all task and restore database
3. tab options at Recovery completion state select "Leave database read-only and able to restore additional transaction logs"
I had been made full backup, differential backup, transaction log backup. I want to create new database from these file backup.
I did it follow:
1. Create new database from full backup file, it is ok. then
2. restore this database use transaction log backup file, an message raise :
"The proceding restore operation did not specify WITH NORECOVERY or WITH STANBY. Restart the restore sequence, specfying WITH NORECOVERY or WITH SATNBY for all but the final step... "
thank for reading.if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say :)|||Originally posted by ms_sql_dba
if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say :)
thank you,
i do follow your guide, but it does not work. please show me again
best regard.|||You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.|||Originally posted by rhigdon
You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.
Thanks, but i can not understan what you mean, please show me.
Best regard|||Do you have BOL installed? This is from BOL - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
Try this script to illustrate:
--Add devices
USE master
EXEC sp_addumpdevice 'disk', 'MyFull',
'c:\myfull.dat'
EXEC sp_addumpdevice 'disk', 'MyDiff',
'c:\MyDiff.dat'
EXEC sp_addumpdevice 'disk', 'MyLog',
'c:\MyLog.dat'
--create database
create database blah
--set recovery model
alter database blah set recovery full
--do our backups
backup database blah to MyFull
use blah
create table afterfull(ident int identity(1,1))
BACKUP DATABASE blah TO MyDiff WITH DIFFERENTIAL
use blah
drop table afterfull
create table afterdiff (ident int identity(1,1))
backup log blah to MyLog
--Now start our restores
use master
RESTORE DATABASE blah
FROM MyFull
--The above works, this was the full backup
RESTORE DATABASE blah
FROM MyDiff
--The above does not work as you did the full restore without specifying "with recovery"
RESTORE DATABASE blah
FROM MyFull
with norecovery
RESTORE DATABASE blah
FROM MyDiff
--the above works as we specified norecovery with the full restore
--now try the tran log
RESTORE log blah
FROM MyLog
--Look familiar? We need to use the NORECOVERY with the full and DIFF so we can apply the log backup
RESTORE DATABASE blah
FROM MyFull
with norecovery
RESTORE DATABASE blah
FROM MyDiff
with norecovery
RESTORE log blah
FROM MyLog
--Voila!|||1. Create New Database
2. right click all task and restore database
3. tab options at Recovery completion state select "Leave database read-only and able to restore additional transaction logs"
Monday, March 26, 2012
restore to new db restores less data than what is in the source backup file
I am trying to create sql code that restores a backup of a master database to a new database on the same server. It seems to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with MODTRNMaster
The databases which are created from the restores start with M1_ and M2_. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster
to disk = 'f:\bkup\MODTRNMaster.bak'
backup database MODTRNMaster_IMG
to disk = 'f:\bkup\MODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC
to disk = 'f:\bkup\MODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM
to disk = 'f:\bkup\MODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. Im hoping that there is just something simple that Im overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master
go
drop database M1_MSLH
go
restore database M1_MSLH
from disk = 'f:\bkup\MODTRNMaster.bak'
with move 'DEV5_Data' to 'f:\mssql\data\M1_MLSH.mdf',
move 'MM' to 'f:\mssql\data\M1_MLSH_1.mdf',
move 'AMB' to 'f:\mssql\data\M1_MLSH_2.mdf',
move 'DM' to 'f:\mssql\data\M1_MLSH_3.mdf',
move 'IMM' to 'f:\mssql\data\M1_MLSH_4.mdf',
move 'ED' to 'f:\mssql\data\M1_MLSH_5.mdf',
move 'DEV5_Log' to 'f:\mssql\log\M1_MLSH_log.ldf',
recovery
go
Thanks in advance
:eek:
:eek:that surely sounds like some magic. I do not know off the the top of my head what the problem could be but you should only have one mdf file and the rest of your data files should be ndfs.|||I am trying to create sql code that restores a backup of a master database to a new database on the same server. It seems to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Are you sure that the master database is in full recovery mode? I did not think that was possible and/or mattered.
Also, even if it is in full recovery, your restore script indicated that you had restored only the last full backup (.BAK) file and had not restored any transaction logs (.TRN). Without restoring the transaction logs, you will only restore the database to the point in time when the last full backup completed; any transacations performed after that will not get restored.
Finally, I have to ask: why are you restoring the master database? I'm not sure that doing so gets you anything (except maybe practice).
Regards,
hmscott|||sorry. I should have been a little clearer. :o It is not actually THE master database that I'm backing up. We are having some training sessions for users of a new system. The trainer makes his updates in a database named MODTRNMaster (the master database for our purposes). At night I am restoring into 2 individual training databases M1_MLSH and M2_MLSH that the users are accessing.
No transaction log backups are being done. These databases are only being accessed during the day. At night there is no activity in those databases. At night I backup MODTRNMaster and then immediately (attempt to) restore the backup file as M1_MLSH and then again as M2_MLSH.|||You need to add with init to your backup commands, or with file = # to the restore commands. By default, SQL Server will append your backup to the exisiting file (you may have noticed them getting bigger), and restore from the first backup found. Do this and get back to us with the results:
restore headeronly from disk = 'f:\bkup\MODTRNMaster.bak'|||Thanks So much for your suggestion! Adding the "init" parameter to the backup database statement was exactly what was needed!! :D
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with MODTRNMaster
The databases which are created from the restores start with M1_ and M2_. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster
to disk = 'f:\bkup\MODTRNMaster.bak'
backup database MODTRNMaster_IMG
to disk = 'f:\bkup\MODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC
to disk = 'f:\bkup\MODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM
to disk = 'f:\bkup\MODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. Im hoping that there is just something simple that Im overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master
go
drop database M1_MSLH
go
restore database M1_MSLH
from disk = 'f:\bkup\MODTRNMaster.bak'
with move 'DEV5_Data' to 'f:\mssql\data\M1_MLSH.mdf',
move 'MM' to 'f:\mssql\data\M1_MLSH_1.mdf',
move 'AMB' to 'f:\mssql\data\M1_MLSH_2.mdf',
move 'DM' to 'f:\mssql\data\M1_MLSH_3.mdf',
move 'IMM' to 'f:\mssql\data\M1_MLSH_4.mdf',
move 'ED' to 'f:\mssql\data\M1_MLSH_5.mdf',
move 'DEV5_Log' to 'f:\mssql\log\M1_MLSH_log.ldf',
recovery
go
Thanks in advance
:eek:
:eek:that surely sounds like some magic. I do not know off the the top of my head what the problem could be but you should only have one mdf file and the rest of your data files should be ndfs.|||I am trying to create sql code that restores a backup of a master database to a new database on the same server. It seems to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Are you sure that the master database is in full recovery mode? I did not think that was possible and/or mattered.
Also, even if it is in full recovery, your restore script indicated that you had restored only the last full backup (.BAK) file and had not restored any transaction logs (.TRN). Without restoring the transaction logs, you will only restore the database to the point in time when the last full backup completed; any transacations performed after that will not get restored.
Finally, I have to ask: why are you restoring the master database? I'm not sure that doing so gets you anything (except maybe practice).
Regards,
hmscott|||sorry. I should have been a little clearer. :o It is not actually THE master database that I'm backing up. We are having some training sessions for users of a new system. The trainer makes his updates in a database named MODTRNMaster (the master database for our purposes). At night I am restoring into 2 individual training databases M1_MLSH and M2_MLSH that the users are accessing.
No transaction log backups are being done. These databases are only being accessed during the day. At night there is no activity in those databases. At night I backup MODTRNMaster and then immediately (attempt to) restore the backup file as M1_MLSH and then again as M2_MLSH.|||You need to add with init to your backup commands, or with file = # to the restore commands. By default, SQL Server will append your backup to the exisiting file (you may have noticed them getting bigger), and restore from the first backup found. Do this and get back to us with the results:
restore headeronly from disk = 'f:\bkup\MODTRNMaster.bak'|||Thanks So much for your suggestion! Adding the "init" parameter to the backup database statement was exactly what was needed!! :D
Friday, March 23, 2012
Restore takes forever..
Okay, I understand that when I'm restoring a database that
it needs some time to "create" the empty database before
it starts populating the data, but this is ridiculous...
I have an approximately 60gb database (with separate log
and data drives) that I backup up (to the data drive) in
22 minutes.
I'm now trying to restore that database and it has so far
literally taken 4 hours and the restore progress bar (in
EM) hasn't even started moving yet. Checking current
activity I can see that the restore process is "Waiting
For ASYNC_IO_COMPLETION".
Now I know that my disk configuration is okay because SQL
was able to read the enter 60gb database and write it out
to a backup file in 22 minutes. Why in the world would
it take so long to restore?
My experience has been that this initial part of the
restore (where my guess is that it is creating the "blank"
database) takes forever and then once the progress bars
start it goes really quick.
What is making this take so long? How can I back
something up and then try to restore and it literally take
10 or 20 times as long?
Any help would be greatly appreciated!
thanks!
-dave
P.S. as a postscript... how come apps like SQL and
Exchange can write out 60gb backup files in a matter of
minutes but if I try to copy (not move) a 60gb file from
one directory on a drive to another directory on the same
drive it takes 2 hours? What do Exchange/SQL do that
makes disk access so fast and how can we make is that fast
for normal file copy operations?Dave,
From what I understand, it goes a little something like this...
When you restore, SQL Server has to initialize all pages in the database.
Then, it has to put the data back on _exactly_ those same pages.
If it did not do this, then the internal list linking strategy would be
hosed.
You might consider not having the backup device on the same disk system
during the restore, or you will be stepping all over yourself with
read/write ops, and waste a lot of time thrashing.
James Hokes
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:92a801c3eab8$3eb2c090$a301280a@.phx.gbl...
it needs some time to "create" the empty database before
it starts populating the data, but this is ridiculous...
I have an approximately 60gb database (with separate log
and data drives) that I backup up (to the data drive) in
22 minutes.
I'm now trying to restore that database and it has so far
literally taken 4 hours and the restore progress bar (in
EM) hasn't even started moving yet. Checking current
activity I can see that the restore process is "Waiting
For ASYNC_IO_COMPLETION".
Now I know that my disk configuration is okay because SQL
was able to read the enter 60gb database and write it out
to a backup file in 22 minutes. Why in the world would
it take so long to restore?
My experience has been that this initial part of the
restore (where my guess is that it is creating the "blank"
database) takes forever and then once the progress bars
start it goes really quick.
What is making this take so long? How can I back
something up and then try to restore and it literally take
10 or 20 times as long?
Any help would be greatly appreciated!
thanks!
-dave
P.S. as a postscript... how come apps like SQL and
Exchange can write out 60gb backup files in a matter of
minutes but if I try to copy (not move) a 60gb file from
one directory on a drive to another directory on the same
drive it takes 2 hours? What do Exchange/SQL do that
makes disk access so fast and how can we make is that fast
for normal file copy operations?Dave,
From what I understand, it goes a little something like this...
When you restore, SQL Server has to initialize all pages in the database.
Then, it has to put the data back on _exactly_ those same pages.
If it did not do this, then the internal list linking strategy would be
hosed.
You might consider not having the backup device on the same disk system
during the restore, or you will be stepping all over yourself with
read/write ops, and waste a lot of time thrashing.
James Hokes
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:92a801c3eab8$3eb2c090$a301280a@.phx.gbl...
quote:
> Okay, I understand that when I'm restoring a database that
> it needs some time to "create" the empty database before
> it starts populating the data, but this is ridiculous...
> I have an approximately 60gb database (with separate log
> and data drives) that I backup up (to the data drive) in
> 22 minutes.
> I'm now trying to restore that database and it has so far
> literally taken 4 hours and the restore progress bar (in
> EM) hasn't even started moving yet. Checking current
> activity I can see that the restore process is "Waiting
> For ASYNC_IO_COMPLETION".
> Now I know that my disk configuration is okay because SQL
> was able to read the enter 60gb database and write it out
> to a backup file in 22 minutes. Why in the world would
> it take so long to restore?
> My experience has been that this initial part of the
> restore (where my guess is that it is creating the "blank"
> database) takes forever and then once the progress bars
> start it goes really quick.
> What is making this take so long? How can I back
> something up and then try to restore and it literally take
> 10 or 20 times as long?
> Any help would be greatly appreciated!
> thanks!
> -dave
> P.S. as a postscript... how come apps like SQL and
> Exchange can write out 60gb backup files in a matter of
> minutes but if I try to copy (not move) a 60gb file from
> one directory on a drive to another directory on the same
> drive it takes 2 hours? What do Exchange/SQL do that
> makes disk access so fast and how can we make is that fast
> for normal file copy operations?
Restore takes forever..
Okay, I understand that when I'm restoring a database that
it needs some time to "create" the empty database before
it starts populating the data, but this is ridiculous...
I have an approximately 60gb database (with separate log
and data drives) that I backup up (to the data drive) in
22 minutes.
I'm now trying to restore that database and it has so far
literally taken 4 hours and the restore progress bar (in
EM) hasn't even started moving yet. Checking current
activity I can see that the restore process is "Waiting
For ASYNC_IO_COMPLETION".
Now I know that my disk configuration is okay because SQL
was able to read the enter 60gb database and write it out
to a backup file in 22 minutes. Why in the world would
it take so long to restore?
My experience has been that this initial part of the
restore (where my guess is that it is creating the "blank"
database) takes forever and then once the progress bars
start it goes really quick.
What is making this take so long? How can I back
something up and then try to restore and it literally take
10 or 20 times as long?
Any help would be greatly appreciated!
thanks!
-dave
P.S. as a postscript... how come apps like SQL and
Exchange can write out 60gb backup files in a matter of
minutes but if I try to copy (not move) a 60gb file from
one directory on a drive to another directory on the same
drive it takes 2 hours? What do Exchange/SQL do that
makes disk access so fast and how can we make is that fast
for normal file copy operations?Dave,
From what I understand, it goes a little something like this...
When you restore, SQL Server has to initialize all pages in the database.
Then, it has to put the data back on _exactly_ those same pages.
If it did not do this, then the internal list linking strategy would be
hosed.
You might consider not having the backup device on the same disk system
during the restore, or you will be stepping all over yourself with
read/write ops, and waste a lot of time thrashing.
James Hokes
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:92a801c3eab8$3eb2c090$a301280a@.phx.gbl...
> Okay, I understand that when I'm restoring a database that
> it needs some time to "create" the empty database before
> it starts populating the data, but this is ridiculous...
> I have an approximately 60gb database (with separate log
> and data drives) that I backup up (to the data drive) in
> 22 minutes.
> I'm now trying to restore that database and it has so far
> literally taken 4 hours and the restore progress bar (in
> EM) hasn't even started moving yet. Checking current
> activity I can see that the restore process is "Waiting
> For ASYNC_IO_COMPLETION".
> Now I know that my disk configuration is okay because SQL
> was able to read the enter 60gb database and write it out
> to a backup file in 22 minutes. Why in the world would
> it take so long to restore?
> My experience has been that this initial part of the
> restore (where my guess is that it is creating the "blank"
> database) takes forever and then once the progress bars
> start it goes really quick.
> What is making this take so long? How can I back
> something up and then try to restore and it literally take
> 10 or 20 times as long?
> Any help would be greatly appreciated!
> thanks!
> -dave
> P.S. as a postscript... how come apps like SQL and
> Exchange can write out 60gb backup files in a matter of
> minutes but if I try to copy (not move) a 60gb file from
> one directory on a drive to another directory on the same
> drive it takes 2 hours? What do Exchange/SQL do that
> makes disk access so fast and how can we make is that fast
> for normal file copy operations?
it needs some time to "create" the empty database before
it starts populating the data, but this is ridiculous...
I have an approximately 60gb database (with separate log
and data drives) that I backup up (to the data drive) in
22 minutes.
I'm now trying to restore that database and it has so far
literally taken 4 hours and the restore progress bar (in
EM) hasn't even started moving yet. Checking current
activity I can see that the restore process is "Waiting
For ASYNC_IO_COMPLETION".
Now I know that my disk configuration is okay because SQL
was able to read the enter 60gb database and write it out
to a backup file in 22 minutes. Why in the world would
it take so long to restore?
My experience has been that this initial part of the
restore (where my guess is that it is creating the "blank"
database) takes forever and then once the progress bars
start it goes really quick.
What is making this take so long? How can I back
something up and then try to restore and it literally take
10 or 20 times as long?
Any help would be greatly appreciated!
thanks!
-dave
P.S. as a postscript... how come apps like SQL and
Exchange can write out 60gb backup files in a matter of
minutes but if I try to copy (not move) a 60gb file from
one directory on a drive to another directory on the same
drive it takes 2 hours? What do Exchange/SQL do that
makes disk access so fast and how can we make is that fast
for normal file copy operations?Dave,
From what I understand, it goes a little something like this...
When you restore, SQL Server has to initialize all pages in the database.
Then, it has to put the data back on _exactly_ those same pages.
If it did not do this, then the internal list linking strategy would be
hosed.
You might consider not having the backup device on the same disk system
during the restore, or you will be stepping all over yourself with
read/write ops, and waste a lot of time thrashing.
James Hokes
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:92a801c3eab8$3eb2c090$a301280a@.phx.gbl...
> Okay, I understand that when I'm restoring a database that
> it needs some time to "create" the empty database before
> it starts populating the data, but this is ridiculous...
> I have an approximately 60gb database (with separate log
> and data drives) that I backup up (to the data drive) in
> 22 minutes.
> I'm now trying to restore that database and it has so far
> literally taken 4 hours and the restore progress bar (in
> EM) hasn't even started moving yet. Checking current
> activity I can see that the restore process is "Waiting
> For ASYNC_IO_COMPLETION".
> Now I know that my disk configuration is okay because SQL
> was able to read the enter 60gb database and write it out
> to a backup file in 22 minutes. Why in the world would
> it take so long to restore?
> My experience has been that this initial part of the
> restore (where my guess is that it is creating the "blank"
> database) takes forever and then once the progress bars
> start it goes really quick.
> What is making this take so long? How can I back
> something up and then try to restore and it literally take
> 10 or 20 times as long?
> Any help would be greatly appreciated!
> thanks!
> -dave
> P.S. as a postscript... how come apps like SQL and
> Exchange can write out 60gb backup files in a matter of
> minutes but if I try to copy (not move) a 60gb file from
> one directory on a drive to another directory on the same
> drive it takes 2 hours? What do Exchange/SQL do that
> makes disk access so fast and how can we make is that fast
> for normal file copy operations?
Monday, March 12, 2012
Restore problems.
Hi,
I need to restore a database from a server to another, but all tables are not dbo's tables.
I first create the user on the destination instance, and after restoring datas, I try to change rights for this user.
I can't delete or create new rights for this user because it own tables.
How can I restore my users rights database ?If you don't mind to change the owner to DBO (which is how it should have been) you can use sp_changeobjectowner procedure, without having to create the user.|||At this point, I'd probably use sp_change_users_login (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp).
-PatP|||sp_change_users_login will map SID's, but will not allow to do what the poster asked for.|||At least as I read the question, the poster created a login named "foo" because there was a user in the database they were restoring that was already named "foo". If that is the case, and they were expecting their login to have access to the database, then sp_change_users_login would fix the SID values, giving the "foo" login on this server equivalent permissions in this database to what the "foo" login on the original server had.
Once the poster fixes the underlying SID problem, they can then grant or revoke user permissions as they see fit.
Do you think that I missed something?
-PatP|||As I said earlier, if you use sp_changeobjectowner, you don't even need to create the "foo" login to begin with.|||Thanks for all opf us.
I succeed in my problem with sp_changeobjectowner procedure.|||RDJabarov is the man
I need to restore a database from a server to another, but all tables are not dbo's tables.
I first create the user on the destination instance, and after restoring datas, I try to change rights for this user.
I can't delete or create new rights for this user because it own tables.
How can I restore my users rights database ?If you don't mind to change the owner to DBO (which is how it should have been) you can use sp_changeobjectowner procedure, without having to create the user.|||At this point, I'd probably use sp_change_users_login (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp).
-PatP|||sp_change_users_login will map SID's, but will not allow to do what the poster asked for.|||At least as I read the question, the poster created a login named "foo" because there was a user in the database they were restoring that was already named "foo". If that is the case, and they were expecting their login to have access to the database, then sp_change_users_login would fix the SID values, giving the "foo" login on this server equivalent permissions in this database to what the "foo" login on the original server had.
Once the poster fixes the underlying SID problem, they can then grant or revoke user permissions as they see fit.
Do you think that I missed something?
-PatP|||As I said earlier, if you use sp_changeobjectowner, you don't even need to create the "foo" login to begin with.|||Thanks for all opf us.
I succeed in my problem with sp_changeobjectowner procedure.|||RDJabarov is the man
restore problems
I have a db on msde on one server.
I"d like to replicate it by restoring a back up on another PC with a newly
installed MSDE.
I create a shell of the DB
I create a user name which my application uses to login with SQL
authentication.
I give the user db access to the empty-shell to be filled in by the backup
I proceed with the restore.
After the restore is done, the user I created now loses the access to the
db.
When I try to reset the dbaccess for the user, I get an error that says the
user already exists,
This is most liklely because that user name is the owner of some of the
tables in restored DB.
Any ideas as to how to do a restore correctly?
Many Thanks in advance
ps.. I've tried sql-copy and that was a teerrible disatster as I got olst
figuring out what the heck a service account is.
hi Ernesto,
"Ernesto" <tsh@.mathematicuslabs.com> ha scritto nel messaggio
news:iqOdnZtH1JOec2PdRVn-rA@.speakeasy.net...
> I have a db on msde on one server.
> I"d like to replicate it by restoring a back up on another PC with a newly
> installed MSDE.
> I create a shell of the DB
> I create a user name which my application uses to login with SQL
> authentication.
> I give the user db access to the empty-shell to be filled in by the backup
> I proceed with the restore.
> After the restore is done, the user I created now loses the access to the
> db.
> When I try to reset the dbaccess for the user, I get an error that says
the
> user already exists,
> This is most liklely because that user name is the owner of some of the
> tables in restored DB.
> Any ideas as to how to do a restore correctly?
> Many Thanks in advance
> ps.. I've tried sql-copy and that was a teerrible disatster as I got olst
> figuring out what the heck a service account is.
>
if the "user" shoul'd be the database owner as well, do not make the
corresponing login a dbuser by granting him access to the db..
just restore the db and change the db ownership to your login , which will
automatically grant him access and ownership rights...
EXEC sp_changedbowner 'login' , 'true'
please see
http://msdn.microsoft.com/library/de...ca-cz_30s2.asp
for it's related synopsis and further info
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I guess I shoudl have mentioned that the problem SEEMs to be that the
created user mysteriously loses its "login" name after the restore.
I found out that the query:
sp_change_users_login 'auto_fix', 'username'
Fixed the problem.
I've looked at sp_changedbowner and it just changes the owner of the db. It
does not fix the user problem.
sp_changedbowner accepts a login name as the parameter, and the problem was
that hhuser did not have a login name after the restore.
Now if I only could find a smoother way of doing this whole restore thing
without all the hassle of creating an empty db, creatina login, restoring,
and fixing the login!!
Sure looks like this ms product has a long way to go before it is user
froiendly even in its more common aspects like restoring a legitimate
backup.
|||hi Ernesto,
"Ernesto" <tsh@.mathematicuslabs.com> ha scritto nel messaggio
news:hc6dndtw6OTxRGLdRVn-hQ@.speakeasy.net...
> I guess I shoudl have mentioned that the problem SEEMs to be that the
> created user mysteriously loses its "login" name after the restore.
> I found out that the query:
> sp_change_users_login 'auto_fix', 'username'
> Fixed the problem.
>
> I've looked at sp_changedbowner and it just changes the owner of the db.
It
> does not fix the user problem.
> sp_changedbowner accepts a login name as the parameter, and the problem
was
> that hhuser did not have a login name after the restore.
> Now if I only could find a smoother way of doing this whole restore thing
> without all the hassle of creating an empty db, creatina login, restoring,
> and fixing the login!!
> Sure looks like this ms product has a long way to go before it is user
> froiendly even in its more common aspects like restoring a legitimate
> backup.
as regard this last sentence, my opinion is not Microsoft "has a long way to
go.." but just us understand how it works..
MSDE/SQL Server is NOT JET, and we have to take care understanding ith'
behaviours..
anyway,
http://www.sqlservercentral.com/colu...okenlogins.asp
Neil Boyle article about this related issue is worth reading, IMHO
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
I"d like to replicate it by restoring a back up on another PC with a newly
installed MSDE.
I create a shell of the DB
I create a user name which my application uses to login with SQL
authentication.
I give the user db access to the empty-shell to be filled in by the backup
I proceed with the restore.
After the restore is done, the user I created now loses the access to the
db.
When I try to reset the dbaccess for the user, I get an error that says the
user already exists,
This is most liklely because that user name is the owner of some of the
tables in restored DB.
Any ideas as to how to do a restore correctly?
Many Thanks in advance
ps.. I've tried sql-copy and that was a teerrible disatster as I got olst
figuring out what the heck a service account is.
hi Ernesto,
"Ernesto" <tsh@.mathematicuslabs.com> ha scritto nel messaggio
news:iqOdnZtH1JOec2PdRVn-rA@.speakeasy.net...
> I have a db on msde on one server.
> I"d like to replicate it by restoring a back up on another PC with a newly
> installed MSDE.
> I create a shell of the DB
> I create a user name which my application uses to login with SQL
> authentication.
> I give the user db access to the empty-shell to be filled in by the backup
> I proceed with the restore.
> After the restore is done, the user I created now loses the access to the
> db.
> When I try to reset the dbaccess for the user, I get an error that says
the
> user already exists,
> This is most liklely because that user name is the owner of some of the
> tables in restored DB.
> Any ideas as to how to do a restore correctly?
> Many Thanks in advance
> ps.. I've tried sql-copy and that was a teerrible disatster as I got olst
> figuring out what the heck a service account is.
>
if the "user" shoul'd be the database owner as well, do not make the
corresponing login a dbuser by granting him access to the db..
just restore the db and change the db ownership to your login , which will
automatically grant him access and ownership rights...
EXEC sp_changedbowner 'login' , 'true'
please see
http://msdn.microsoft.com/library/de...ca-cz_30s2.asp
for it's related synopsis and further info
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I guess I shoudl have mentioned that the problem SEEMs to be that the
created user mysteriously loses its "login" name after the restore.
I found out that the query:
sp_change_users_login 'auto_fix', 'username'
Fixed the problem.
I've looked at sp_changedbowner and it just changes the owner of the db. It
does not fix the user problem.
sp_changedbowner accepts a login name as the parameter, and the problem was
that hhuser did not have a login name after the restore.
Now if I only could find a smoother way of doing this whole restore thing
without all the hassle of creating an empty db, creatina login, restoring,
and fixing the login!!
Sure looks like this ms product has a long way to go before it is user
froiendly even in its more common aspects like restoring a legitimate
backup.
|||hi Ernesto,
"Ernesto" <tsh@.mathematicuslabs.com> ha scritto nel messaggio
news:hc6dndtw6OTxRGLdRVn-hQ@.speakeasy.net...
> I guess I shoudl have mentioned that the problem SEEMs to be that the
> created user mysteriously loses its "login" name after the restore.
> I found out that the query:
> sp_change_users_login 'auto_fix', 'username'
> Fixed the problem.
>
> I've looked at sp_changedbowner and it just changes the owner of the db.
It
> does not fix the user problem.
> sp_changedbowner accepts a login name as the parameter, and the problem
was
> that hhuser did not have a login name after the restore.
> Now if I only could find a smoother way of doing this whole restore thing
> without all the hassle of creating an empty db, creatina login, restoring,
> and fixing the login!!
> Sure looks like this ms product has a long way to go before it is user
> froiendly even in its more common aspects like restoring a legitimate
> backup.
as regard this last sentence, my opinion is not Microsoft "has a long way to
go.." but just us understand how it works..
MSDE/SQL Server is NOT JET, and we have to take care understanding ith'
behaviours..
anyway,
http://www.sqlservercentral.com/colu...okenlogins.asp
Neil Boyle article about this related issue is worth reading, IMHO
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Monday, February 20, 2012
Restore master database from 64bit sql server to 32 bit server
Hi,
We are planning to create a prod environment on our Dev server, by
creating a new named instance on DEV. Our prod server is running on
sql enterprise edition 64bit and dev on SQl enterprise 32bit.
Could we copy all the databases from prod server including
Master,msdb,model onto our dev server, and bring up all the databases?
Does Master store information about the version of SQl server?
We are planning to create a prod environment on our Dev server, by
creating a new named instance on DEV. Our prod server is running on
sql enterprise edition 64bit and dev on SQl enterprise 32bit.
Could we copy all the databases from prod server including
Master,msdb,model onto our dev server, and bring up all the databases?
Does Master store information about the version of SQl server?
Thanks for your help
GGIt would probably be best not to copy the system databases (except
perhaps model) - master and msdb have lots of information about the
instance, including name, backup history etc which could create
problems if you restore onto another instance. Typically, those
databases are only restored if they become corrupted, or as part of a
complete server recovery.
Simon
Subscribe to:
Posts (Atom)