Please excues my ignorance, but I am in no way a SQL guy; so here goes:
I have an accounting package that uses SQL 2000. SQL is creating a backup
file that is getting HUGE (about 2 GB)
The actual data is around a few hundred MB.
Is there a way to reduce the size of the backup file by:
Compacting?
Performaing a backup (that SQL knows about)?
Is there a way to move that file?
Can any of this be done on-line?
Thanks for any help.
Regards;
PBWelcome to SQL
Try doing the following at a time when the database has
few or no users.
Right Click on the Database and select Properties
Select Options Tab
Under Recovery - Model if its not set to simple change it
to simple.
Press OK
Right Click the database and select All Tasks -> Shrink
Database
Select the check box 'Move Pages to beginning...'
Press ok
Go make yourself a drink whilst your waiting ;)
And after if finishes change your 'Recovery - Model' back
to what it was prior to your change.
Peter
>--Original Message--
>Please excues my ignorance, but I am in no way a SQL guy;
so here goes:
>I have an accounting package that uses SQL 2000. SQL is
creating a backup
>file that is getting HUGE (about 2 GB)
>The actual data is around a few hundred MB.
>Is there a way to reduce the size of the backup file by:
>Compacting?
>Performaing a backup (that SQL knows about)?
>Is there a way to move that file?
>Can any of this be done on-line?
>Thanks for any help.
>Regards;
>PB
>
>.
>|||Please forgive my ignorance, but:
Where is the database? It appears that the app that uses SQL is pointing to
a file share on another server. The backup file (BAK) is local, and it is
killing me. As I said, it is 2 GB and growing.
If it is the BAK file that is huge; will that fix my problem? The database
itself is only 132 MB.
TIA;
PB
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:44e901c47fb8$3fa1b9d0$a401280a@.phx.gbl...
> Welcome to SQL
> Try doing the following at a time when the database has
> few or no users.
> Right Click on the Database and select Properties
> Select Options Tab
> Under Recovery - Model if its not set to simple change it
> to simple.
> Press OK
> Right Click the database and select All Tasks -> Shrink
> Database
> Select the check box 'Move Pages to beginning...'
> Press ok
> Go make yourself a drink whilst your waiting ;)
> And after if finishes change your 'Recovery - Model' back
> to what it was prior to your change.
> Peter
>
>
> >--Original Message--
> >Please excues my ignorance, but I am in no way a SQL guy;
> so here goes:
> >
> >I have an accounting package that uses SQL 2000. SQL is
> creating a backup
> >file that is getting HUGE (about 2 GB)
> >
> >The actual data is around a few hundred MB.
> >
> >Is there a way to reduce the size of the backup file by:
> >
> >Compacting?
> >Performaing a backup (that SQL knows about)?
> >
> >Is there a way to move that file?
> >
> >Can any of this be done on-line?
> >
> >Thanks for any help.
> >
> >Regards;
> >
> >PB
> >
> >
> >
> >.
> >|||When releasing space SQL Server doesn't actually free
disk space, its thinking is if its needed it before, it
needs it now, so if you say delete half your database you
will see the same disk space.
The problem though is probably to do with your log file
that will grows a heck of a lot.
To work out where your files are click onto your database
then at the top menu selecy view 'Something (I'm away
from SQL) pad'
It will give you the location and size of your data files
and log files.
These files can be anywhere on your Network.
As for your backup files (.BAK) its considered good
practice to store them on a different server than the one
that SQL is on.
And yes, performing the steps will solve the problem as
the steps will release the unused disk space and so the
backups will not be fooled into thinking there is more to
the files than there actually is.
Anyway its 22:15 here and I am off to do something else.
Good luck
Peter
Peter
>--Original Message--
>Please forgive my ignorance, but:
>Where is the database? It appears that the app that uses
SQL is pointing to
>a file share on another server. The backup file (BAK) is
local, and it is
>killing me. As I said, it is 2 GB and growing.
>If it is the BAK file that is huge; will that fix my
problem? The database
>itself is only 132 MB.
>TIA;
>PB
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:44e901c47fb8$3fa1b9d0$a401280a@.phx.gbl...
>> Welcome to SQL
>> Try doing the following at a time when the database has
>> few or no users.
>> Right Click on the Database and select Properties
>> Select Options Tab
>> Under Recovery - Model if its not set to simple change
it
>> to simple.
>> Press OK
>> Right Click the database and select All Tasks -> Shrink
>> Database
>> Select the check box 'Move Pages to beginning...'
>> Press ok
>> Go make yourself a drink whilst your waiting ;)
>> And after if finishes change your 'Recovery - Model'
back
>> to what it was prior to your change.
>> Peter
>>
>>
>> >--Original Message--
>> >Please excues my ignorance, but I am in no way a SQL
guy;
>> so here goes:
>> >
>> >I have an accounting package that uses SQL 2000. SQL
is
>> creating a backup
>> >file that is getting HUGE (about 2 GB)
>> >
>> >The actual data is around a few hundred MB.
>> >
>> >Is there a way to reduce the size of the backup file
by:
>> >
>> >Compacting?
>> >Performaing a backup (that SQL knows about)?
>> >
>> >Is there a way to move that file?
>> >
>> >Can any of this be done on-line?
>> >
>> >Thanks for any help.
>> >
>> >Regards;
>> >
>> >PB
>> >
>> >
>> >
>> >.
>> >
>
>.
>|||Are you appending each backup to your existing backup set?
If so then your backup file will continue to grow.
If you are using Enterprise Manager to do the backup,
check this:
R-click the database you want to back up.
Select 'All Tasks-->Backup Database' and in the 'General'
tab have a look at the 'Append to media' option - if this
is set then change it to check the 'Overwrite Existing
media' option instead.
Doing this will leave you with only the current backup so
you might want to set up a database maintenance plan
instead to handle your backups.
HTH
John
>--Original Message--
>Please excues my ignorance, but I am in no way a SQL guy;
so here goes:
>I have an accounting package that uses SQL 2000. SQL is
creating a backup
>file that is getting HUGE (about 2 GB)
>The actual data is around a few hundred MB.
>Is there a way to reduce the size of the backup file by:
>Compacting?
>Performaing a backup (that SQL knows about)?
>Is there a way to move that file?
>Can any of this be done on-line?
>Thanks for any help.
>Regards;
>PB
>
>.
>
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Friday, March 30, 2012
New to SQL - Backup Fails
Bear with me, as I am new to SQL and have quite simply been thrown into this situation.
I have set up a simple backup to DVD that fails. The log file is attached. I'm sure that there's an obvious answer here, but I don't know what to look for. Until we find a new database guy, I'm it.
Thansk so much in advance for any insight you can provide!
TomWhy to a DVD(?) Why not to disk? It's faster...
And if you're it (Like in tag?) then why not use the maintenance wizard?|||well, your errors relate to what you're asking in the first command, - set db to single user mode. if there is at least one more connection (which can be even yours0 the command will fail. you can write a simple script that would create a script that kills all connections to that database. fire that script before running sqlmaint and set a job step dependency "on success" with sqlmaint step.|||Just to be clear: ms_sql_dba is right; your job is failing because your database is not in single user mode. SQL backups can run with multiple users logged in, but in your case, the first task in your job is to "repair" the database.
I'm guessing that you are using the maintenance wizard; if so, skip the Integrity page on the maintenance wizard (make sure nothing is checked).
After doing that, your job should run okay.
Regards,
hmscott|||Thanks to all or you for your help.
Skipping the integrity page in the maintenance wizard cleared it up!
Hopefully I won't have to do this very long!
Regards,
Tom
I have set up a simple backup to DVD that fails. The log file is attached. I'm sure that there's an obvious answer here, but I don't know what to look for. Until we find a new database guy, I'm it.
Thansk so much in advance for any insight you can provide!
TomWhy to a DVD(?) Why not to disk? It's faster...
And if you're it (Like in tag?) then why not use the maintenance wizard?|||well, your errors relate to what you're asking in the first command, - set db to single user mode. if there is at least one more connection (which can be even yours0 the command will fail. you can write a simple script that would create a script that kills all connections to that database. fire that script before running sqlmaint and set a job step dependency "on success" with sqlmaint step.|||Just to be clear: ms_sql_dba is right; your job is failing because your database is not in single user mode. SQL backups can run with multiple users logged in, but in your case, the first task in your job is to "repair" the database.
I'm guessing that you are using the maintenance wizard; if so, skip the Integrity page on the maintenance wizard (make sure nothing is checked).
After doing that, your job should run okay.
Regards,
hmscott|||Thanks to all or you for your help.
Skipping the integrity page in the maintenance wizard cleared it up!
Hopefully I won't have to do this very long!
Regards,
Tom
Wednesday, March 28, 2012
New to MS SQL 2000
How do I backup databases, onto Veritas Backup? In the Enterprise Edition if
I expand the database server I could select all task, backup database and
select the destination.
"OSA" <OSA@.discussions.microsoft.com> wrote in message
news:58C5D3AA-D2FC-419E-B560-DD5703546B5A@.microsoft.com...
> How do I backup databases, onto Veritas Backup? In the Enterprise Edition
if
> I expand the database server I could select all task, backup database and
> select the destination.
If you've got the Veritas netbackup SQL Server agent, you can configure
netback to capture all relevant databases. If not, you can use the SQL
Server backup database task wizard to backup your databases, then use
netbackup to backup these files.
Steve
I expand the database server I could select all task, backup database and
select the destination.
"OSA" <OSA@.discussions.microsoft.com> wrote in message
news:58C5D3AA-D2FC-419E-B560-DD5703546B5A@.microsoft.com...
> How do I backup databases, onto Veritas Backup? In the Enterprise Edition
if
> I expand the database server I could select all task, backup database and
> select the destination.
If you've got the Veritas netbackup SQL Server agent, you can configure
netback to capture all relevant databases. If not, you can use the SQL
Server backup database task wizard to backup your databases, then use
netbackup to backup these files.
Steve
Friday, March 23, 2012
New SQL Server 2000 Install - Backup of Empty Database never compl
We installed SQL Server 2000 SP3 and created a number of databases. However
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimber
mikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimber
mikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
New SQL Server 2000 Install - Backup of Empty Database never compl
We installed SQL Server 2000 SP3 and created a number of databases. However
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimbermikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimbermikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
Monday, March 19, 2012
New Server and replication
Have a look at 'replication, backup and restore
operations' in BOL. Provided the new server and the old
server have the same name, this is possible. You'll need
to take copies of all the publisher/distributor databases
involved - the publishing database, MSDB, distribution
and master. BTW, prevent anyone from accessing the
publishing database and stop all replication jobs inc the
cleanup agent before starting the backup procedures.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks for your reply. The new server has the same name as of old. But
eventually I'll have to change the name of the old server since two machines
with same name cannot belong to the same domain.
How do i set up the subscribers to replicate the data on the old server that
will sit in the branch and then replicate the data on the new server that
has the publisher?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:046901c53435$c318f170$a601280a@.phx.gbl...
> Have a look at 'replication, backup and restore
> operations' in BOL. Provided the new server and the old
> server have the same name, this is possible. You'll need
> to take copies of all the publisher/distributor databases
> involved - the publishing database, MSDB, distribution
> and master. BTW, prevent anyone from accessing the
> publishing database and stop all replication jobs inc the
> cleanup agent before starting the backup procedures.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I'm not too sure I follow the last bit. As I understand,
you have an existing publisher that will be replaced by a
new publisher server. The new server will have the same
name and the old server taken offline. Form the point of
view of the subscribers, they won't know any difference,
unless they are using IP addresses for registration of
the publisher.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am sorry If I confused you. You are absolutly correct till now. What I
intend to do now is put our old server in one of our branches. I'll call it
Server1. Server1 is going to be a member of the domain if it makes sense.
Earlier managers used to replicate directly on to the publisher server but
now I want a bunch of managers to replicate to Server1 and rest of them to
Server0 i.e. my new server (Domain Controller). And I want Server1 to
replicate everything onto Server0. Server1 should pickup any relevant
changes from Server0 and pass onto subscribers replicating on Server1.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:108301c53502$00b05510$a501280a@.phx.gbl...
> I'm not too sure I follow the last bit. As I understand,
> you have an existing publisher that will be replaced by a
> new publisher server. The new server will have the same
> name and the old server taken offline. Form the point of
> view of the subscribers, they won't know any difference,
> unless they are using IP addresses for registration of
> the publisher.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thanks for the clarification. What you are referring to
is 'republishing'. There isn't a huge amount of
information in BOL but there is some. If you are using
merge replication, the same effect could be achieved by
using alternative synchronization partners.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
operations' in BOL. Provided the new server and the old
server have the same name, this is possible. You'll need
to take copies of all the publisher/distributor databases
involved - the publishing database, MSDB, distribution
and master. BTW, prevent anyone from accessing the
publishing database and stop all replication jobs inc the
cleanup agent before starting the backup procedures.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks for your reply. The new server has the same name as of old. But
eventually I'll have to change the name of the old server since two machines
with same name cannot belong to the same domain.
How do i set up the subscribers to replicate the data on the old server that
will sit in the branch and then replicate the data on the new server that
has the publisher?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:046901c53435$c318f170$a601280a@.phx.gbl...
> Have a look at 'replication, backup and restore
> operations' in BOL. Provided the new server and the old
> server have the same name, this is possible. You'll need
> to take copies of all the publisher/distributor databases
> involved - the publishing database, MSDB, distribution
> and master. BTW, prevent anyone from accessing the
> publishing database and stop all replication jobs inc the
> cleanup agent before starting the backup procedures.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I'm not too sure I follow the last bit. As I understand,
you have an existing publisher that will be replaced by a
new publisher server. The new server will have the same
name and the old server taken offline. Form the point of
view of the subscribers, they won't know any difference,
unless they are using IP addresses for registration of
the publisher.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am sorry If I confused you. You are absolutly correct till now. What I
intend to do now is put our old server in one of our branches. I'll call it
Server1. Server1 is going to be a member of the domain if it makes sense.
Earlier managers used to replicate directly on to the publisher server but
now I want a bunch of managers to replicate to Server1 and rest of them to
Server0 i.e. my new server (Domain Controller). And I want Server1 to
replicate everything onto Server0. Server1 should pickup any relevant
changes from Server0 and pass onto subscribers replicating on Server1.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:108301c53502$00b05510$a501280a@.phx.gbl...
> I'm not too sure I follow the last bit. As I understand,
> you have an existing publisher that will be replaced by a
> new publisher server. The new server will have the same
> name and the old server taken offline. Form the point of
> view of the subscribers, they won't know any difference,
> unless they are using IP addresses for registration of
> the publisher.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thanks for the clarification. What you are referring to
is 'republishing'. There isn't a huge amount of
information in BOL but there is some. If you are using
merge replication, the same effect could be achieved by
using alternative synchronization partners.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Monday, March 12, 2012
new problem - RESTORE
I thought I have successfully restored a backup from another instance of
MSDE, however in my .NET program I am getting an error message about the
login failed. So what I did is create a login id using sp_grantlogin. I
assume that this allows my Windows login to be authenticated. Just in case,
I also added a new login account using sp_addlogin. Those both worked.
Now when I do...EXEC sp_grantaccess to the database, I get an error:
Database cannot be opened. It is in the middle of a restore.
When I was restoring, I did the RESTORE FILELISTONLY to get the names of the
files in the backup. I then did RESTORE DATABASE using WITH and MOVE which
announced it was successful, and the two files were placed into the
directory. I thought this was it, what did I miss?
Thanks,
Brad
Make sure you use WITH RECOVERY (the default) othewise the database will be
left in a state where it is expecting to continue the restore process using
additional backup sets. Also if you are moving databases from one server to
another you will need to synchronize the users using sp_change_users_login
(see the BOL for this one).
Jim
"AllcompPC" <allcomppc@.sbcglobal.net> wrote in message
news:%23LZHQ0HEFHA.624@.TK2MSFTNGP09.phx.gbl...
>I thought I have successfully restored a backup from another instance of
> MSDE, however in my .NET program I am getting an error message about the
> login failed. So what I did is create a login id using sp_grantlogin. I
> assume that this allows my Windows login to be authenticated. Just in
> case,
> I also added a new login account using sp_addlogin. Those both worked.
> Now when I do...EXEC sp_grantaccess to the database, I get an error:
> Database cannot be opened. It is in the middle of a restore.
> When I was restoring, I did the RESTORE FILELISTONLY to get the names of
> the
> files in the backup. I then did RESTORE DATABASE using WITH and MOVE
> which
> announced it was successful, and the two files were placed into the
> directory. I thought this was it, what did I miss?
> Thanks,
> Brad
>
|||Thank you Jim. I got it and it seems to be working fine.
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:eY58HwIEFHA.228@.tk2msftngp13.phx.gbl...
> Make sure you use WITH RECOVERY (the default) othewise the database will
be
> left in a state where it is expecting to continue the restore process
using
> additional backup sets. Also if you are moving databases from one server
to[vbcol=seagreen]
> another you will need to synchronize the users using sp_change_users_login
> (see the BOL for this one).
> Jim
> "AllcompPC" <allcomppc@.sbcglobal.net> wrote in message
> news:%23LZHQ0HEFHA.624@.TK2MSFTNGP09.phx.gbl...
I
>
MSDE, however in my .NET program I am getting an error message about the
login failed. So what I did is create a login id using sp_grantlogin. I
assume that this allows my Windows login to be authenticated. Just in case,
I also added a new login account using sp_addlogin. Those both worked.
Now when I do...EXEC sp_grantaccess to the database, I get an error:
Database cannot be opened. It is in the middle of a restore.
When I was restoring, I did the RESTORE FILELISTONLY to get the names of the
files in the backup. I then did RESTORE DATABASE using WITH and MOVE which
announced it was successful, and the two files were placed into the
directory. I thought this was it, what did I miss?
Thanks,
Brad
Make sure you use WITH RECOVERY (the default) othewise the database will be
left in a state where it is expecting to continue the restore process using
additional backup sets. Also if you are moving databases from one server to
another you will need to synchronize the users using sp_change_users_login
(see the BOL for this one).
Jim
"AllcompPC" <allcomppc@.sbcglobal.net> wrote in message
news:%23LZHQ0HEFHA.624@.TK2MSFTNGP09.phx.gbl...
>I thought I have successfully restored a backup from another instance of
> MSDE, however in my .NET program I am getting an error message about the
> login failed. So what I did is create a login id using sp_grantlogin. I
> assume that this allows my Windows login to be authenticated. Just in
> case,
> I also added a new login account using sp_addlogin. Those both worked.
> Now when I do...EXEC sp_grantaccess to the database, I get an error:
> Database cannot be opened. It is in the middle of a restore.
> When I was restoring, I did the RESTORE FILELISTONLY to get the names of
> the
> files in the backup. I then did RESTORE DATABASE using WITH and MOVE
> which
> announced it was successful, and the two files were placed into the
> directory. I thought this was it, what did I miss?
> Thanks,
> Brad
>
|||Thank you Jim. I got it and it seems to be working fine.
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:eY58HwIEFHA.228@.tk2msftngp13.phx.gbl...
> Make sure you use WITH RECOVERY (the default) othewise the database will
be
> left in a state where it is expecting to continue the restore process
using
> additional backup sets. Also if you are moving databases from one server
to[vbcol=seagreen]
> another you will need to synchronize the users using sp_change_users_login
> (see the BOL for this one).
> Jim
> "AllcompPC" <allcomppc@.sbcglobal.net> wrote in message
> news:%23LZHQ0HEFHA.624@.TK2MSFTNGP09.phx.gbl...
I
>
New MS SQL Server
How do I backup databases, onto Veritas Backup? In the Enterprise Edition if
I expand the database server I could select all task, backup database and
select the destination. Which is the proper way to backup these databases.
You could get a Veritas agent that will be able to back up the database real
time. The problem with using the plain vanilla Backup EXEC is that it can't
back up a file that is in use. Alternatively, you can use a scheduled task
within Enterprise Manager to back up the database before you backup to tape.
We have ours set up this way and it works well...
Regards,
Hank Arnold
"OSA" <OSA@.discussions.microsoft.com> wrote in message
news:12671BDC-34F2-427E-80EF-753A96DC4818@.microsoft.com...
> How do I backup databases, onto Veritas Backup? In the Enterprise Edition
> if
> I expand the database server I could select all task, backup database and
> select the destination. Which is the proper way to backup these databases.
I expand the database server I could select all task, backup database and
select the destination. Which is the proper way to backup these databases.
You could get a Veritas agent that will be able to back up the database real
time. The problem with using the plain vanilla Backup EXEC is that it can't
back up a file that is in use. Alternatively, you can use a scheduled task
within Enterprise Manager to back up the database before you backup to tape.
We have ours set up this way and it works well...
Regards,
Hank Arnold
"OSA" <OSA@.discussions.microsoft.com> wrote in message
news:12671BDC-34F2-427E-80EF-753A96DC4818@.microsoft.com...
> How do I backup databases, onto Veritas Backup? In the Enterprise Edition
> if
> I expand the database server I could select all task, backup database and
> select the destination. Which is the proper way to backup these databases.
Friday, March 9, 2012
New Log or append?
I'm backing into the DBA position from being an app / web designer. Using SQL 2005 and hating wizards, I'm trying to code a BACKUP LOG query to run every 15 minutes and create a separate date-time-stamped TRN. I have several questions starting with - should I be doing this? Is it better to create separate LOGs or append to one? Isn't it true that the only time I'll need the LOGs is when I have a crash and that then I'll need to RESTORE in date-time-order?
I'm doing a FULL backup every 6 hours and intend on automating deleting the TRNs on a successful completion of the BAK.
The following code runs fine and creates a date-time-stamped TRN but doesn't do what I want, as it overwrites the first TRN, so how do I create, say, four JOBSTEPs and get a JOBSTEP @.COMMAND to run the SETs?
USE msdb
IFEXISTS(SELECTNameFROM sysjobs WHEREName='jobDBBackupWedb_1Log')
EXECsp_delete_job @.job_name ='jobDBBackupWedb_1Log'
GO
DECLARE @.now char(14)-- current date in the form of yyyymmddhhmmss
DECLARE @.dbName sysname-- database name to include date-time-stamp
DECLARE @.Cmd nvarchar(260)-- variable used to build JobStep command
SET @.now =REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50),GETDATE(), 120),'-',''),' ',''),':','')
SET @.dbName ='Wedb_1_'+ @.now +'_.trn'
SET @.Cmd ='BACKUP LOG Wedb_1 TO DISK = '+CHAR(39)+'\\SERVER1\D$\SQLDatabases\Backup\WebEoc\'+ @.dbName +CHAR(39)
EXECsp_add_job
@.Job_Name ='jobDBBackupWedb_1Log',
@.Description ='Run Wedb_1 DB Backup Transaction Log Every 15 Minutes',
@.Category_Name ='Database Maintenance'
EXECsp_add_jobstep
@.Job_Name ='jobDBBackupWedb_1Log',
@.step_name ='DB_Backup_Log',
@.step_id = 1,
@.Database_Name ='Master',
@.subsystem ='TSQL',
@.command = @.Cmd
/*
Start time for the Transaction Log backups is seven and one half minutes after midnight
to not conflict with the Full database backups run every 6 hours starting at midnight.
The Transaction Log backups are to be run every fifteen minutes.
*/
EXECsp_Add_JobSchedule
@.Job_Name ='jobDBBackupWedb_1Log',
@.Name ='schedDB_BackupLog',
@.Freq_Type = 4,-- Daily
@.Freq_Interval = 1,-- Each day
@.Freq_Subday_Type = 0x4,-- Interval in minutes
@.Freq_Subday_Interval = 15,-- Every fifteen minutes
@.Active_Start_Time = 000730 -- Start Time is seven and one half minutes after midnight
--@.Active_End_Time = 235959 -- End Time is next midnight
EXECsp_Add_JobServer
@.Job_Name ='jobDBBackupWedb_1Log'
GO
My opinion, I'd create DB Maint Plans to do database and trans log backups, and not write my own, unless there is some real good reason to. Main plans aren't perfect, but still better then trying to reproduce the same end result. One of the points of backing up the trans log is for disaster recovery. You could backup your trans log every 30 minutes, then copy/move those backups off to a different server/tape, etc... So, I wouldn't stack them on one file, becuase you need to move them continually to a different box, or yea, backup cross server, ehhhh.... I prefer local backups and then copying them somewhere else, like with RoboCopy. The times you nee the trans logs are for recovery, or to do log shipping to a different server (similar concept to database mirroring, but still more in use then mirroring is I think). If doing a full backup every 6 hours, same thing, back it up and move it off somewhere safe, not leave that on teh same drives as your database files! I'd still leave the old TRN files on teh other location, there COULD be a case where you need to go back 9 hours, then you'd have the old trans and backups to get there, I wouldnt delete the TRN files every 6 hours as you said.
Bruce
|||Thanks ... but ...
I'm backing up to a separate / different network drive on a different / separate server that my network admin has established as a SANs - Storage Area Network - drive. That SANs drive is data specific and is being snap-shot-backed-up every 5 minutes. Then it goes to tape every night at 11:00 PM.
The nature of the DB is that the WebEoc is for running the SEOC - State Emergency Operations Center - during hurricanes, earthquakes, etc.
|||I think that the problem with your original script is that you are creating the backup command when you create the job rather than dynamically creating and executing the command when executing the job.
Try the re-worked version below.
Chris
Code Snippet
USE msdbIF EXISTS ( SELECT Name
FROM sysjobs
WHERE Name = 'jobDBBackupWedb_1Log' )
EXEC sp_delete_job @.job_name = 'jobDBBackupWedb_1Log'
GO
DECLARE @.cmd NVARCHAR(3201)
SET @.cmd = N'DECLARE @.sql VARCHAR(8000)
DECLARE @.now char(14)
-- current date in the form of yyyymmddhhmmss
DECLARE @.dbName sysname
-- database name to include date-time-stamp
SET @.now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), ''-'',
''''), '' '', ''''), '':'', '''')
SET @.dbName = ''Wedb_1_'' + @.now + ''_.trn''
SET @.sql = ''BACKUP LOG Wedb_1 TO DISK = '' + CHAR(39)
+ ''\\SERVER1\D$\SQLDatabases\Backup\WebEoc\'' + @.dbName + CHAR(39)
EXEC(@.sql)'
EXEC sp_add_job @.Job_Name = 'jobDBBackupWedb_1Log',
@.Description = 'Run Wedb_1 DB Backup Transaction Log Every 15 Minutes',
@.Category_Name = 'Database Maintenance'
EXEC sp_add_jobstep @.Job_Name = 'jobDBBackupWedb_1Log',
@.step_name = 'DB_Backup_Log', @.step_id = 1, @.Database_Name = 'Master',
@.subsystem = 'TSQL', @.command = @.Cmd
/*
Start time for the Transaction Log backups is seven and one half minutes after midnight
to not conflict with the Full database backups run every 6 hours starting at midnight.
The Transaction Log backups are to be run every fifteen minutes.
*/
EXEC sp_Add_JobSchedule @.Job_Name = 'jobDBBackupWedb_1Log',
@.Name = 'schedDB_BackupLog', @.Freq_Type = 4, -- Daily
@.Freq_Interval = 1, -- Each day
@.Freq_Subday_Type = 0x4, -- Interval in minutes
@.Freq_Subday_Interval = 15, -- Every fifteen minutes
@.Active_Start_Time = 000730
-- Start Time is seven and one half minutes after midnight
--@.Active_End_Time = 235959 -- End Time is next midnight
EXEC sp_Add_JobServer @.Job_Name = 'jobDBBackupWedb_1Log'
GO
|||
Chris,
Thank you very kindly! I take your comment about run-time, create/execute; that was spot on. Thanks for the code change - works like a charm ...
... but, I've looked and not found an answer, what is the "N" doing for me in SET @.cmd = N'DECLARE ... ?
Is it declaring that what follows is all an NVARCHAR string?
Wednesday, March 7, 2012
New Install of Sql server Express fails to restore Master database
Hello,
I have a fresh install of sqlExpress and Management Studio Express on my test server. I want to restore my master database from backup.
From the command prompt I set the Sqlservr -s SQLEXPRESS -m
Then I opened another comand prompt and ran my SQLCMD script to restore the Master Database.
here is the sql script:
RESTORE DATABASE [Master] FROM DISK = N'E:\COPLEYNEWSDATABASEBACKUP\Master.bak' WITH FILE = 1, MOVE N'mastlog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Master_1.ldf', NOUNLOAD, STATS = 10
GO
I recieve the following error.
Msg 3154, Level 16, State 4, Server COPLEYNEWS\SQLEXPRESS, Line 1
The backup set holds a backup of a database other than the existing 'Master' dat
abase.
Msg 3013, Level 16, State 1, Server COPLEYNEWS\SQLEXPRESS, Line 1
How do I restore a Master Database on SQL Express?
the error indicates the backupset does not contain a backup of master. do 'restore headeronly' and verify its contents.
aslo, you will want to name the system database as "master" (all lower case) and do not include the "move" option as part of your system database restore.
|||
Thanks for the reply,
I change the *.sql restore script to:
RESTORE DATABASE [Master] FROM DISK = N'E:\COPLEYNEWSDATABASEBACKUP\Master.bak' WITH FILE= 1, NOUNLOAD, REPLACE, STATS = 10
GO
Now I get the error that I am restoring a Master.bak file to a SQL Server 9.0.1399 and I need to restore it to a SQL Server 9.0.2047 server.
So, right know I have SQL Server 9.0.2047 that has the Master.bak file.
I am trying to restore that file on a SQL Server 9.0.1399.
My problem right now is that I know I need to restore on the SQL Server 9.0.2047 but I can't figure out which version of SQL Server Express to download.
I thought it was the SQL Server Express w/advanced Services. There is also the issue of service packs for SQL Express.
Can someone please give me the download link to the correct version of SQL Server 9.0.2047?
Thanks in advance.
|||version 9.0.1399 is RTM and 9.0.2047 is sp1.
you want to download the latest from site. it's sp1 patched.
http://msdn.microsoft.com/vstudio/express/sql/download/
|||After a week of trying to figure out a working disaster recovery solution for sql express, I have finally succeeded.
I wanted to do a disaster recovery to the same server with a tape backup and then restore the Master.bak, Model.bak, MSDB.bak, and MyDatabase.bak, back to the SQLExpress DBMS. Let me start out by saying that you cannot do that unless you have a working copy of the Master.mdf on the server you are trying to restore to. The catch here is that the tape backup software will not back up the Master.mdf because its always being used by SQLExpress.
Now, I suppose I could have dettached the Master.mdf before I backed it up, but I decided to go a different route.
I found out that there is a directory called "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data" and in it is a copy of the same contents (except I think that they are the original files created automatically by the original install, in other words, not uptodate) found in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory where the files need to reside in order to do a restore to the database, which happened to be missing after I did my tape back up restore. I simply draged and dropped a copy of the missing *mdf, *ldf files and then I was able to launch the SQLExpress Studio Management Express and connect to the SQL Server. The only thing different was that the Model database was set to (Read Only) but that didn't concern me because I was going to restore all the Databases (Master, Model, MSDB, MyDatabase) anyway.
After that was solved, I could now run any *.sql restore scripts and have them work.
Next step is to restore the Master.mdf but you can only do that in single user mode.
So, I went into SQLServer Express, single user mode with the command prompt:
C:cd "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn: Sqlservr.exe -s SQLEXPRESS -m
Press<enter>
After it does its thing you need to open an additional command prompt window because it will say that its ready for connections in single user mode, but you will not be given a new cursor prompt.
So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MASTERFULLRESTORE.sql"
This is what my MASTERFULLRESTORE.sql restore script file looks like (I created it previously using the GUI in sql express and then saved it to a script file instead of clicking on OK):
RESTORE DATABASE [Master] FROM DISK = N'E:\DATABASEBACKUP\Master.bak' WITH FILE= 1, NOUNLOAD, REPLACE, STATS = 10
GO
The SQL restore script will work this time becuase I have a working instance SQLExpress thanks to the previous drag and drop restore of the Master.mdf from the Template Data directory.
After that you need to go into Services and start the 'SQLServer Service'.
Then simply go into SQLExpress like normal and restore the Model, MSDB, from the GUI interface by right clicking on each one and selecting restore and then point to your *bak files.
When it came time to restore 'MyDatabase', I could see it in SQLExpress Management Studio Express but it was just a shell of it with no contents inside. This shell of my database is from the Master.mdf restore. Unfortunately, even though I could see it in the SQLExpress listing, when I right clicked on it, hoping for the restore option, It was grayed out. So I closed SQL Express and ran my restore.sql script from the command prompt using SQLCMD.exe and it succeeded.
So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MYDATABASEFULLRESTORE.sql"
I then went back into SQLExpress and ran some queries to test and everything seems to be working now.
Thanks to everyone for helping to point me in the right direction to solve this problem.
Monday, February 20, 2012
New Database Name not visible while DSN setup
Hi,
I have taken backup(connectdb.bak) from production and
created the database in test server. For that I used
following procedure,
In sql query analyzer, I executed the following two steps
consecutively.
1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak'
2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak' WITH
MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
This created a new database "newdb" in test server from
the back up file connectdb.bak.
I want to connect to this database.
Now while going through SQL server DSN configuration,
there comes an option where we get the following option,
"change the default database to"
While clicking in this box,we get a drop down listing of
all the databases. Here I am stuck. Ideally the "newdb"
database which I have created from connectdb.bak file,
should be visible here so that I can choose to connect to
it. But it's not visible here and unless it's visible here
I can't do anything. The otherdatabases which were present
in the test server since beginning are visible accept this
one.
Please help. It's urgent
regards,
Vishal
Check to see if the login/user for whom you are setting up
the DSN is a user in the database that has been restored. It
sounds like they users could have been orphaned.
You can find a good article on orphaned users and
troubleshooting techniques, links to KB articles at:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
-Sue
On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have taken backup(connectdb.bak) from production and
>created the database in test server. For that I used
>following procedure,
>In sql query analyzer, I executed the following two steps
>consecutively.
>1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak'
>2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak' WITH
>MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
>MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
>This created a new database "newdb" in test server from
>the back up file connectdb.bak.
>I want to connect to this database.
>Now while going through SQL server DSN configuration,
>there comes an option where we get the following option,
>"change the default database to"
>While clicking in this box,we get a drop down listing of
>all the databases. Here I am stuck. Ideally the "newdb"
>database which I have created from connectdb.bak file,
>should be visible here so that I can choose to connect to
>it. But it's not visible here and unless it's visible here
>I can't do anything. The otherdatabases which were present
>in the test server since beginning are visible accept this
>one.
>Please help. It's urgent
>regards,
>Vishal
>
|||If this is a new serer then the user exists in the
database but not in master. You first need to create the
login. Then synch up the users with sp_change_users_login
>--Original Message--
>Check to see if the login/user for whom you are setting up
>the DSN is a user in the database that has been restored.
It
>sounds like they users could have been orphaned.
>You can find a good article on orphaned users and
>troubleshooting techniques, links to KB articles at:
>http://vyaskn.tripod.com/troubleshoo...phan_users.htm
>-Sue
>On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
><anonymous@.discussions.microsoft.com> wrote:
steps
to
here
present
this
>.
>
I have taken backup(connectdb.bak) from production and
created the database in test server. For that I used
following procedure,
In sql query analyzer, I executed the following two steps
consecutively.
1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak'
2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak' WITH
MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
This created a new database "newdb" in test server from
the back up file connectdb.bak.
I want to connect to this database.
Now while going through SQL server DSN configuration,
there comes an option where we get the following option,
"change the default database to"
While clicking in this box,we get a drop down listing of
all the databases. Here I am stuck. Ideally the "newdb"
database which I have created from connectdb.bak file,
should be visible here so that I can choose to connect to
it. But it's not visible here and unless it's visible here
I can't do anything. The otherdatabases which were present
in the test server since beginning are visible accept this
one.
Please help. It's urgent
regards,
Vishal
Check to see if the login/user for whom you are setting up
the DSN is a user in the database that has been restored. It
sounds like they users could have been orphaned.
You can find a good article on orphaned users and
troubleshooting techniques, links to KB articles at:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
-Sue
On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have taken backup(connectdb.bak) from production and
>created the database in test server. For that I used
>following procedure,
>In sql query analyzer, I executed the following two steps
>consecutively.
>1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak'
>2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak' WITH
>MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
>MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
>This created a new database "newdb" in test server from
>the back up file connectdb.bak.
>I want to connect to this database.
>Now while going through SQL server DSN configuration,
>there comes an option where we get the following option,
>"change the default database to"
>While clicking in this box,we get a drop down listing of
>all the databases. Here I am stuck. Ideally the "newdb"
>database which I have created from connectdb.bak file,
>should be visible here so that I can choose to connect to
>it. But it's not visible here and unless it's visible here
>I can't do anything. The otherdatabases which were present
>in the test server since beginning are visible accept this
>one.
>Please help. It's urgent
>regards,
>Vishal
>
|||If this is a new serer then the user exists in the
database but not in master. You first need to create the
login. Then synch up the users with sp_change_users_login
>--Original Message--
>Check to see if the login/user for whom you are setting up
>the DSN is a user in the database that has been restored.
It
>sounds like they users could have been orphaned.
>You can find a good article on orphaned users and
>troubleshooting techniques, links to KB articles at:
>http://vyaskn.tripod.com/troubleshoo...phan_users.htm
>-Sue
>On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
><anonymous@.discussions.microsoft.com> wrote:
steps
to
here
present
this
>.
>
New Database Name not visible while DSN setup
Hi,
I have taken backup(connectdb.bak) from production and
created the database in test server. For that I used
following procedure,
In sql query analyzer, I executed the following two steps
consecutively.
1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak'
2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak' WITH
MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
This created a new database "newdb" in test server from
the back up file connectdb.bak.
I want to connect to this database.
Now while going through SQL server DSN configuration,
there comes an option where we get the following option,
"change the default database to"
While clicking in this box,we get a drop down listing of
all the databases. Here I am stuck. Ideally the "newdb"
database which I have created from connectdb.bak file,
should be visible here so that I can choose to connect to
it. But it's not visible here and unless it's visible here
I can't do anything. The otherdatabases which were present
in the test server since beginning are visible accept this
one.
Please help. It's urgent
regards,
VishalCheck to see if the login/user for whom you are setting up
the DSN is a user in the database that has been restored. It
sounds like they users could have been orphaned.
You can find a good article on orphaned users and
troubleshooting techniques, links to KB articles at:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
-Sue
On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have taken backup(connectdb.bak) from production and
>created the database in test server. For that I used
>following procedure,
>In sql query analyzer, I executed the following two steps
>consecutively.
>1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak'
>2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak' WITH
>MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
>MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
>This created a new database "newdb" in test server from
>the back up file connectdb.bak.
>I want to connect to this database.
>Now while going through SQL server DSN configuration,
>there comes an option where we get the following option,
>"change the default database to"
>While clicking in this box,we get a drop down listing of
>all the databases. Here I am stuck. Ideally the "newdb"
>database which I have created from connectdb.bak file,
>should be visible here so that I can choose to connect to
>it. But it's not visible here and unless it's visible here
>I can't do anything. The otherdatabases which were present
>in the test server since beginning are visible accept this
>one.
>Please help. It's urgent
>regards,
>Vishal
>|||If this is a new serer then the user exists in the
database but not in master. You first need to create the
login. Then synch up the users with sp_change_users_login
>--Original Message--
>Check to see if the login/user for whom you are setting up
>the DSN is a user in the database that has been restored.
It
>sounds like they users could have been orphaned.
>You can find a good article on orphaned users and
>troubleshooting techniques, links to KB articles at:
>http://vyaskn.tripod.com/troublesho...rphan_users.htm
>-Sue
>On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
><anonymous@.discussions.microsoft.com> wrote:
>
steps
to
here
present
this
>.
>
I have taken backup(connectdb.bak) from production and
created the database in test server. For that I used
following procedure,
In sql query analyzer, I executed the following two steps
consecutively.
1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak'
2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak' WITH
MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
This created a new database "newdb" in test server from
the back up file connectdb.bak.
I want to connect to this database.
Now while going through SQL server DSN configuration,
there comes an option where we get the following option,
"change the default database to"
While clicking in this box,we get a drop down listing of
all the databases. Here I am stuck. Ideally the "newdb"
database which I have created from connectdb.bak file,
should be visible here so that I can choose to connect to
it. But it's not visible here and unless it's visible here
I can't do anything. The otherdatabases which were present
in the test server since beginning are visible accept this
one.
Please help. It's urgent
regards,
VishalCheck to see if the login/user for whom you are setting up
the DSN is a user in the database that has been restored. It
sounds like they users could have been orphaned.
You can find a good article on orphaned users and
troubleshooting techniques, links to KB articles at:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
-Sue
On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have taken backup(connectdb.bak) from production and
>created the database in test server. For that I used
>following procedure,
>In sql query analyzer, I executed the following two steps
>consecutively.
>1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak'
>2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak' WITH
>MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
>MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
>This created a new database "newdb" in test server from
>the back up file connectdb.bak.
>I want to connect to this database.
>Now while going through SQL server DSN configuration,
>there comes an option where we get the following option,
>"change the default database to"
>While clicking in this box,we get a drop down listing of
>all the databases. Here I am stuck. Ideally the "newdb"
>database which I have created from connectdb.bak file,
>should be visible here so that I can choose to connect to
>it. But it's not visible here and unless it's visible here
>I can't do anything. The otherdatabases which were present
>in the test server since beginning are visible accept this
>one.
>Please help. It's urgent
>regards,
>Vishal
>|||If this is a new serer then the user exists in the
database but not in master. You first need to create the
login. Then synch up the users with sp_change_users_login
>--Original Message--
>Check to see if the login/user for whom you are setting up
>the DSN is a user in the database that has been restored.
It
>sounds like they users could have been orphaned.
>You can find a good article on orphaned users and
>troubleshooting techniques, links to KB articles at:
>http://vyaskn.tripod.com/troublesho...rphan_users.htm
>-Sue
>On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
><anonymous@.discussions.microsoft.com> wrote:
>
steps
to
here
present
this
>.
>
New database from backup
I am going to build a new website and I have all the data in a sql-backup file - and I have a new installation of SQL Server - how do I install it? I try to use Restore database from SQL Server Manager, but I just get the errors (I think because of that I do not have the database in the server).
I am totally beginner with SQL Server - so coudl anyone help me?
What errors are you getting, exactly?If you want to restore the database on a second machine (instead of the one where the backup was made), you probably need to modify the path where the files will be stored. On the restore database dialog, go to the second tab and check the file names. (This is in SQL 2000 Enterprise Manager, the dialog may look different on the 2005 version).|||Thanks.
You made my day.
Subscribe to:
Posts (Atom)