Backup And Restore From Current Setup To New Servers
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?.
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?
I'm currently working on a project which uses a SQL Express 2005 database. I want to be able to setup SQL Express from my C# program so that it can perform backups to a specified path location at the requested interval itself. This would free up my program from having to manage the backups. Just wanted to know if this is possible?
Also, when the current database becomes corrupt will SQL Express perform the restore for the user automatically, instead of them having to manually request a restore? I realize that it might not be able to handle the restores in the same way as backups, but I figured I'd at least ask.
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC 2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original 3) Took the new database offline 4) deleted the MDF and LDF files of the new database 5) put "MyDB_0.LDF" in the place of the LDF file I just deleted 6) put the database back on-line 7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)" 8) tried to do Tail Log Backup with this command BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak' WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3 Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Hi All,I am facing this issue very frist time. I add a logical device asfollowing:USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksData','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksData.bak'-- Create a logical backup device, AdvWorksLog.USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksLog','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksLog.bak'-- Back up the full AdventureWorks database.BACKUP DATABASE AdventureWorks TO AdvWorksData-- Back up the AdventureWorks log.BACKUP LOG AdventureWorksTO AdvWorksLogThe database backup is completed successfully but log's backup failswith the following messages:Msg 4214, Level 16, State 1, Line 1BACKUP LOG cannot be performed because there is no current databasebackup.Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.We are moving from SQL 2000 to SQL 2005. I have ensured that databasehas full recovery mode on. We are using SQL 2005 on Windows 2003 ServerSP1.Further more, I also created a plan to test it, and plan also failswhen backing up the logs.Can anyone shed some light on this issue.Thanks in advance.Najm
I have a MS SQL Server 2005 Enterprise Edition 9.0.2153 which manages my BizTalk Server 2006 Databases. The BizTalk server installer automatically creates a job named Backup BizTalk Server (BizTalkMgmtDb) which should back up the databases and transaction protocolls.
The SQL Server Agent runs under domain-administrator account which has full access to my backup directory D:ackups.
The job executes the following steps:
Step 1: BackupFull:
Command:
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'D:Backups' /* location of backup files */
Step 2: MarkAndBackupLog
Command:
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'D:Backups' /* location of backup files */
My challenges are:
1. The job doesn't back up my databases(D:Backups is empty after executing the job)
2. I get every time the following error in error protocol of the job:
BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Fehler 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Fehler 3013).
I have already switched the Databases from full to simple recovery mode and vica versa, it didn't help. Also, the above stored procedures doesn't include neither the TRUNCATE nor the LOG parameters for the logfiles so I wasn't able to solve this issue by adjusting these flags.
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.
Hi,Currently we have logshipping setup with one production db serversending the logs to one standby database server.we would like to have setup where there will be two standby dbservers. Second server will be located in different country.Kindly let me know if we can have the setup wherethere is one primary server and two standby db servers.When I tried to configure the same I am not able to complete themaintenance plan.It gives error that logshipping monitor already exists.Please help me. tks in advance.RegardsKamal
When I do RESTORE FILELISTONLY FROM DISK = 'db.bak' I don't want to specify the path to the .bak file. Is there any way to do this? Something like '%CD%db.bak'?! If I for example want to put MSDE, .bak and a script file (cmd) on a CD-r I want to install (and restore) the database from the current directory.
Hi Everyone, I am using SQL Server 2005 with SQL Management Studio. What I am trying to do is move tables and stored procedures from one database on one server to another database which is on a different server. Essentially, I am trying to move tables and stored procedures from a database on my local computer to a database on our staging server I was told the best way to do this would be to use a linked server. I have been trying to set one up using managment studio but right clicking on server-object and choosing 'new ---> Linked Server' but I have not been able to successfully create the connection. Can anyone out there explain me to me how to setup the linked server using SQL 2005 Management Studio? Thanks!
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.
When installing SQLEXPRESS I am getting this message:
Setup failed because service MSSQL$SQLExpress is disabled for the current hardware profile. Services must be set with the current Hardware Profile logon property enabled.
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.
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.
I found an article online stating that this can occur of your principle backup wasn't restored to the mirror server in NO RECOVERY mode. I missed that step when performing the first restore, so i deleted that database and started over using the NO RECOVERY option on the mirror restore.
The problem I've run into now is that after my restore executes and completes, the database remains in a (Restoring...) state and never becomes accessible.
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.
I am trying to setup the following and was hoping if someone could tell me a more efficient way to set this up. I am not using the maintenance plan or wizard cause Ive had nothing but issues with them:
1. Create a backup device for each day of the week (Sunday - Saturday)
2. Create a job using T-SQL to run maintenance (Rebuild Indexes) and then at the conclusion of the maintenance run a full backup every night at midnight. I would need once job for each day of the week and store them on the proper backup device.
BACKUP DATABASE [DBS1] TO [Sunday Backup] WITH NOFORMAT, NOINIT, NAME = N'DBS1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
3. Create a job using T-SQL for transaction log backups to run every hour and store them in the proper backup device. I would need one job for each day of the week.
BACKUP LOG [DBS1 [Sunday Backup] WITH NOFORMAT, NOINIT,
select @backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1"' not found.', 16, 1) end
RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
This method would give me 7 backups devices and 14 jobs but at least things would be organized nice and neat. Im sure there is a better way to do this so I am looking for some help. Any help would be much appreciated.
I need to delete all backups that are more than a day old . When I set-up a new maintenance plan using "Maintenance Plan Wizard", I don't get an option to retain backups only for "1" day... can you tell me where I should be setting this up? In a SQL 2000, this option was available... Thanks!
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
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?
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.
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
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
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?????
I'm setting up my first pair of Sql 2012 servers using AlwaysOn. I set up backups to run on the primary and I understand that you can set up backups to run on both the primary and secondary servers but the secondary will fail. Is there a way I can stop the secondary server from sending out error messages about failed backups? Is it possible to script it so that the server looks at whether it's primary or secondary and turns on or off alerts based on that?
