Automating A Backup And Restore Across Servers
Jan 10, 2005
Hi,
I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?
Thanks,
Maria
View 1 Replies
ADVERTISEMENT
Jun 23, 2015
We are told that we cannot get remote access on our DB servers , can access them only via SQL management studio. But we are the one's who will be restoring all our DB's from current setup to new servers.
My Question:
1. Can we restore the DB's directly via SQL Management studio(from other server where management studio is installed) without logging into the DB server.
2. How do we set the mdf/ldf file paths, assuming we have elevated access on the DB. Is this possible?.
Any other blockers we can face?
View 1 Replies
View Related
Sep 19, 2000
Hello All,
Our OLAP environment involves an ETL/Data Warehouse/Data Mart server and a cube publisher server.
We would like to learn how to automate the Archival/Restore of OLAP databases. We are currently doing
it manually though OLAP Manager. Any help would be appreciated. Thanks. James.
--
James E. Bothamley
Senior Database Administrator
Dave & Buster's, Inc.
2481 Manana
Dallas, TX 75220
Work
Phone (214) 904-2296
email jbothaml@DaveAndBusters.Com
"Once in a while you can get shown the light
in the strangest of places if you look at it right"
JG 1942-1995 RIP
View 1 Replies
View Related
Jun 25, 2015
I am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them.
2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
View 4 Replies
View Related
Aug 3, 2015
Need to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
View 8 Replies
View Related
Apr 1, 2008
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
View 3 Replies
View Related
Oct 17, 2006
I have a full backup and several diff backup,now i want to restore
firstly,I restore full backup
RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO
it's working,then i don;'t know how to continue
Thanks in advance
View 3 Replies
View Related
Oct 14, 2007
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.
View 16 Replies
View Related
May 9, 2008
Get the following error when trying to restore from one SQL 2005 server (64-bit, standard edition) to another (32-bit, enterprise edition).
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
View 3 Replies
View Related
Apr 28, 2008
Hi there
I have a concern about restore whole SQL server environment.
1st server:
x86 SQL Server 2005 EE SP2 + hotfix 3159 is installed on Windows 2003 SP1 R2. SQL Server is running under Local System Account. Server is not part of AD.
2nd server
Win XP SP2, x86 SQL Server 2005 DE SP2 + hotfix 3159. SQL Server is running under Local Admin Account. Server is part of AD. Server has Credentials and linked servers
I'm trying to move all databases from server 2 to server 1
For this I am doing:
1) Create backup SMK
2) Detach all user databases
3) Backup all system databases
4) Copy all this stuff to server 1
5) Run Server 1 in single-mode (-m)
6) Restore master database
7) Try to restore backup SMK that was done on step 1 and get this error message:
Msg 15329, Level 16, State 11, Line 2
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.
My understanding is that credential secrets, linked server login passwords are lost and should be recreated. DbMK can be restored using passwords.
As far as I know this can be avoided if the same domain account is using for both servers. But the question still this - if I have stand alone server, using local system account for the SQL Server service and have encrypted objects - I cannot restore SQL Server on another box (say in case disaster lost original one) without loosing credentials and linked servers even if I have all backups including backup of SMK.
Sounds gloomily for me. Any idea what I missed?
PS This links
http://blogs.msdn.com/lcris/archive/2005/09/23/473464.aspx
http://blogs.msdn.com/lcris/archive/2005/07/08/437048.aspx
http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx
http://blogs.msdn.com/lcris/archive/2006/04/10/572678.aspx
and all relative topics in BOL were studied very carefully
Regards,
Gennadi
View 4 Replies
View Related
Jun 30, 2000
I am trying to install failover support for my sql server 7.0 database on the in clustere server enviorment
Now We have operating system on two clusterd servers but just one common database disk
Now what will happen if i lose server A ( Active ) the server B will take over and use the same database . But what will
happen if database disk crashes IS it possible for me to replicate the data or use disk mirroring , or have one clustered
database server seperate
How does the database server cluster work ?
What kind of replication do i use for my backup support ?
what should be my recovery process ?
how can i use standby option on the database during recovery ?
Please try to reply as soon as possible
if you can also point to some documetation on the net that will also help
Also look at my current structure ( but this should not affect your answer )
Tables
group 1 --- 20 tables ( being replicated to server c )
group 2 ----60 tables
on cluster 1
Server A-- Holds Group 1 ( Transictional replication to server c )
on cluster 2
server B---(Production server ) Group 2 + Group 1
View 1 Replies
View Related
Aug 10, 1999
Hello!
We are planning to build Primary SQL Server 6.5 and Backup SQL Server 6.5.
In case of media failure on primary server what kind of options should
we set if we want all users automatically switch to backup server until recovery process on primary server take place?
Thank you in advance,
Alona
View 2 Replies
View Related
Apr 14, 2004
How to backup database on the other servers in the LANS.
View 8 Replies
View Related
Jul 26, 2015
I am wanting to use backup compression on a few sql servers (2008R2 and 2012). I have never touched compression before and always just gone with the default.I use Ola Hallengren's scripts to do the backups which, if not specifically specified, will use the server default.Backups (FULL and LOG) have been happening successfully on these servers for years.SQL won't care that the previous backup in the set was uncompressed or that the hourly transaction log backups previously taken were uncompressed?Restore statements (T-SQL) will be identical?From everything I am reading it is simply a case of setting the configuration and acting like nothing changed but I just wanted to be 100% certain.1 of the servers is a SharePoint backend.All of the backup files from all servers are picked up by Commvault backup system.
View 4 Replies
View Related
Sep 14, 2007
I ran the following code on one of our servers and I got some surprising results. Those results included backups for that machine but also showed entries for other servers. I don't understand it! I can understand the results being wrong because I coded something incorrectly, but why would another server be listed in bs.server_Name? It appears that the entries are "correct" in that backups were taken on the other server at the time given, but why is it there??? (I put the code together to list the most recent backup for each database on the instance. I hope it is correct.)
select @@ServerName, bs.server_Name, bs.Database_Name,
bmf.physical_Device_Name,
bs.backup_start_date, bs.backup_finish_date,
Case When bs.backup_start_date < Getdate() - 1 Then 'Stale' Else 'Recent' End as Status,
Case When bs.backup_finish_date is NULL Then 'Failed' Else 'Successful' End as Success
from msdb.dbo.backupset bs
Join msdb.dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id
where type = 'd'
and bs.backup_set_id = (select max (bso.backup_set_id)
from msdb.dbo.backupset bso
where bso.server_name = bs.server_Name
and bso.Database_Name = bs.Database_Name
and bso.Type = 'D')
order by database_name
View 1 Replies
View Related
Apr 10, 2000
Hello out there.
I am currently testing the transmission rates of sql server 70 backups over an ATM circuit (T3) from tampa, fl to detroit. We are evaluating what we need to do to relocate our warm backup database servers to our Disaster Recovery site in detroit. I have tried several types of transmissions and are not receiving any transfer rates that will reasonably work with a recovery plan. I have tried copying a file from source to target, I have tried backing up from source to target, I have tried FTP'ing files, and never can achieve higher transmission rate then 1 gig per hour.
I am looking to talk to anyone out there that may have implemented a long distance sql servber DR site or anyone who is thinking about it. Or anyone who has any input on how I might be able to achieve a higher transmission rate over a T3. Or anyone who is using SAN's in both their local and DR site and might be using special synchronizing software. I am currently piloting a Metastore SAN and a Compaq SAN to solve some local disk management problems and eventually transmit data from SAN to SAN for DR.
Please, if you are involved with any of these strategies and would like someone to share your knowledge with or if I can be of help to you by sharing our testing knowledge, please write asap.
Thanks.
Gail Wade
Database Administration
Raymond James Financial
gwade@it.rjf.com
View 3 Replies
View Related
Sep 14, 2004
Hi,
I am creating a job that runs this command:
EXEC xp_cmdshell 'copy f:dados_sqlmssqlackup
ecom_tb_basico.bak \stalingrado_2c$ emp',
The result of this job is:
"Access is denied.
0 file(s) copied.
NULL"
But I am running this command with sa user.....
Wich kind of permission is missing to execute this copy?
When I execute the same command to copy the backup from the server to itself, it works fine!!!!
Does someone have an idea to solve this problem?????
View 3 Replies
View Related
May 17, 2006
I had to replace my hard drive which was going bad. I reinstalled Sql 2005 and now need to restore the backup that I have. I selected Restore Database and chose From Device, and selected the backup location on J: drive and selected the backup file which has no extenstion. I checked the checkbox next to the backup set to restore. Click OK and then I get this error message:
Restore failed for Server
System.Data.SqlClient.SqlError: Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLdataMyDB_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
I don't know why it is looking for a MDF file as this was never in the Backup folder. Can someone please help me on this? I thought I was doing the right thing making a backup all along and now I can't restore it!
View 2 Replies
View Related
Dec 17, 2001
Is it possisable to restore from tape when the backup was taken from a
different server. ServerA has the tape drive which performs the backups
to tape, but ServerB which is under a different domain wants to restore ServerA database on ServerB, both databases on the two servers are the same
is it possiable to restore a database on ServerB using ServerA file?
View 2 Replies
View Related
May 7, 2001
hello all !!
I suffered a crash on one of my disks on a server runing MS-sql 7.0,and found out my backupexec dosnt really restore a runing version of my database.
which solution would you recomend ,taking in acount i may have a copy of the database on another computer,which files i can restore?
thanks
yochai lam
View 2 Replies
View Related
May 24, 2001
Hi I am operating my databases under "trunc log on checkpoint" mode as I do not need point of failure recoverability.I was performing a recovery test using a backup which resulted in it being unsuccessful .Error Message received was
"Recovery has failed because a nonlogged operation could not be redone.Use the RESTORE statement to restore all data in filegroup PRIMARY to a pt beyond the nonlogged changes"
Isnt sql*server supposed to prevent a non-logged operation while a backup is in progress?Also how do i prevent such a situation from recurring.
If i switch to single user mode before a backup how do i prevent exisiting users from accessing ... without bouncing my sql*server?
View 1 Replies
View Related
Sep 25, 2001
Hi Everyone,
I did a restore of database the user the original database is admin where it says the user is dbo.
After i did the restore i was able to add admin as user and dbowner but when i tried to change the user it said the user already exists.
How do i create the same..enviroment as or the original system with users..i saw a problem i i drop the user and them recreate it then it cannot find the object or the object which are owned by it are lost..could any one help me with this ...so as how to and what do i need to keep track of while restoring the database and configure the server back with same user names and permissions.
Thanks.
Kavitha
View 1 Replies
View Related
Sep 15, 2000
Hi,
I have two databases on two different SQL Servers,our application is updating these two databases simultaniously.So every time these two databases will be in synchronized state.
Now i want to schedule backup for these two Databases.How can i take backup of these two databases at single point of time.
so that if i restore these two backups on corresponding servers,There should not be any conflicts.
Any suggetions please....
Ananth
View 1 Replies
View Related
Sep 6, 2000
How can I backup and restore a database from one server to another. I am new in this business. I need your help please!!!
View 3 Replies
View Related
Jan 29, 2001
I'm trying to backup a database to remote server first i did add a device using USE master
EXEC sp_addumpdevice 'disk', 'NetworkDevice', 'cabaanpubs.bak'
then
BACKUP DATABASE pubs TO NetworkDevice
it gives me an error saying
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'NetworkDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
I don't really understand what's goin.
basically i need to synchronize my production database so what am i planning is to take a full backup on that server everynight and then restore it back.
Can some one guide me to take a backup as well as restore on the remoteserver with syntax and what does it mean to add a device...i could not understand.
Is there any other process that's much faster to implement.
i appriciate your support and help .
View 1 Replies
View Related
Aug 30, 2000
Whenever I restore a backup on sqlserver 7.0 and create a new database from it, I always end up having problems with the users existing in the backup.
To enable those users to have access, I end up creating logins with those names and when I try granting those users Database access, I get the error message that the user already exists in the database.
Could some one tell me the right way to restore the backup and having all the users of the database also restored in the proper manner.
View 3 Replies
View Related
Dec 13, 1999
WE are using sqlserver 7.0 & backing full database using Seagate backup ver 7.2 on a tape drive.
I have following questions if anybody could help
1]How do I restore a single table backup from the tape backup without restoring the full database first on the disk & then running import/export
utility to transfer/ copy that particular table to production database.
Is there any third party backup utility which allows for a single / selected table restore?
2]What are the steps or if anybody has the script for tansfering logins/users
with permisssions & without deencrypting the passwords when transfering from one server to another server.
View 1 Replies
View Related
Nov 26, 2006
Hi there,
I am new to administration. i would like to create a development environment out of production database.
for this i have to backup the production database on a regular basis. then use this backup and restore it to a database on different. i can do this task, but i want to automate this task to do it on regular basis.
i would like to schedule my backup time. and immediately after backup i want to restore it to the database on other server, over writing existing data. what is the best way to do this???
thanks,
kishore
View 6 Replies
View Related
Apr 20, 1999
I have a question related to SQL 6.5. We have a SMS 1.2 server which uses SQL 6.5. Two months ago, one of the NT admins did a NT backup of SMS database(sms.db) and then formatted the HD. Now, we need the SMS server back again. We installed the NT server, SMS server snd SQL server on that machine. The problem is, we now want the SQL server to recognize our SMS database (SMS.db file). We don't have any SQL backup of any database (that means, no copy of master database, no copy of other databases. IS THERE A WAY TO RECOVER SO THAT SQL SERVER RECPGNIZE THE SMS DATABASE(SMS.DB FILE)
Please help me out.
I would really appreciate that
Jazib Frahim
View 2 Replies
View Related
Sep 21, 2006
Does some one know how to back up a dts and then restore from a backup?
View 5 Replies
View Related
Feb 2, 2004
I have a database xxxx on server a, I want to copy this DB to server b.How can I do this By back and restore.
WHere can take this back up?I want to take this to a personal folder and wanted to restore to server b from this folder.Is it possible?
Thanks.
View 2 Replies
View Related
Apr 11, 2008
Hi all.,
How to take Backup for whole database and how to restore it.,
which would be the efficient way.,
View 3 Replies
View Related
