SQL Server 2008 :: Moving Backups / Restoring Databases
Feb 23, 2015
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
I was contacted by the SAN team to test backup/restore of larger databases using a split-mirror backup (BCV) or clone that is taken from production db server and copied to another sql box. They want to use this process once a week. I see the mounted drives with the data/log files. All looks good. Initially I attempted to attach the databases and received (Unable to open the physical file db.mdf Operating System Error 5 Access is denied). I manually granting SQLServerMSSQLUser$<computer_name>$<instance_name> on all of the physical files 20 total. That worked.
Since this will be weekly, the SAN team performed the copy again and now none of the databases can communicate with the newly copied files. NTFS permissions need to be set again. I'm getting (Operating System error 21: the device is not ready). Is there something that I'm missing in this process how the vendor BCV clones the data and SQL communicates with the copied files as I was thinking it would be more automated process?
I have a question regarding moving databases within the same server but to a different drive. Using the 'Alter Database' query transaction procedures within the SQL Server Management Studio, I'm able to take a user database offline and move it. However, when using the Alter transaction to bring the database back online, I get errors. There was also an attempt that seemed to work, but the app that uses the database through ODBC can't see the database that was moved and errors out. How can databases be moved and connections re-established within the same server? Thanks...
Until yesterday I had a server running SQL Server 2008 R2 - with all the SQL Server DB files on an attached disk array.
The server died - so I attached the disk array to a new server - and all the DB data files are visible there.
I installed SQL Server 2014 on the new server and am trying to work out how to point it at the existing database files.
I also have backups of the DB's - but they will take ages to copy over and restore - so it would be much easier to just use the db files. Should I restore the master db first (easy as its small)?
I'm working on a restore procedure for the case where all MDF filesare missing, but the LDF files are all intact. A full backup is doneevery 24 hours, and a log backup is done every 3 hours. Afterrestoring the last full + log backups, is it at all possible to usethe LDF files to recover data from that point up to a newer point intime?I've found a post which explains how to do this on SQL Server 2000<http://groups.google.com/group/comp...s.ms-sqlserver/browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4fails with the following error message:BACKUP LOG cannot be performed because there is no current databasebackup.
I recently was given control of an SQL server that stop working. We had no past backups of the database and the only file we had was the .mdf I decided to install a fresh version of SQL 2000 on another server and created a database using enterprise manager with the same name as my database called SWATraining
I then stop the sql server
the first thing that I notice is that the .mdf that enterprise manager created had _DATA at the end of the name. Thus I renamed the the orginal .mdf SWATraining_Data and
copied it to the C:program filesmicrosoft sql servermssqldata when I started SQL the database was greyed out and had (suspect)labeled
How can I recover the database when the only file I have is the .mdf file??
Server: Msg 5172, Level 16, State 15, Line 1 The header for file 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
I'd like to hear the "best practices" for moving databases from one server to another with minimal impact (minimum downtime) to users. The database is about 1GB, but growing at an average rate of 5MB/day.
Only 2 databases on that source server are to be moved. There are other databases on that server that shouldn't be affected as much as possible.
Any good suggestions?
I'm sorry, but I'm a complete newbie to SQL Server although I'm very familiar with Exchange/NT stuff. Got thrown this responsibility lately :(
We are going to be moving over about 29 databases from one server to another (space issues). The new database will then be renamed to the old server name. The database sizes range from 100mb to 29gig. I was planning on doing a dump/restore which will take over 3 hours to dump and 12-15 hours to restore. Our NTAdmin suggested shutting down the SQL Servers and just copying over the data/log files to the new box and then bringing up the new server. Had anyone ever tried this? What are the effects on files over 22gig? Should I also copy over the model/msdb/master database files? What other potential problems should I be aware of? My fear is that I start copying them over, run into a problem half way through and then have to revert back to the dump/restore method.
Any help would be appreciated, we are scheduled to do the move this weekend.
how can I easily move my whole SQL Server 7 installation to another different machine as the current one is going down for maintenance during one week? Thanks.
I was hoping to get some input on the best approach for the following situation: I have been tasked with moving several SQL server databases from one SQL server to another to free up the hardware on the first one for another purpose. The SQL server is version 6.5 and has service pack 5 on it.
I have installed SQL server on the server where the data is going to end up with the same code page and sort order as the current server.
For my next step I was going to create devices for the databases on the new server, then add the databases to the devices. I was going to use the "Create database for Load" statement. Then I was going to load a transaction dump from each database on the old server into the new database on the new server. Is there anything else I will need to do? Will I have to do anything with the master database or is it sufficient to do what I have stated above?
Can anyone direct me to a good technet article or white paper on this process? I did find a short page in SQL books on line which helped a bit.
I have to periodically migrate a copy of database on a production web server down to a web server used by some developers. The names of the databases are different, as are the underlying file names and locations. I use Management Studio to do this. we're running 2008 R2.
I make a backup on the production server, then move the file to the developer server. On that server, I take the db offline, then restore the file into the proper database. Before I start the restore I change the files to match the default names for the target database. Then I do the restore.
The main practical problem I have is that the security ends of breaking for some logins, but not for others, and I don't know why. I use the same login names for users on the source and destination machines, but sometimes they don't come through cleanly, and I have to recreate them. It's always the same users that break. I can fix them, but I'd like to understand why some users come across while others don't.Also, what's the best way to move a db from one engine to another, with a name change along the way? And how do you do it if you have different physical environments and different paths for the actual underlying files between the two systems?
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. 2. Restore the server with Oct 1 full backup with NORECOVERY option. 3.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
I just got hosted on a site which supports .Net, and my forms seem to be coming up okay. I have a database residing on my computer that accompanies the pages I have written.
I was told that I need to export my database to their site, and I have an ip address to use. Can anyone tell me how I would go about with this export ?
I have a SQL 2000 server with a lot of databases (about 100) and I would like to move a great deal of them (+/- 50) to a new SQL 2005 server. All the databases I would move has the same word in the database name. Let's say the all include "Customer" in the Database name. It would be a huge job to detach and attach every db manually. Is there a method that can detach db's based on a query againts the master db with a select on the name of the db?
If so, I also need to attach the db's on the new server with a script after moving the files to a new location.
Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.
I have 2 databases on a remote server on which Replication and Replication Monitor is enabled. Now I want to move this databases from different remove location.
How do I transfer bboth databases with replication and replication monitor enabled on it with all the user logins.
I've got an old version of SQL Server 2008 R2 Developer Edition on an old PC which is failing. I've got a new PC and have put SQL Server 2014 Developer Edition onto it. Now before the old machine completely dies, I've gotten into SSMS on the old machine and did a backup of the databases I want to save. I've moved the .BAK files to where I could get to them from SSMS on the new machine. I've gotten into SSMS and tried to do a restore the database to my new machine. However I'm getting an error that does not make any sense to me.
The database I'm I've backed up is named JobSearch. When I backed it up, that was the only database I had selected. Like I said I copied the .BAK to the new machine. Got into SSMS, told it that I wanted to restore the JobSearch database, telling it where I wanted to put it, and it then immediately fails with a:
"Restore of database 'JobSearch' failed. System.Data.SqlClient.SqlError: Logical file 'VideoLibrary_Data' is not part of the database 'JobSearch'. Use RESTORE FILEISTONLY to list the logical file names."
Well of course VideoLibrary isn't "the logical file". But neither did I select VideoLibrary (which is a database I also want to move, but I'm doing one at a time). So what in heck is going on here? Why is it complaining about a database I haven't even selected to back up? Why, when I check everything on the old machine, it's backing up JobSearch, but on the new machine it sees VideoLibrary?
Any advice that can be offered on this subject would be much appreciated.
We are debating whether to backup/restore the replicated databases or simply copy the mdf and ldf files accross and Attach them. The question is whether the replication information is retained for the publshed objects when the files are copied across? Alernatively, must we use backup/restore with KEEP_REPLICATION set for the restore?
We also plan to back up and restore the distribution, publication, master, msdb etc databases as well as rename the SQL Server to have the same name as the original.
Do these things need to happen in any particular order?
Does the timing of the renaming of the SQL Server matter?
At least some of the these operations (restoring the master database and renaming the SQL Server) need to be done in single user mode. Is is best to restore all the system databases in single user mode?
Lastly, is it necsssary (from a SQL Server point of viewpoint) to rename the computer to have the same name as the SQL Server?
I am trying to set up a process where a differential backup is taken daily and applied to another DB. For some reason I cannot get the diff backup to restore. The full DB backup restored fine.
Server: Msg 3136, Level 16, State 1, Line 4 Cannot apply the differential backup on device 'diff' to database 'test'. Server: Msg 3013, Level 16, State 1, Line 4 Backup or restore operation terminating abnormally.
hey guyz.. i got this code.. its running... but i am not able to find the file that has been backed up by the program.. pls help.. if there is anything wrong with the codes pls help me
this code is for creating backups
Dim paramsBackup As String = " -Usa -P -q " & Chr(34) & "Backup database test to disk ='c: est.bak'" & Chr(34) & " -n "
Try Process.Start("c:program filesmicrosoft sql server80 oolsinnosql.exe", paramsBackup) MsgBox("Backuped") Catch ex As Exception MessageBox.Show(ex.Message) End Try
this code is for restoring
Dim paramsRestore As String = " -Usa -P -q " & Chr(34) & "restore database test from disk ='c: est.bak'" & Chr(34) & " -n " Try Process.Start("c:program filesmicrosoft sql server80 oolsinnosql.exe", paramsRestore) MsgBox("Restored") Catch ex As Exception MessageBox.Show(ex.Message) End Try
SQL Server's Books On-line seems to hint that this can be done. Does anyone have any info or practical application of this? Can someone confirm that this is possible please.
I have a partitioned table in which one of the partitions is on the Primary filegroup. I want to move the data off of that Primary filegroup, and and on to a new filegroup named RTFG6.
Scheme and function currently defined as:
CREATE PARTITION SCHEME [PS1_Left_id] AS PARTITION [PF1_Left_id] TO ([RTFG1], [RTFG2], [RTFG3], [RTFG4], [RTFG5], [PRIMARY])
CREATE PARTITION FUNCTION [PF1_Left_id](int) AS RANGE LEFT FOR VALUES (10, 15, 35, 48, 53)
I've tried split and merge, and for whatever reason, always end up with the Primary filegroup holding data.
How do i get it off of Primary completely, and onto RTFG1 to RTFG6?
I don't want to export to a holding table and re-create the table if i can avoid it, due to identity columns and relationships with multiple tables.
I have another question : 1) Restoring SQL Server Databases after uninstalling and reinstalling SQL Server.
Two days ago there was some problem with Norton Antivirus, so Windows 98 restored itself to a previous state. The SQL Server name was not available when I tried to start again, so I had to uninstall and reinstall SQL Server. I started a new Database just a week before this happened (the change from Access to SQL Server as I stated in the other thread http://www.dbforums.com/showthread.php?threadid=987880) and after reinstallation I could not access the Database any more - even while it is in the Data directory. I did not have any backup to restore from - I just started creating tables, so there was only the Log file created and the MDF database file. I tried to import it for a while to the new server, but as the database is not shown in the new server, it was unable to import like I hoped. So how do you restore it back to SQL Server when you have no backup and only the (undamaged) MDF database file if you only have one server? I had to recreate the Database from scratch again - I don't want it to happen again.
if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
Followed this article to move my model and msdb databes but I think I messed up. http://msdn.microsoft.com/en-us/library/ms345408.aspx The instance will not start because it says one of the files does not match it's primary file (not sure if it's model or msdb)
I can bring the instance up in single user mode (NET START mssqlserver /f /t3608) but when I try to do a query to re-do my alter statements, it says i can't attach because only one seesion is allowed. in 2000 i used to be able to start query analyzer without starting enterprise manager..
When i try and start with a minimal config (sqlservr -c -m) it won't start. I get an error saying that one of my files does not match it's primary file (either model or msbd don't know which) any ideas????????
I am restoring a database with 10yrs worth of data which have monthly partitions but i would like to keep only 5yrs of data after the restore is done, what is the best/faster approach to delete the 5yrs data without deleting the partitions as that may cause the db in accessible.
I have a database with deployed CLR assembly on SQL 2008. I recently took a backup for the database and restored it on SQL 2012 server. Everything worked fine except very strange working of CLR assemblies functions.The result of the function is very odd. It manipulates the numeric string and return the result. It is returning very strange result on SQL 2012 as compared to SQL 2008.Example SQL 2008 ManipulateString('1234') returns 2345 On SQL 2012 the same call returns 1155.I am also trying to deploy the same assembly to SQL 2012 server database. I have successfully register the assembly, but the database do not list the exposed methods.
I backup a database at the begining of each month with a full and then do nightly diffs on it.
For the same database I run daily fulls and 10 minute log backups.
these two backups create / append to two different backup files.
The problem im having is that I cant restore the Differential backup set. SQL seems to restore the full just fine but alwasy throws an error when its about to start to retore the last diff. now forgive me but I clicked OK on the message and I cant find any record of the error in the logs but its something like:
"SQL cannot restore the database as the database has not been restored to the previous correct state"
is my 10 min TS log backups screwing up the DIff chain somehow?
this is really doing my head in. any help appreciated.
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
