engineroom
asked on
Manually Backup SQL 2005
Hey all, here's what i'm trying to do. I have 2 machines that have sql Server on it. Both machines have a database called db. What i'm trying to do is manually backup the db from machine 1 to machine 2. So i want to overwrite the db on machine 2. What files do i grab on machine 1 to replace to machine 2? I do not want to use the sql maintenance or anything like that. Thanks all!
er
er
why don't you truncate db on 2 and then use a select into from db on 1
-- machine 1
backup database <database>
to disk='C:\Backup.bak'
-- transfer backup.bak to machine 2
-- machine 2
restore database <database>
from disk='C:\Backup.bak'
backup database <database>
to disk='C:\Backup.bak'
-- transfer backup.bak to machine 2
-- machine 2
restore database <database>
from disk='C:\Backup.bak'
ASKER
@SQL_SERVER_DBA:
I have no idea what that means... could you please explain.
@Crag:
I didn't want to do that. Aren't there some physical SQL files that i could grab?
I have no idea what that means... could you please explain.
@Crag:
I didn't want to do that. Aren't there some physical SQL files that i could grab?
You can copy the files directly if you detach the database from machine 1 copy the files to machine 2.
You will then need to re-attach it om machine 1 and also on machine 2.
What's wrong with backing up the database?
Use the sp_helpdb <database> command to find all of the files that relate to a specific database.
You will then need to re-attach it om machine 1 and also on machine 2.
What's wrong with backing up the database?
Use the sp_helpdb <database> command to find all of the files that relate to a specific database.
if you are restoring over an existing database, you will need to use WITH MOVE and add the file names.
You can also create a backup device on machine 2 and then set the backup to use the backup device.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E5DAC
Has a section on how to create a backup device.
You can also create a backup device on machine 2 and then set the backup to use the backup device.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E5DAC
Has a section on how to create a backup device.
ASKER
@Crag.. What do you mean detach and attach?
I do not want to use backup and restore, is this possible?
I do not want to use backup and restore, is this possible?
If you use the Enterprise Manager you can right click the database and then select Detach. This takes the database out of SQL Serer but leaves it intact. Make sure you use the sp_helpdb <database> command beforehand to find all of the files related to the database.
You can then copy the files to the second system .
Right click the Databases section and select Attach, fill in the appropriate boxes and search for the files you copied over.
You will need to re-attach the database to machine 1 though otherwise it will not be available.
Also - while you do this the database will not be available to anyone.
Try this on a test database first to make sure you are happy with how it works!
You can then copy the files to the second system .
Right click the Databases section and select Attach, fill in the appropriate boxes and search for the files you copied over.
You will need to re-attach the database to machine 1 though otherwise it will not be available.
Also - while you do this the database will not be available to anyone.
Try this on a test database first to make sure you are happy with how it works!
ASKER
@Crag:
I want to write a batch file that grabs physical files... is this possible? I do not want to interact with enterprise at all...?
I want to write a batch file that grabs physical files... is this possible? I do not want to interact with enterprise at all...?
Backup (and restore) are easy to put in a batch and they take care of things. Probably you can give your argument for not wanting to use that.
Where the physical files are is no problem, select your database in Management studion, richt click and go to properties, there you'll find your location at 'files'.
If you want the physical files from server 1 when the database is still running you will run into problems anyway.
Where the physical files are is no problem, select your database in Management studion, richt click and go to properties, there you'll find your location at 'files'.
If you want the physical files from server 1 when the database is still running you will run into problems anyway.
You can find out the logical/physical files names for the database using the following script:
select
[name] as 'LogicalName'
, [Physical_name] as 'PhysicalName'
, [type]
from <database>.sys.database_fi les
You will then need to pull these into a script to do the detach/move/attach.
Don't forgot that you have to re-attach the database on the source server otherwise it won't be available.
Also be aware that you need full sysasmin rights on the SQL Server to attach a database.
To detach the database use SP_DETACH_DB
Check out the CREATE DATABASE section of the Books Online, section E. Attaching a database for some good info.
select
[name] as 'LogicalName'
, [Physical_name] as 'PhysicalName'
, [type]
from <database>.sys.database_fi
You will then need to pull these into a script to do the detach/move/attach.
Don't forgot that you have to re-attach the database on the source server otherwise it won't be available.
Also be aware that you need full sysasmin rights on the SQL Server to attach a database.
To detach the database use SP_DETACH_DB
Check out the CREATE DATABASE section of the Books Online, section E. Attaching a database for some good info.
ASKER
@jogos:
How can i make it so it backs up machine 1, every ten minutes or so, and then transfers the .bak file over the network to machine2, and then machine2 restores the backup?
How can i make it so it backs up machine 1, every ten minutes or so, and then transfers the .bak file over the network to machine2, and then machine2 restores the backup?
There are several ways of doing this depending on whether you want to use SQL Agent or Windows Scheduler to run the job.
If you use SQL Agent then you can schedule a job on system 2 to backup the database to \\system2\share\backup.bak
Then run a command to restore the database backup.
See my statements above.
You need to know that the users will be disconnected from the database on system 2 when you do the restore.
If you use SQL Agent then you can schedule a job on system 2 to backup the database to \\system2\share\backup.bak
Then run a command to restore the database backup.
See my statements above.
You need to know that the users will be disconnected from the database on system 2 when you do the restore.
ASKER
@Crag
I wanted to use windows scheduler to do the backup + restore. how do i do this?
When i do the restore on machine2 my users will be disconnected right? That's okay.
I wanted to use windows scheduler to do the backup + restore. how do i do this?
When i do the restore on machine2 my users will be disconnected right? That's okay.
You'll need a script like the following, two SQLCMD commands, one to backup, one to restore:
I was thinking of the replace command to force the restore over an existing database - not sure it will work if there are users connected.
sqlcmd -s <system1> -E -Q "backup database <database> to disk = 'D:\Temp\<database>_backup _.BAK' with stats = 20"
sqlcmd -s <system2> -E -Q "restore database <database> from disk = 'D:\Temp\<database_backup_ .BAK' with stats = 20, replace"
If the restore fails due to users connected you'll need a script to drop their connections.
I was thinking of the replace command to force the restore over an existing database - not sure it will work if there are users connected.
sqlcmd -s <system1> -E -Q "backup database <database> to disk = 'D:\Temp\<database>_backup
sqlcmd -s <system2> -E -Q "restore database <database> from disk = 'D:\Temp\<database_backup_
If the restore fails due to users connected you'll need a script to drop their connections.
ASKER
so i just dump that in a .bat file?
Try this version in a windows CMD file (backup.cmd), change the database name from TestDB
rem backup the database
sqlcmd -s system1 -E -Q "backup database TestDB to disk = 'D:\Temp\testdb_backup_.BA K' with stats = 20;"
rem drop existing connections
sqlcmd -s system2 -E -Q "ALTER DATABASE TestDB SET SINGLE_USER WITH rollback immediate;"
rem restore the database
sqlcmd -s system2 -E -Q "restore database TestdB from disk = 'D:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
rem backup the database
sqlcmd -s system1 -E -Q "backup database TestDB to disk = 'D:\Temp\testdb_backup_.BA
rem drop existing connections
sqlcmd -s system2 -E -Q "ALTER DATABASE TestDB SET SINGLE_USER WITH rollback immediate;"
rem restore the database
sqlcmd -s system2 -E -Q "restore database TestdB from disk = 'D:\Temp\testdb_backup_.BA
ASKER
okay, here's exactly what i used...
rem backup the database
sqlcmd -s old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
rem drop existing connections
sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
rem restore the database
sqlcmd -s fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
where F is a share to the machine2. This didn't work. It showed me percentages of it running, but the content from machine1 is not the same as machine2. ??
rem backup the database
sqlcmd -s old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
rem drop existing connections
sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
rem restore the database
sqlcmd -s fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
where F is a share to the machine2. This didn't work. It showed me percentages of it running, but the content from machine1 is not the same as machine2. ??
<How can i make it so it backs up machine 1, every ten minutes or so>
If you need a permanent synchronisation you should look at other things, like database mirroring or replication.
<This didn't work.>
There are 3 things here you have to separate.
1) Does the backup-command work (parameters, authorisation....) on server 1
2) Does the restore-command work (parameters, authorisation....) on server 2
3) Can I get it to work from a batch-file with the credentials same credentials .....
If you need a permanent synchronisation you should look at other things, like database mirroring or replication.
<This didn't work.>
There are 3 things here you have to separate.
1) Does the backup-command work (parameters, authorisation....) on server 1
2) Does the restore-command work (parameters, authorisation....) on server 2
3) Can I get it to work from a batch-file with the credentials same credentials .....
Odd. It worked here when I tried it.
Can you dump the output of the batch script to a log file
backup.cmd >output.log
and post it up?
To schedule you can use the Windows Scheduler.
You will need to make sure that the job is run under a windows account that has sufficient access to the SQL Server.
Can you dump the output of the batch script to a log file
backup.cmd >output.log
and post it up?
To schedule you can use the Windows Scheduler.
You will need to make sure that the job is run under a windows account that has sufficient access to the SQL Server.
ASKER
@both, here's the log file....
-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-=
C:\>rem backup the database
C:\>sqlcmd -s old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
21 percent processed.
40 percent processed.
61 percent processed.
80 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 3.
100 percent processed.
Processed 1 pages for database 'nsa', file 'nsa_log' on file 3.
BACKUP DATABASE successfully processed 4849 pages in 5.211 seconds (7.622 MB/sec).
C:\>rem drop existing connections
C:\>sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore the database
C:\>sqlcmd -s fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.720 seconds (6.944 MB/sec).
-=-=-=-=-=-=-=-=-=-=-=-=-=
C:\>rem backup the database
C:\>sqlcmd -s old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
21 percent processed.
40 percent processed.
61 percent processed.
80 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 3.
100 percent processed.
Processed 1 pages for database 'nsa', file 'nsa_log' on file 3.
BACKUP DATABASE successfully processed 4849 pages in 5.211 seconds (7.622 MB/sec).
C:\>rem drop existing connections
C:\>sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore the database
C:\>sqlcmd -s fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.720 seconds (6.944 MB/sec).
ok so the database was restored correctly onto the fileserver system and yet you say that the contents of the database on fileserver do not match that on old20box? Very odd.
What indicates that the two are not the same?
What indicates that the two are not the same?
checked your content again?
ASKER
@both:
I double checked the content. It's not there. I have a table called 'directors'. in the old20box, there are about 100 recs, in the fileserver, there is 1. The 100 recs should come over and replace that 1 record on the fileserver, but it's not.
I double checked the content. It's not there. I have a table called 'directors'. in the old20box, there are about 100 recs, in the fileserver, there is 1. The 100 recs should come over and replace that 1 record on the fileserver, but it's not.
that would imply one of two things:
1. The restore is not completing succesfully - which it is from the log
2. The either you are looking at the wrong database on the source server or the target server.
It's not possible to restore a database over the top of an existing one and still have the original data.
Can you run this revised version of the script and post the output:
sqlcmd -s old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
sqlcmd -s old20box -E -Q "select count(*) from nsa..directors;"
sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
sqlcmd -s fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
sqlcmd -s fileserver -E -Q "select count(*) from nsa..directors;"
you might need to change the schema/table name for directors table.
1. The restore is not completing succesfully - which it is from the log
2. The either you are looking at the wrong database on the source server or the target server.
It's not possible to restore a database over the top of an existing one and still have the original data.
Can you run this revised version of the script and post the output:
sqlcmd -s old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
sqlcmd -s old20box -E -Q "select count(*) from nsa..directors;"
sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
sqlcmd -s fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
sqlcmd -s fileserver -E -Q "select count(*) from nsa..directors;"
you might need to change the schema/table name for directors table.
ASKER
Here's what i got
-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-=
C:\>sqlcmd -s old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
21 percent processed.
40 percent processed.
61 percent processed.
80 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 6.
100 percent processed.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 6.
BACKUP DATABASE successfully processed 4850 pages in 4.779 seconds (8.312 MB/sec).
C:\>sqlcmd -s old20box -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
C:\>sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>sqlcmd -s fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.858 seconds (6.781 MB/sec).
C:\>sqlcmd -s fileserver -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
-=-=-=-=-=-=-=-=-=-=-=-=-=
C:\>sqlcmd -s old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
21 percent processed.
40 percent processed.
61 percent processed.
80 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 6.
100 percent processed.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 6.
BACKUP DATABASE successfully processed 4850 pages in 4.779 seconds (8.312 MB/sec).
C:\>sqlcmd -s old20box -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
C:\>sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>sqlcmd -s fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.858 seconds (6.781 MB/sec).
C:\>sqlcmd -s fileserver -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
ASKER
and no, it didn't change the data in the fileserver
Why do you say it didn't change the data?
what program & method are using to check?
The directors table on fileserver has 182 rows the same as on the old20box.
what program & method are using to check?
The directors table on fileserver has 182 rows the same as on the old20box.
ASKER
I'm opening up the directors table that's in the fileserver using SQL Management Studio... .right-click on table and open..
Two questions:
Have you refreshed the connection after the restore?
Why do you think the data is not the same?
Have you refreshed the connection after the restore?
Why do you think the data is not the same?
ASKER
I've rebooted the fileserver just to make sure and the data is not the same... why? Because on the old20box i have 182 records in the directors table, in the fileserver, i have 1 record in the directors table.
If you can only see 1 record why can SQL Server see 182?
As you reported earlier:
C:\>sqlcmd -s fileserver -E -Q "select count(*) from nsa..directors;"
-----------
182
If you're still not sure use the following two commands and then check how many rows there are in the directors table:
sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
sqlcmd -s fileserver -E -Q "drop database nsa;"
As you reported earlier:
C:\>sqlcmd -s fileserver -E -Q "select count(*) from nsa..directors;"
-----------
182
If you're still not sure use the following two commands and then check how many rows there are in the directors table:
sqlcmd -s fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
sqlcmd -s fileserver -E -Q "drop database nsa;"
ASKER
That deleted the nsa database from the fileserver.
true, that's what the drop database piece is for.
Now what happens if you run the backup/restore script, how many rows do you get?
Can you post the log when you run it?
Now what happens if you run the backup/restore script, how many rows do you get?
Can you post the log when you run it?
<I'm opening up the directors table that's in the fileserver using SQL Management Studio... .right-click on table and open..>
just a little remark before I leave
We see that that from the place you're backup/restore everything looks OK: no errors and 182 records.
When you say 'from management studio', you mean the management studio from that same location or on the 'fileserver' itself.
'filserver' is not an alias that could be pointing to a diffrent server?
just a little remark before I leave
We see that that from the place you're backup/restore everything looks OK: no errors and 182 records.
When you say 'from management studio', you mean the management studio from that same location or on the 'fileserver' itself.
'filserver' is not an alias that could be pointing to a diffrent server?
ASKER
I did not restore the DB. And to be more specific, i used IP's instead of the servernames... here's the output...
-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-=
C:\>rem backup the database
C:\>sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
21 percent processed.
40 percent processed.
61 percent processed.
80 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 8.
100 percent processed.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 8.
BACKUP DATABASE successfully processed 4850 pages in 4.784 seconds (8.303 MB/sec).
C:\>rem drop existing connections
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore the database
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.818 seconds (6.827 MB/sec).
-=-=-=-=-=-=-=-=-=-=-=-=-=
C:\>rem backup the database
C:\>sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
21 percent processed.
40 percent processed.
61 percent processed.
80 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 8.
100 percent processed.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 8.
BACKUP DATABASE successfully processed 4850 pages in 4.784 seconds (8.303 MB/sec).
C:\>rem drop existing connections
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore the database
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.818 seconds (6.827 MB/sec).
ASKER
@jogos
I go to the actual fileserver's management studio. Also, i changed the cmd file to be more specific with IP's instead of servernames, but it still doesn't work.
I go to the actual fileserver's management studio. Also, i changed the cmd file to be more specific with IP's instead of servernames, but it still doesn't work.
Can you use the script with the count statements in it?
When you say it doesn't work - can you be more specific?
When you say it doesn't work - can you be more specific?
ASKER
I mean that the nsa database on the fileserver wasn't restored. Which script would you like me to use?
Can we just check to see what we agree on?
From the last log results that you posted can you see the following:
1. The nsa database was backed up to the F:\Temp folder
- indicated by
BACKUP DATABASE successfully processed 4850 pages in 4.784 seconds (8.303 MB/sec).
2. The nsa database was set to single user
3. The nsa database was restored to 10.0.0.243 from the backup in the F:\Temp folder
- indicated by
RESTORE DATABASE successfully processed 4850 pages in 5.818 seconds (6.827 MB/sec).
Do you agree with each of these?
Can you now check what databases exist on the fileserver by running the following command:
sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases order by name;"
Does this list contain the nsa database?
From the last log results that you posted can you see the following:
1. The nsa database was backed up to the F:\Temp folder
- indicated by
BACKUP DATABASE successfully processed 4850 pages in 4.784 seconds (8.303 MB/sec).
2. The nsa database was set to single user
3. The nsa database was restored to 10.0.0.243 from the backup in the F:\Temp folder
- indicated by
RESTORE DATABASE successfully processed 4850 pages in 5.818 seconds (6.827 MB/sec).
Do you agree with each of these?
Can you now check what databases exist on the fileserver by running the following command:
sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases order by name;"
Does this list contain the nsa database?
or just run the count with the same connection, this still should give the 182-count
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
If it thous -> Analysing this command? It returns that there are 182 records in a directors-table on the database named nsa on server 10.0.0.243.
<I'm opening up the directors table that's in the fileserver using SQL Management Studio... .right-click on table and open..>
So you log on with other credentials on the 'fileserver' (should be the 10.0.0.243- one since you checked).
I see another gap the select 'from nsa..directors' does not specify a scema. Could there be diffrent direcors-tables in the nsa-database... in diffrent scema's that is.
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
If it thous -> Analysing this command? It returns that there are 182 records in a directors-table on the database named nsa on server 10.0.0.243.
<I'm opening up the directors table that's in the fileserver using SQL Management Studio... .right-click on table and open..>
So you log on with other credentials on the 'fileserver' (should be the 10.0.0.243- one since you checked).
I see another gap the select 'from nsa..directors' does not specify a scema. Could there be diffrent direcors-tables in the nsa-database... in diffrent scema's that is.
ASKER
@Crag
1. Yes, it backs up correctly. That's confirmed
2. I guess so... what's single user?
3. Supposedly....
No, it does not show up in the list. :o(
@jogos
Msg 208, Level 16, State 1, Server FILESERVER, Line1
Invalid object name 'nsa.directors'.
Thanx guys...
1. Yes, it backs up correctly. That's confirmed
2. I guess so... what's single user?
3. Supposedly....
No, it does not show up in the list. :o(
@jogos
Msg 208, Level 16, State 1, Server FILESERVER, Line1
Invalid object name 'nsa.directors'.
Thanx guys...
Que?
I'm puzzled as to why the database restore completes ok yet the database doesn't exist.
Can you put the following in a batch script, capture the output log and post it here:
rem Set database to single user
sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
rem restore database
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
rem check databases 1
sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
rem check databases 2
sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases where name like 'n%' order by name;"
rem count rows
sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
rem check schemas
sqlcmd -s 10.0.0.243 -E -Q "select distinct table_schema from information_schema.tables; "
rem check tables
sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like 'direct%';"
I'm puzzled as to why the database restore completes ok yet the database doesn't exist.
Can you put the following in a batch script, capture the output log and post it here:
rem Set database to single user
sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
rem restore database
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
rem check databases 1
sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
rem check databases 2
sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases where name like 'n%' order by name;"
rem count rows
sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
rem check schemas
sqlcmd -s 10.0.0.243 -E -Q "select distinct table_schema from information_schema.tables;
rem check tables
sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like 'direct%';"
ASKER
I feel your pain. I'll get on that right away!
ASKER
here it is
-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-
C:\>rem Set database to single user
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore database
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.917 seconds (6.713 MB/sec).
C:\>rem check databases 1
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --1------- ------1--- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- --1------- ----1----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- ---------- -----
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 1 90
nsa 1 116.94 MB1OLD20BOX\Administrator 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
C:\>rem check databases 2
C:\>sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases where name like 'n' order by name;"
-------------------------- ---------- ----
(0 rows affected)
C:\>rem count rows
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
C:\>rem check schemas
C:\>sqlcmd -s 10.0.0.243 -E -Q "select distinct table_schema from information_schema.tables; "
table_schema
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
dbo
(1 rows affected)
C:\>rem check tables
C:\>sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like 'direct';"
table_schema 1table_name
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --1------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
(0 rows affected)
-=-=-=-=-=-=-=-=-=-=-=-=-=
C:\>rem Set database to single user
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore database
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.917 seconds (6.713 MB/sec).
C:\>rem check databases 1
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
--------------------------
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
nsa 1 116.94 MB1OLD20BOX\Administrator 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
C:\>rem check databases 2
C:\>sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases where name like 'n' order by name;"
--------------------------
(0 rows affected)
C:\>rem count rows
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
C:\>rem check schemas
C:\>sqlcmd -s 10.0.0.243 -E -Q "select distinct table_schema from information_schema.tables;
table_schema
--------------------------
dbo
(1 rows affected)
C:\>rem check tables
C:\>sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like 'direct';"
table_schema 1table_name
--------------------------
(0 rows affected)
Two things I see from this are:
1. Two of the statements are missing their % symbols - the one for the check databases 2 and check tables. Could you fix and retry?
2. The database has been restored and the directors table has 182 rows in it - all good. However it is owned by the OLD20BOX\Administrator account.
Can you change the owner of the database?
Use the following three statements to change and check:
sqlcmd -s 10.0.0.243 -E -d nas -Q "sp_changedbowner sa;"
sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
1. Two of the statements are missing their % symbols - the one for the check databases 2 and check tables. Could you fix and retry?
2. The database has been restored and the directors table has 182 rows in it - all good. However it is owned by the OLD20BOX\Administrator account.
Can you change the owner of the database?
Use the following three statements to change and check:
sqlcmd -s 10.0.0.243 -E -d nas -Q "sp_changedbowner sa;"
sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
As before please post the log file
ASKER
here you go
-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -
C:\>rem Set database to single user
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore database
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.709 seconds (6.958 MB/sec).
C:\>rem check databases 1
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --1------- ------1--- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- --1------- ----1----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- ---------- -----
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 1 90
nsa 1 116.94 MB1OLD20BOX\Administrator 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
C:\>rem check databases 2
C:\>sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases where name like '' order by name;"
-------------------------- ---------- ----
(0 rows affected)
C:\>rem count rows
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
C:\>rem check schemas
C:\>sqlcmd -s 10.0.0.243 -E -Q "select distinct table_schema from information_schema.tables; "
table_schema
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
dbo
(1 rows affected)
C:\>rem check tables
C:\>sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like '';"
table_schema 1table_name
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --1------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
(0 rows affected)
C:\>sqlcmd -s 10.0.0.243 -E -d nas -Q "sp_changedbowner sa;"
Msg 4060, Level 11, State 1, Server OLD20BOX, Line 1
Cannot open database "nas" requested by the login. The login failed.
Msg 18456, Level 14, State 1, Server OLD20BOX, Line 1
Login failed for user 'OLD20BOX\Administrator'.
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --1------- ------1--- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- --1------- ----1----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- ---------- -----
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 1 90
nsa 1 116.94 MB1OLD20BOX\Administrator 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
-=-=-=-=-=-=-=-=-=-=-=-=-=
C:\>rem Set database to single user
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore database
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.709 seconds (6.958 MB/sec).
C:\>rem check databases 1
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
--------------------------
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
nsa 1 116.94 MB1OLD20BOX\Administrator 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
C:\>rem check databases 2
C:\>sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases where name like '' order by name;"
--------------------------
(0 rows affected)
C:\>rem count rows
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
C:\>rem check schemas
C:\>sqlcmd -s 10.0.0.243 -E -Q "select distinct table_schema from information_schema.tables;
table_schema
--------------------------
dbo
(1 rows affected)
C:\>rem check tables
C:\>sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like '';"
table_schema 1table_name
--------------------------
(0 rows affected)
C:\>sqlcmd -s 10.0.0.243 -E -d nas -Q "sp_changedbowner sa;"
Msg 4060, Level 11, State 1, Server OLD20BOX, Line 1
Cannot open database "nas" requested by the login. The login failed.
Msg 18456, Level 14, State 1, Server OLD20BOX, Line 1
Login failed for user 'OLD20BOX\Administrator'.
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
--------------------------
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
nsa 1 116.94 MB1OLD20BOX\Administrator 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
Apologies I mistyped the name of the database, try these three again:
sqlcmd -s 10.0.0.243 -E -d nsa -Q "sp_changedbowner sa;"
sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
sqlcmd -s 10.0.0.243 -E -d nsa -Q "sp_changedbowner sa;"
sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
ASKER
here you go:
-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -
C:\>rem Set database to single user
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore database
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.943 seconds (6.684 MB/sec).
C:\>rem check databases 1
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --1------- ------1--- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- --1------- ----1----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- ---------- -----
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 1 90
nsa 1 116.94 MB1OLD20BOX\Administrator 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
C:\>rem check databases 2
C:\>sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases where name like '' order by name;"
-------------------------- ---------- ----
(0 rows affected)
C:\>rem count rows
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
C:\>rem check schemas
C:\>sqlcmd -s 10.0.0.243 -E -Q "select distinct table_schema from information_schema.tables; "
table_schema
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
dbo
(1 rows affected)
C:\>rem check tables
C:\>sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like '';"
table_schema 1table_name
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --1------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
(0 rows affected)
C:\>sqlcmd -s 10.0.0.243 -E -d nsa -Q "sp_changedbowner sa;"
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --1------- ------1--- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- --1------- ----1----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----1---- ---------- -----
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 1 90
nsa 1 116.94 MB1sa 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 1 90
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
-=-=-=-=-=-=-=-=-=-=-=-=-=
C:\>rem Set database to single user
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
C:\>rem restore database
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.943 seconds (6.684 MB/sec).
C:\>rem check databases 1
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
--------------------------
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
nsa 1 116.94 MB1OLD20BOX\Administrator 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
C:\>rem check databases 2
C:\>sqlcmd -s 10.0.0.243 -E -Q "select substring(name,1,40) from master.sys.databases where name like '' order by name;"
--------------------------
(0 rows affected)
C:\>rem count rows
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
C:\>rem check schemas
C:\>sqlcmd -s 10.0.0.243 -E -Q "select distinct table_schema from information_schema.tables;
table_schema
--------------------------
dbo
(1 rows affected)
C:\>rem check tables
C:\>sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like '';"
table_schema 1table_name
--------------------------
(0 rows affected)
C:\>sqlcmd -s 10.0.0.243 -E -d nsa -Q "sp_changedbowner sa;"
C:\>sqlcmd -s 10.0.0.243 -E -Q "sp_helpdb;"
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
--------------------------
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
nsa 1 116.94 MB1sa 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
C:\>sqlcmd -s 10.0.0.243 -E -Q "select count(*) from nsa..directors;"
-----------
182
(1 rows affected)
ASKER
Any idea?
Ok.
I can see from this that the nsa database was restored to the system with the IP address 10.0.0.243.
It is the only user database on the system and is now owned by the sa login.
The nsa..directors table has 182 rows.
Can you start the SQL Management Studio, connect to the 10.0.0.243 server using the sa account (or an account with SysAdmin privs) and then run the following query:
select count(*) from nsa..directors;
How many rows do you get?
I can see from this that the nsa database was restored to the system with the IP address 10.0.0.243.
It is the only user database on the system and is now owned by the sa login.
The nsa..directors table has 182 rows.
Can you start the SQL Management Studio, connect to the 10.0.0.243 server using the sa account (or an account with SysAdmin privs) and then run the following query:
select count(*) from nsa..directors;
How many rows do you get?
<
@jogos
Msg 208, Level 16, State 1, Server FILESERVER, Line1
Invalid object name 'nsa.directors'.
>
I think you misunderstood me : 'nsa.directors' will give an error while you don't menion a schema where 'nsa..directors' will select from your default schema.
And the select craig typed for you was not implemented (you did it without the %-character)
sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like 'direct%';"
back to top
@jogos
Msg 208, Level 16, State 1, Server FILESERVER, Line1
Invalid object name 'nsa.directors'.
>
I think you misunderstood me : 'nsa.directors' will give an error while you don't menion a schema where 'nsa..directors' will select from your default schema.
And the select craig typed for you was not implemented (you did it without the %-character)
sqlcmd -s 10.0.0.243 -E -Q "select table_schema, table_name from information_schema.tables where table_name like 'direct%';"
back to top
ASKER
@crag
here's the error i got:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'nsa..directors'.
here's the error i got:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'nsa..directors'.
Can you check that you get the same error and then run the following command:
select @@servername
select @@servername
ASKER
returned FILESERVER
Very odd - the log shows that the database has been restored yet you're not able to access it.
Might be a security issue.
Can you please run the following command as-is and post the results:
sqlcmd -s 10.0.0.243 -E -Q "select @@servername; select left(name,30) from master..sysdatabases; select sl.name from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid;"
Might be a security issue.
Can you please run the following command as-is and post the results:
sqlcmd -s 10.0.0.243 -E -Q "select @@servername; select left(name,30) from master..sysdatabases; select sl.name from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid;"
ASKER
output:
-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -
C:\>sqlcmd -s 10.0.0.243 -E -Q "select @@servername; select left(name,30) from master..sysdatabases; select sl.name from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid;"
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
FILESERVER
(1 rows affected)
-------------------------- ----
master
tempdb
model
msdb
(4 rows affected)
name
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
FILESERVER\Administrator
(1 rows affected)
-=-=-=-=-=-=-=-=-=-=-=-=-=
C:\>sqlcmd -s 10.0.0.243 -E -Q "select @@servername; select left(name,30) from master..sysdatabases; select sl.name from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid;"
--------------------------
FILESERVER
(1 rows affected)
--------------------------
master
tempdb
model
msdb
(4 rows affected)
name
--------------------------
FILESERVER\Administrator
(1 rows affected)
Still very off. The database restore completed and was list yet not it isn't
We'll have to go one step at a time to find this one.
Try this script and post the results.
sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace; select @@servername; select sl.name, sl.sysadmin from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid; sp_helpdb; select left(name,30) from master..sysdatabases;"
We'll have to go one step at a time to find this one.
Try this script and post the results.
sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
ASKER
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
Msg 102, Level 15, State 1, Server FILESERVER, Line 1
Incorrect syntax near 'sp_helpdb'.
ASKER
wanna IM?
Try this one...
sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace; select @@servername; select sl.name, sl.sysadmin from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid; exec sp_helpdb; select left(name,30) from master..sysdatabases;"
sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
ASKER
C:\>sqlcmd -s 10.0.0.243 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
Msg 5011, Level 14, State 5, Server FILESERVER, Line 1
User does not have permission to alter database 'nsa' or the database does not exist.
Msg 5069, Level 16, State 1, Server FILESERVER, Line 1
ALTER DATABASE statement failed.
Msg 3201, Level 16, State 2, Server FILESERVER, Line 1
Cannot open backup device 'F:\Temp\testdb_backup_.BA
Msg 3013, Level 16, State 1, Server FILESERVER, Line 1
RESTORE DATABASE is terminating abnormally.
--------------------------
FILESERVER
(1 rows affected)
name 1sysadmin
--------------------------
FILESERVER\Administrator 1 0
(1 rows affected)
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
--------------------------
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 1 6.50 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
tempdb 1 8.50 MB1sa 1 21Oct 12 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
--------------------------
master
tempdb
model
msdb
(4 rows affected)
Oh - the database backup has been deleted.
Here is a revised script that creates the backup first.
Please run as-is and post the output log.
sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace; select @@servername; select sl.name, sl.sysadmin from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid; exec sp_helpdb; select left(name,30) from master..sysdatabases;"
Here is a revised script that creates the backup first.
Please run as-is and post the output log.
sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
ASKER
C:\>sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
21 percent processed.
40 percent processed.
61 percent processed.
80 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 2.
100 percent processed.
Any result from the second statement?
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace; select @@servername; select sl.name, sl.sysadmin from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid; exec sp_helpdb; select left(name,30) from master..sysdatabases;"
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
ASKER
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
Msg 3201, Level 16, State 2, Server FILESERVER, Line 1
Cannot open backup device 'F:\Temp\testdb_backup_.BA
Msg 3013, Level 16, State 1, Server FILESERVER, Line 1
RESTORE DATABASE is terminating abnormally.
--------------------------
FILESERVER
(1 rows affected)
name 1sysadmin
--------------------------
FILESERVER\Administrator 1 0
(1 rows affected)
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
--------------------------
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 1 6.50 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
tempdb 1 8.50 MB1sa 1 21Oct 12 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
--------------------------
master
tempdb
model
msdb
(4 rows affected)
IT failed this time because the backup file created last time is now missing.
Can you please run both statements one after the other?
sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace; select @@servername; select sl.name, sl.sysadmin from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid; exec sp_helpdb; select left(name,30) from master..sysdatabases;"
Can you please run both statements one after the other?
sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
ASKER
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
Msg 3201, Level 16, State 2, Server FILESERVER, Line 1
Cannot open backup device 'F:\Temp\testdb_backup_.BA
Msg 3013, Level 16, State 1, Server FILESERVER, Line 1
RESTORE DATABASE is terminating abnormally.
--------------------------
FILESERVER
(1 rows affected)
name 1sysadmin
--------------------------
FILESERVER\Administrator 1 0
(1 rows affected)
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
--------------------------
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 1 6.50 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
tempdb 1 8.50 MB1sa 1 21Oct 12 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
--------------------------
master
tempdb
model
msdb
(4 rows affected)
This still failed due to a missing backup file.
What happened to the first statement?
Can you please run the following two statements, one at a time and post both log files:
sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace; select @@servername; select sl.name, sl.sysadmin from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid; exec sp_helpdb; select left(name,30) from master..sysdatabases;"
What happened to the first statement?
Can you please run the following two statements, one at a time and post both log files:
sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
ASKER
C:\>sqlcmd -s 10.0.0.28 -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
21 percent processed.
40 percent processed.
61 percent processed.
80 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 4.
100 percent processed.
-=-=-=-=-=-=-=-=-=-=-=-=-=
C:\>sqlcmd -s 10.0.0.243 -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 4848 pages for database 'nsa', file 'nsa' on file 1.
Processed 2 pages for database 'nsa', file 'nsa_log' on file 1.
RESTORE DATABASE successfully processed 4850 pages in 5.902 seconds (6.730 MB/sec).
--------------------------
OLD20BOX
(1 rows affected)
name 1sysadmin
--------------------------
OLD20BOX\Administrator 1 0
(1 rows affected)
name 1db_size 1owner 1dbid 1created 1status 1compatibility_level
--------------------------
master 1 4.50 MB1sa 1 11Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1 1.69 MB1sa 1 31Apr 8 20031Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 1 5.94 MB1sa 1 41Oct 14 20051Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
nsa 1 116.94 MB1sa 1 51Oct 9 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
tempdb 1 8.50 MB1sa 1 21Oct 11 20071Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
--------------------------
master
tempdb
model
msdb
nsa
(5 rows affected)
eh?
The first statement runs against the 10.0.0.28 server and backs up the database. so far so good.
The second statement runs against the 10.0.0.243 server and restores the database but then immediately reports that this is the OLD20BOX. Surely not!?!?!?!
I thought you were backing up from the OLD20BOXand restoring to the FILESERVER, not the otherway around?
Can you please triple check which system has which IP address?
run an ipconfig on the systems themselves and then using the ping -a command on the client
The first statement runs against the 10.0.0.28 server and backs up the database. so far so good.
The second statement runs against the 10.0.0.243 server and restores the database but then immediately reports that this is the OLD20BOX. Surely not!?!?!?!
I thought you were backing up from the OLD20BOXand restoring to the FILESERVER, not the otherway around?
Can you please triple check which system has which IP address?
run an ipconfig on the systems themselves and then using the ping -a command on the client
ASKER
The 10...28 is the OLD20Box and the 243 is the Fileserver. For sure.
Doesn't seem like it. Use these two scripts to check
sqlcmd -s 10.0.0.28 -E -Q "select @@servername;"
sqlcmd -s 10.0.0.243 -E -Q "select @@servername;"
sqlcmd -s 10.0.0.28 -E -Q "select @@servername;"
sqlcmd -s 10.0.0.243 -E -Q "select @@servername;"
ASKER
Okay, I ran both sqlcmd's on each machine, here's what i got
machine1
cmd1 = OLD20BOX
cmd2 = OLD20BOX
machine2
cmd1 = FILESERVER
cmd2 = FILESERVER
... Interesting... what do you think?
machine1
cmd1 = OLD20BOX
cmd2 = OLD20BOX
machine2
cmd1 = FILESERVER
cmd2 = FILESERVER
... Interesting... what do you think?
Well as each command should connect to a different database server and return their names I suspect that you have a small problem with your IP addresses. The result should have been:
machine1
cmd1 = OLD20BOX
cmd2 = FILESERVER
machine2
cmd1 = OLD20BOX
cmd2 = FILESERVER
Lets check and see.
On both machine 1 and machine 2 can you please run the following command and post the results:
ipconfig /all
machine1
cmd1 = OLD20BOX
cmd2 = FILESERVER
machine2
cmd1 = OLD20BOX
cmd2 = FILESERVER
Lets check and see.
On both machine 1 and machine 2 can you please run the following command and post the results:
ipconfig /all
ASKER
Machine1:
Windows 2000 IP Configuration
Host Name . . . . . . . . . . . . : old20box
Primary DNS Suffix . . . . . . . :
Node Type . . . . . . . . . . . . : Broadcast
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : NVIDIA nForce Networking Controller
Physical Address. . . . . . . . . : 00-50-8D-F9-54-03
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : 10.0.0.28
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.0.0.1
DNS Servers . . . . . . . . . . . : 10.0.0.104
10.0.0.219
-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -
Machine2
Windows IP Configuration
Host Name . . . . . . . . . . . . : fileserver
Primary Dns Suffix . . . . . . . :
Node Type . . . . . . . . . . . . : Unknown
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Realtek RTL8139 Family PCI Fast Ethernet
NIC
Physical Address. . . . . . . . . : 00-0D-61-80-84-B3
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : 10.0.0.243
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.0.0.1
DNS Servers . . . . . . . . . . . : 10.0.0.104
10.0.0.219
Windows 2000 IP Configuration
Host Name . . . . . . . . . . . . : old20box
Primary DNS Suffix . . . . . . . :
Node Type . . . . . . . . . . . . : Broadcast
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : NVIDIA nForce Networking Controller
Physical Address. . . . . . . . . : 00-50-8D-F9-54-03
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : 10.0.0.28
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.0.0.1
DNS Servers . . . . . . . . . . . : 10.0.0.104
10.0.0.219
-=-=-=-=-=-=-=-=-=-=-=-=-=
Machine2
Windows IP Configuration
Host Name . . . . . . . . . . . . : fileserver
Primary Dns Suffix . . . . . . . :
Node Type . . . . . . . . . . . . : Unknown
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Realtek RTL8139 Family PCI Fast Ethernet
NIC
Physical Address. . . . . . . . . : 00-0D-61-80-84-B3
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : 10.0.0.243
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.0.0.1
DNS Servers . . . . . . . . . . . : 10.0.0.104
10.0.0.219
Oh my god,
the -s should be a -S
the -s should be a -S
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi jogos. You are right the parameter in the command should be an upper case S. Must have missed it transferring it from my test script to here. It defaults to the local box - ouch!!!!
OK so now we got to go back a step and run these two commands:
sqlcmd -S 10.0.0.28 -E -Q "select @@servername; backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
sqlcmd -S 10.0.0.243 -E -Q "select @@servername; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace; select sl.name, sl.sysadmin from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid; exec sp_helpdb; select left(name,30) from master..sysdatabases;"
OK so now we got to go back a step and run these two commands:
sqlcmd -S 10.0.0.28 -E -Q "select @@servername; backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
sqlcmd -S 10.0.0.243 -E -Q "select @@servername; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
ASKER
here's the output:
C:\>sqlcmd -S 10.0.0.28 -E -Q "select @@servername; backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
OLD20BOX
(1 rows affected)
21 percent processed.
(1 rows affected)
40 percent processed.
(1 rows affected)
61 percent processed.
(1 rows affected)
80 percent processed.
(1 rows affected)
Processed 4848 pages for database 'nsa', file 'nsa' on file 6.
(1 rows affected)
100 percent processed.
(1 rows affected)
Processed 1 pages for database 'nsa', file 'nsa_log' on file 6.
(1 rows affected)
BACKUP DATABASE successfully processed 4849 pages in 4.643 seconds (8.555 MB/sec).
C:\>sqlcmd -S 10.0.0.243 -E -Q "select @@servername; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace; select sl.name, sl.sysadmin from master..sysprocesses sp join master..syslogins sl on sp.sid = sl.sid where @@spid = spid; exec sp_helpdb; select left(name,30) from master..sysdatabases;"
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
FILESERVER
Msg 3201, Level 16, State 2, Server FILESERVER, Line 1
Cannot open backup device 'F:\Temp\testdb_backup_.BA K'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Server FILESERVER, Line 1
RESTORE DATABASE is terminating abnormally.
(1 rows affected)
name sysadmin
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -- -----------
FILESERVER\Administrator 0
(1 rows affected)
name db_size owner dbid created status compatibility_level
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -- ------------- -------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -- ------ ----------- -------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---- -------------------
master 4.50 MB sa 1 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 90
model 1.69 MB sa 3 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 90
msdb 6.50 MB sa 4 Oct 14 2005 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90
tempdb 8.50 MB sa 2 Oct 16 2007 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener al_CP1_CI_ AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 90
-------------------------- ----
master
tempdb
model
msdb
(4 rows affected)
C:\>sqlcmd -S 10.0.0.28 -E -Q "select @@servername; backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
--------------------------
OLD20BOX
(1 rows affected)
21 percent processed.
(1 rows affected)
40 percent processed.
(1 rows affected)
61 percent processed.
(1 rows affected)
80 percent processed.
(1 rows affected)
Processed 4848 pages for database 'nsa', file 'nsa' on file 6.
(1 rows affected)
100 percent processed.
(1 rows affected)
Processed 1 pages for database 'nsa', file 'nsa_log' on file 6.
(1 rows affected)
BACKUP DATABASE successfully processed 4849 pages in 4.643 seconds (8.555 MB/sec).
C:\>sqlcmd -S 10.0.0.243 -E -Q "select @@servername; restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
--------------------------
FILESERVER
Msg 3201, Level 16, State 2, Server FILESERVER, Line 1
Cannot open backup device 'F:\Temp\testdb_backup_.BA
Msg 3013, Level 16, State 1, Server FILESERVER, Line 1
RESTORE DATABASE is terminating abnormally.
(1 rows affected)
name sysadmin
--------------------------
FILESERVER\Administrator 0
(1 rows affected)
name db_size owner dbid created status compatibility_level
--------------------------
master 4.50 MB sa 1 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
model 1.69 MB sa 3 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_Gener
msdb 6.50 MB sa 4 Oct 14 2005 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
tempdb 8.50 MB sa 2 Oct 16 2007 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_Gener
--------------------------
master
tempdb
model
msdb
(4 rows affected)
<FILESERVER
Msg 3201, Level 16, State 2, Server FILESERVER, Line 1
Cannot open backup device 'F:\Temp\testdb_backup_.BA K'. Operating system error 3(The system cannot find the path specified.).
>
The f:\temp-location should be from the point of view of the server on witch you restore, so Fileserver.
Msg 3201, Level 16, State 2, Server FILESERVER, Line 1
Cannot open backup device 'F:\Temp\testdb_backup_.BA
>
The f:\temp-location should be from the point of view of the server on witch you restore, so Fileserver.
As per Jogos - the F drive needs to be shared and accessible by both systems.
It can be perfect that the share on the OLD20BOX is accessible as F:\temp and that on FILESERVER this is mapped on another drive , for example G:\temp.
Logon to FILESERVER and look how this is accessible, and of course credentials need to be sufficient
(sorry but I'm off again)
Logon to FILESERVER and look how this is accessible, and of course credentials need to be sufficient
(sorry but I'm off again)
ASKER
I have that section all setup properly from the getgo.. Running on OLD20Box, f:\ is on the fileserver. Scripts are running on the old20box, not the fileserver. On the fileserver f:\ is actually it's c:\ drive.
ASKER
thanx guys for all your help so far!
does that mean you have the script working?
ASKER
No, i just wanted to say thanks for all your efforts.
Ok lets see if we can close this one off then before we get to 100 comments.
Now that we've established that I can't type here are the corrected versions of the scripts:
rem backup the database
sqlcmd -S old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20;"
rem drop existing connections
sqlcmd -S fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
rem restore the database
sqlcmd -S fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA K' with stats = 20, replace;"
Now that we've established that I can't type here are the corrected versions of the scripts:
rem backup the database
sqlcmd -S old20box -E -Q "backup database nsa to disk = 'F:\Temp\testdb_backup_.BA
rem drop existing connections
sqlcmd -S fileserver -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback immediate;"
rem restore the database
sqlcmd -S fileserver -E -Q "restore database nsa from disk = 'F:\Temp\testdb_backup_.BA
@crag
<On the fileserver f:\ is actually it's c:\ drive.>
remember that this already tried, but the gave a message because the F: is actualy the C:-drive on Fileserver , but engineroom did not mentioned the result
rem restore the database
sqlcmd -S fileserver -E -Q "restore database nsa from disk = C:\Temp\testdb_backup_.BAK ' with stats = 20, replace;"
<On the fileserver f:\ is actually it's c:\ drive.>
remember that this already tried, but the gave a message because the F: is actualy the C:-drive on Fileserver , but engineroom did not mentioned the result
rem restore the database
sqlcmd -S fileserver -E -Q "restore database nsa from disk = C:\Temp\testdb_backup_.BAK
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It worked! Yes... now just a quick question... remember way back when, when we changed owners or users on the database... does that still play a role or everything is at peace with the universe?
ASKER
Also, how do you guys want me to distribute the points? I would love to give 1000 pts each, but i can't.. let me know.
Points : accepted sollution and assisted sollution shares the points
owners and users (You're pushin it :) ) still is something to keep your mind on, ex. a user that already exists on the other server but with other rights could be a problem when for example some action needs administrator rights.....
owners and users (You're pushin it :) ) still is something to keep your mind on, ex. a user that already exists on the other server but with other rights could be a problem when for example some action needs administrator rights.....
ASKER
I was referring to earlier when he made a change to a user. Wondering if that still comes into play or not. Maybe i'm wrong.
<If the restore fails due to users connected you'll need a script to drop their connections.>
I think in most cases you will want to know if someone is working an the database when you want to restore it.
Think also on the fact that the whole week you were backing up a database and restoring it on the same server some small moment later. I hope it wasn't a 'live' database, people could had some strange feeling using it.
I think in most cases you will want to know if someone is working an the database when you want to restore it.
Think also on the fact that the whole week you were backing up a database and restoring it on the same server some small moment later. I hope it wasn't a 'live' database, people could had some strange feeling using it.
ASKER
@jogos
Of course it was not a live webserver. There were 2 test servers., BUT...
I am now trying to transport this to the (2) live servers that i'm going to use. And it does not work! :( Arg...! Here's the output. (p.s.... the share for the "S:\" does exist)
-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -=-=-=-=-= -
C:\>sqlcmd -S 10.0.0.4 -E -Q "backup database nsa to disk = 'S:\Temp\testdb_back
up_.BAK' with stats = 20;"
Msg 3201, Level 16, State 1, Server ERDM-FIRE2, Line 1
Cannot open backup device 'S:\Temp\testdb_backup_.BA K'. Operating system error 3
(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Server ERDM-FIRE2, Line 1
BACKUP DATABASE is terminating abnormally.
C:\>rem drop existing connections
C:\>sqlcmd -S 10.0.0.124 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback
immediate;"
Msg 5011, Level 14, State 5, Server ERDM-XM5L58JJCY, Line 1
User does not have permission to alter database 'nsa' or the database does not e
xist.
Msg 5069, Level 16, State 1, Server ERDM-XM5L58JJCY, Line 1
ALTER DATABASE statement failed.
C:\>rem restore the database
C:\>sqlcmd -S 10.0.0.124 -E -Q "restore database nsa from disk = 'C:\Temp\testdb
_backup_.BAK' with stats = 20, replace;"
Msg 3201, Level 16, State 2, Server ERDM-XM5L58JJCY, Line 1
Cannot open backup device 'C:\Temp\testdb_backup_.BA K'. Operating system error 2
(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server ERDM-XM5L58JJCY, Line 1
RESTORE DATABASE is terminating abnormally.
Of course it was not a live webserver. There were 2 test servers., BUT...
I am now trying to transport this to the (2) live servers that i'm going to use. And it does not work! :( Arg...! Here's the output. (p.s.... the share for the "S:\" does exist)
-=-=-=-=-=-=-=-=-=-=-=-=-=
C:\>sqlcmd -S 10.0.0.4 -E -Q "backup database nsa to disk = 'S:\Temp\testdb_back
up_.BAK' with stats = 20;"
Msg 3201, Level 16, State 1, Server ERDM-FIRE2, Line 1
Cannot open backup device 'S:\Temp\testdb_backup_.BA
(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Server ERDM-FIRE2, Line 1
BACKUP DATABASE is terminating abnormally.
C:\>rem drop existing connections
C:\>sqlcmd -S 10.0.0.124 -E -Q "ALTER DATABASE nsa SET SINGLE_USER WITH rollback
immediate;"
Msg 5011, Level 14, State 5, Server ERDM-XM5L58JJCY, Line 1
User does not have permission to alter database 'nsa' or the database does not e
xist.
Msg 5069, Level 16, State 1, Server ERDM-XM5L58JJCY, Line 1
ALTER DATABASE statement failed.
C:\>rem restore the database
C:\>sqlcmd -S 10.0.0.124 -E -Q "restore database nsa from disk = 'C:\Temp\testdb
_backup_.BAK' with stats = 20, replace;"
Msg 3201, Level 16, State 2, Server ERDM-XM5L58JJCY, Line 1
Cannot open backup device 'C:\Temp\testdb_backup_.BA
(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server ERDM-XM5L58JJCY, Line 1
RESTORE DATABASE is terminating abnormally.
<
Msg 5011, Level 14, State 5, Server ERDM-XM5L58JJCY, Line 1
User does not have permission to alter database 'nsa' or the database does not e
xist.
>
Permissions! For example because the user with witch sqlcmd is executed has sysadmin-rights on the testservers but not on production.
With the parameters -U (user) and -P (pasword) you can run it with other credentials (but don't post it here :) )
Msg 5011, Level 14, State 5, Server ERDM-XM5L58JJCY, Line 1
User does not have permission to alter database 'nsa' or the database does not e
xist.
>
Permissions! For example because the user with witch sqlcmd is executed has sysadmin-rights on the testservers but not on production.
With the parameters -U (user) and -P (pasword) you can run it with other credentials (but don't post it here :) )
The backup command may have failed if the Temp folder does not exist on th S drive. The command will not automaticaly create it.
The second command failed due to permissions. You need to make sure your windows account has the correct permissions to run these commands on SQL Server. Jogos provided the options to use if you want to use a SQL Login.
The third command failed due to the lack of backup - from the first failed command.
The second command failed due to permissions. You need to make sure your windows account has the correct permissions to run these commands on SQL Server. Jogos provided the options to use if you want to use a SQL Login.
The third command failed due to the lack of backup - from the first failed command.
ASKER
Thanx guys for all your help! I really appreciate it!
er
er